CODEWITHSUNDEEP
sql-serverdatabase
ryan_s
ryan_s

Reputation: 7954

How best to copy entire databases in MS SQL Server?

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) 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

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?

Upvotes: 24

Views: 45606

Answers (11)

Doug
Doug

Reputation: 6518

Another one to check out that is quick and simple:

Simple SQL BULK Copy

http://projects.c3o.com/files/3/plugins/entry11.aspx

Upvotes: 2

Yordan Georgiev
Yordan Georgiev

Reputation: 5440

Check those links:

Upvotes: 0

Mike L
Mike L

Reputation: 622

If you use the Backup/Restore solution you're likely to have orphaned users so be sure to check out this article<microsoft> on how to fix them.

Upvotes: 2

MotoWilliams
MotoWilliams

Reputation: 1568

There are better answers already but this is an 'also ran' because it is just another option.

For the low low price of free you could look at the Microsoft SQL Server Database Publishing Wizard. This tool allows you to script the schema, data or data and schema. Plus is can be run from a UI or command line <- think CI process.

Upvotes: 4

Kevin Sheffield
Kevin Sheffield

Reputation: 3616

Redgate SQL Compare and SQL Data Compare. The Comparison Bundle was by far the best investment a company I worked for ever made. Moving e-training content was a breeze with it.

Upvotes: 0

stephbu
stephbu

Reputation: 5092

In order of ease

  • stop server/fcopy/attach is probably easiest.
  • backup/restore - can be done disconnected pretty simple and easy
  • transfer DTS task - needs file copy permissions
  • replication - furthest from simple to setup

Things to think about permissions, users and groups at the destination server esp. if you're transferring or restoring.

Upvotes: 4

Leon Bambrick
Leon Bambrick

Reputation: 26306

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

Upvotes: 49

user1151
user1151

Reputation:

Maybe the easiest is to detach/reattach. Right-click in the server manager on the DB, tasks --> detach. Then copy the MDF/LDF files to the new server and then reattach by clicking on the server icon and tasks-->attach. It will ask you for the MDF file - make sure the name etc is accurate.

Upvotes: 15

JesseG
JesseG

Reputation:

use backups to restore the databases to the new server with the new names.

Upvotes: 0

X-Cubed
X-Cubed

Reputation: 1899

Backup the databases using the standard SQL backup tool in Enterprise Manager, then when you restore on the second server you can specify the name of the new database.

This is the best way to maintain the schema in its entirety.

Upvotes: 1

dimarzionist
dimarzionist

Reputation: 18687

Backup -> Restore is the simplest, if not to use the replication.

Upvotes: 3

Related Questions