用于克隆数据库的 SQL 脚本，保持原始数据库不变 [英] SQL Script to clone database leaving original untouched

问题描述

我们目前有一个基本的 CMS 安装，在这个 CMS 中它包含一个完整的用户、产品、内容等工作数据集.我们希望增加我们的安装时间，因为现在我们目前必须进入 SQL Server 2012，创建一个新数据库，然后从现有的基本安装数据库中恢复该数据库.

we currently have a base installation of our CMS, in this CMS it contains a complete working dataset for users, products, content etc. We are looking to increase our installation time because right now we currently have to go into SQL Server 2012, create a new DB and then restore the DB from an existing base installation db.

我们每次安装最多可能需要 10 到 15 分钟.

This can take up to 10 - 15 minutes each installation we do.

我们还确保我们的基础数据库满足我们构建的网站的所有要求.

We also make sure our base database has all the requirements for sites we build.

我们的问题是，我们想做以下事情.

Our issue is, we would like to do the following.

1. 让脚本创建一个全新的空数据库
2. 将此数据库的克隆复制到一个新的 .bak 文件中
3. 获取这个 .bak 文件，然后将其复制到一个全新的数据库中，分别使用自己的 MDF 和 LDF 文件.

数据库位于同一台服务器上，因此我们无需将其迁移到任何其他机器或实例.

the databases are on the same server so we dont need to migrate this across to any other machine or instance.

我们的代码如下

CREATE database my_test

BACKUP DATABASE test_db TO DISK = 'C:\my_test\my_test.bak' WITH INIT;
EXEC internal_lab_test.dbo.sp_helpfile;

RESTORE FILELISTONLY
  FROM DISK = 'C:\my_test\my_test.bak'

RESTORE DATABASE my_test
  FROM DISK = 'C:\my_test\my_test.bak'
  WITH MOVE 'my_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db.mdf',
  MOVE 'my_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db_log.ldf'

我们希望确保一切都是新鲜干净的，但仍然包含所有数据，但是每次运行此代码时，我们都会收到以下错误消息

we want to make sure that everything is fresh and clean, but also still contain all the data, however everytime we run this code, we get the following error messages

我们还希望确保原始数据库的 mdf 和 ldf 文件完好无损，不会在新数据库中使用

we also want to make sure the original database mdf and ldf files are left in tact and arent used in the new database

Msg 3154, Level 16, State 4, Line 10
The backup set holds a backup of a database other than the existing 'my_test' database.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

推荐答案

我知道这是旧的，但它是谷歌的第二个条目，所以为了完整性.

I know this is old, but it was the 2nd entry in google so for completeness.

这是因为数据库已经存在.所以要么删除数据库，要么添加替换.

It is because the database already exists. so either drop the database or add with Replace.

此外，my_test 和 my_test_log 名称必须是 restore filelistonly 命令中的逻辑名称.

Also the my_test and my_test_log names need to be the logical names from the restore filelistonly command.

RESTORE DATABASE my_test
  FROM DISK = 'C:\my_test\my_test.bak'
  WITH Replace,
  MOVE 'my_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db.mdf',
  MOVE 'my_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db_log.ldf'

这篇关于用于克隆数据库的 SQL 脚本，保持原始数据库不变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！