如何最好复制整个数据库在MS SQL Server？ [英] How best to copy entire databases in MS SQL Server?

问题描述

我需要将大约40个数据库从一个服务器复制到另一个服务器。新数据库应具有新名称，但所有相同的表，数据和索引应与原始数据库相同。到目前为止，我一直是：

I need to copy about 40 databases from one server to another. The new databases should have new names, but all the same tables, data and indexes as the original databases. So far I've been:

1）创建每个目标数据库

2）使用任务 - >导出数据命令单独为每个数据库创建和填充表

3）使用SQL脚本重建每个数据库的所有索引

1) creating each destination database
2) using the "Tasks->Export Data" command to create and populate tables for each database individually
3) rebuilding all of the indexes for each database with a SQL script

每个数据库只有三个步骤，但我敢打赌，有一个更简单的方法。是否有任何MS SQL Server专家有任何建议？

Only three steps per database, but I'll bet there's an easier way. Do any MS SQL Server experts out there have any advice?

推荐答案

想要一个简单的脚本解决方案，而不是点和点击解决方案。

Given that you're performing this on multiple databases -- you want a simple scripted solution, not a point and click solution.

这是一个备份脚本，我保持。

This is a backup script that i keep around. Get it working for one file and then modify it for many.

(on source server...)
BACKUP DATABASE Northwind
  TO DISK = 'c:\Northwind.bak'

(target server...)
RESTORE FILELISTONLY
  FROM DISK = 'c:\Northwind.bak'

(look at the device names... and determine where you want the mdf and
ldf files to go on this target server)

RESTORE DATABASE TestDB
  FROM DISK = 'c:\Northwind.bak'
  WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
  MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

这篇关于如何最好复制整个数据库在MS SQL Server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！