复制SQL Server DB来测试DB的最简单方法是什么?

时间:2021-05-23 16:26:59

What is the easiest way to take a SQL Server database and make a test copy of it?

使用SQL Server数据库并对其进行测试拷贝的最简单方法是什么?

I have looked through some existing topics but not sure if there is an easier way. I have database publisher. I want to be able to keep both database on the same server, potentially.

我浏览了一些现有的主题,但不确定是否有更简单的方法。我有数据库出版商。我希望能够将两个数据库保持在同一个服务器上。

Update: I used the Microsoft SQL Server Publishing Wizard to script to file, create a new database, add "use db", and execute the script. This seemed to work okay. The attach/detach is not recommended because it leaves links back to the original location if it is a full text search database or if the log is missing.

更新:我使用Microsoft SQL Server发布向导将脚本文件转换为脚本,创建一个新的数据库,添加“use db”,并执行脚本。这似乎行得通。不推荐附加/分离,因为如果它是一个完整的文本搜索数据库或日志丢失,它将链接返回到原始位置。

3 个解决方案

#1


5  

I always just back it up then restore to a different name/file set; How to: Restore a Database to a New Location and Name (Transact-SQL) or you can creater an empty db & use the restore "wizard" enabling OVERWRITE and changing the restore file paths.

我总是备份它然后恢复到不同的名称/文件集;如何:将数据库恢复到一个新的位置和名称(Transact-SQL),或者您可以创建一个空的db并使用恢复“向导”来覆盖和更改恢复文件路径。

#2


2  

I would just create the database, then use the "Import Data" task in SS Mgmt Studio to copy the data over. Or you could back up the production database and restore it into the test database.

我只需创建数据库,然后使用SS Mgmt Studio中的“导入数据”任务将数据复制过来。或者您可以备份生产数据库并将其恢复到测试数据库中。

Maybe not the absolute easiest ways, but pretty low-drama. You can also script the data to a file and play that back into a new database -- that takes awhile, but it's handy for things like version control, and it's human (well, "developer")-readable.

也许这不是最简单的方法,但戏剧性却很低。您还可以将数据脚本化到一个文件中,并将其回放到一个新的数据库中——这需要一段时间,但对于像版本控制这样的东西来说,这是很方便的,而且它是可读的(嗯,“开发人员”)。

#3


1  

Detatch the database (means taking it offline), copy the mdf file, and then re-attach both the original and the copy.

Detatch数据库(意味着将其脱机),复制mdf文件,然后重新附加原件和副本。

#1


5  

I always just back it up then restore to a different name/file set; How to: Restore a Database to a New Location and Name (Transact-SQL) or you can creater an empty db & use the restore "wizard" enabling OVERWRITE and changing the restore file paths.

我总是备份它然后恢复到不同的名称/文件集;如何:将数据库恢复到一个新的位置和名称(Transact-SQL),或者您可以创建一个空的db并使用恢复“向导”来覆盖和更改恢复文件路径。

#2


2  

I would just create the database, then use the "Import Data" task in SS Mgmt Studio to copy the data over. Or you could back up the production database and restore it into the test database.

我只需创建数据库,然后使用SS Mgmt Studio中的“导入数据”任务将数据复制过来。或者您可以备份生产数据库并将其恢复到测试数据库中。

Maybe not the absolute easiest ways, but pretty low-drama. You can also script the data to a file and play that back into a new database -- that takes awhile, but it's handy for things like version control, and it's human (well, "developer")-readable.

也许这不是最简单的方法,但戏剧性却很低。您还可以将数据脚本化到一个文件中,并将其回放到一个新的数据库中——这需要一段时间,但对于像版本控制这样的东西来说,这是很方便的,而且它是可读的(嗯,“开发人员”)。

#3


1  

Detatch the database (means taking it offline), copy the mdf file, and then re-attach both the original and the copy.

Detatch数据库(意味着将其脱机),复制mdf文件,然后重新附加原件和副本。