在SQL Server 2012 Express中创建数据库副本 [英] Create copy of database in sql server 2012 express

问题描述

你好，我有用于用户工作的数据库。
我想创建该数据库的副本以进行测试。

Hello I have database on which user work. I want to create copy of this database for testing purpose.

谁能告诉我该怎么做？
我尝试了从复制SQL Server Express中的数据库？ a>

Can anyone tell how can this be done? I tried everything from copy a database within SQL Server Express?

但是复制文件并将其附加会破坏第一个数据库，并尝试将备份还原到新数据库中，从而引发数据库不相同的错误。

But Copying files and attaching it destroys first database and trying to restore backup into new database throws error that database is not the same.

有人可以建议我解决方案吗？

Can anyone suggest me solution?

因为@Nadeem_MK建议我创建了备份，现在我要使用此脚本进行还原：

as @Nadeem_MK suggested I created backup and now I'm trying to restore with this script:

RESTORE DATABASE [Equipment Test]  -- use your new db name
FROM  DISK = N'C:\Backup\ExistingDb.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Equipment Test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ExistingDb.mdf',  --logical name and physical name 
MOVE N'Equipment Test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ExistingDb.ldf' --logical name and physical name

并抛出错误：

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf' cannot be overwritten.  It is being used by database 'Equipment'.
Msg 3156, Level 16, State 4, Line 1
File 'Equipment' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1

The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf' cannot be overwritten.  It is being used by database 'Equipment'.
Msg 3156, Level 16, State 4, Line 1
File 'Equipment_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

如您所见，还原正在尝试更改备份数据库中的文件而不是新数据库中的文件

As you can see restores is trying to alter files from backup-ed database instead of new database

@Update：
备份脚本：

@Update: BackUp Script:

--To backup Database (Delete the previous backup first)
BACKUP DATABASE Equipment --Database Name
TO DISK = 'C:\Backup\Equipment.bak' WITH INIT --Path of backup location

文件位置：

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment Test.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment Test_log.mdf

恢复功能：

RESTORE DATABASE [Equipment Test]  -- use your new db name
FROM  DISK = N'C:\Backup\Equipment.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Equipment Test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment2.mdf',  --logical name and physical name 
MOVE N'Equipment Test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log2.ldf' --logical name and physical name

推荐答案

假定这是原始数据库的路径：

Assuming this are the paths of the original database:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf

备份文件名

Equipment.bak

确保新数据库的文件名与旧数据库的文件名不同。这就是您所得到的错误的意思：

Make sure that the filenames for the new database aren't the same as the old one. That is what the errors you are getting means:

The file 'C:\...' cannot be overwritten.  It is being used by database 'Equipment'.

说明：请勿使用已有文件的文件名！

Explanation: Don't use filenames of files you already have!

相反，给它们起一个新名字。像这样：

Instead, give these a new name. Like this:

RESTORE DATABASE [Equipment Test]  -- use your new db name
FROM  DISK = N'C:\Backup\Equipment.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Equipment Test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment2.mdf',  --logical name and physical name 
MOVE N'Equipment Test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log2.ldf' --logical name and physical name

这基本上是Nadeem_MK的答案，但现在您应该使用原始名称 Equipment.mdf 和 Equipment_log.ldf 来代替有 Equipment2.mdf 和 Equipment_log.ldf

This is basically Nadeem_MK's answer, but instead of your original names Equipment.mdf and Equipment_log.ldf, now you should have Equipment2.mdf and Equipment_log.ldf

如果这行得通，也请反对Nadeem_MK的回答，因为这基本上是他在说的。我只是没有足够的声誉来评论他的回答。

If this works, please also opvote Nadeem_MK's answer, as this is basically what he was saying. I just don't have enough reputation yet to comment on his answer.

这篇关于在SQL Server 2012 Express中创建数据库副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！