如何制作 SQL Server 数据库的副本并连接到它?

2021-09-16 00:00:00 vb.net sql-server

我正在研究 vb.NET 2013 和 SQL server 2008R2.

I'm working on vb.NET 2013 and SQL server 2008R2.

我有这种情况需要解决:

I have this situation to resolve:

我有一个数据库DB1".我想在同一个 SQL 服务器上用另一个名称DB2"创建这个数据库的副本，然后让这个数据库准备好连接.

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?

推荐答案

查看此技术网络链接以使用新文件名进行恢复.

Check out this tech net link for restore with new file names.

http://technet.microsoft.com/en-us/library/ms190447(v=sql.105).aspx.

  1. 备份数据库一 (DB1).

  1. Take a backup of database one (DB1).

验证备份是否有效.

获取正确的逻辑和物理文件名

Get correct logical and physical file names

使用移动命令恢复以创建数据库 2 (DB2).

Restore with a move command to create database two (DB2).

将所有权更改为 SA.默认为创建数据库的 SQL 登录

Change ownership to SA. Defaults to SQL login that creates the database

这是我在笔记本电脑上测试的脚本.备份的好处是不需要停机时间.如果您决定脱机、复制数据文件并创建附加数据库，则会造成停机时间.

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

相关文章