問題描述
我正在研究 vb.NET 2013 和 SQL server 2008R2.
I'm working on vb.NET 2013 and SQL server 2008R2.
我有這種情況需要解決:
I have this situation to resolve:
我有一個數(shù)據(jù)庫DB1".我想在同一個 SQL 服務(wù)器上用另一個名稱DB2"創(chuàng)建這個數(shù)據(jù)庫的副本，然后讓這個數(shù)據(jù)庫準(zhǔn)備好連接.
I have a database "DB1". I want to create a copy of this database on the same SQL server with another name "DB2", and after make this database ready to connect.
如何通過 Vb.NET 中的代碼來完成此操作?
How can I do this through code from Vb.NET?
推薦答案
查看此技術(shù)網(wǎng)絡(luò)鏈接以使用新文件名進(jìn)行恢復(fù).
Check out this tech net link for restore with new file names.
http://technet.microsoft.com/en-us/library/ms190447(v=sql.105).aspx.
備份數(shù)據(jù)庫一 (DB1).
Take a backup of database one (DB1).
驗(yàn)證備份是否有效.
獲取正確的邏輯和物理文件名
Get correct logical and physical file names
使用移動命令恢復(fù)以創(chuàng)建數(shù)據(jù)庫 2 (DB2).
Restore with a move command to create database two (DB2).
將所有權(quán)更改為 SA.默認(rèn)為創(chuàng)建數(shù)據(jù)庫的 SQL 登錄
Change ownership to SA. Defaults to SQL login that creates the database
這是我在筆記本電腦上測試的腳本.備份的好處是不需要停機(jī)時間.如果您決定脫機(jī)、復(fù)制數(shù)據(jù)文件并創(chuàng)建附加數(shù)據(jù)庫，則會造成停機(jī)時間.
Here is a script that I tested on my laptop. Nice thing about backup is no need for down time. If you decide to take off line, copy data files, and create database for attach, down time is created.
-- Sample database
USE AdventureWorks2012;
GO
-- 1 - Make a backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\mssql\backup\AdventureWorks2012.Bak'
WITH FORMAT,
MEDIANAME = 'SQLServerBackups',
NAME = 'Full Backup of AdventureWorks2012';
GO
-- Use master
USE master
GO
-- 2 - Is the backup valid
RESTORE VERIFYONLY
FROM DISK = 'C:\mssql\backup\AdventureWorks2012.Bak';
GO
-- 3 - Check the logical / physical file names
RESTORE FILELISTONLY
FROM DISK = 'C:\mssql\backup\AdventureWorks2012.Bak';
GO
-- 4 - Restore the files change the location and name
RESTORE DATABASE Adv2012
FROM DISK = 'C:\mssql\backup\AdventureWorks2012.Bak'
WITH RECOVERY,
MOVE 'AdventureWorks2012_Data' TO 'c:\mssql\data\MyAdvWorks_Data.mdf',
MOVE 'AdventureWorks2012_Log' TO 'c:\mssql\log\MyAdvWorks_Data.ldf';
GO
-- 5 - Switch owner to system admin
ALTER AUTHORIZATION ON DATABASE::Adv2012 TO SA;
GO
這篇關(guān)于如何制作 SQL Server 數(shù)據(jù)庫的副本并連接到它?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！