script - Best way to copy a database(SQL Server 2008)




sql server copy database wizard (6)

Dumb question - what's the best way to copy instances in an environment where I want to refresh a development server with instances from a production server?

I've done backup-restore, but I've heard detach-copy-attach and one guy even told me he would just copy the datafiles between the filesystems....

Are these the three (or two, the last one sounds kind of suspect) accepted methods?

My understanding is that the second method is faster but requires downtime on the source because of the detach aspect.

Also, in this situation (wanting an exact copy of production on a dev server) what's the accepted practice for transferring logins,etc.? Should I just backup and restore the user databases + master + msdb?


UPDATE:
My advice below tells you how to script a DB using SQL Server Management Studio, but the default settings in SSMS miss out all sorts of crucial parts of a database (like indexes and triggers!) for some reason. So, I created my own program to properly script a database including just about every type of DB object you may have added. I recommend using this instead. It's called SQL Server Scripter and it can be found here:
https://bitbucket.org/jez9999/sqlserverscripter


I'm surprised no-one has mentioned this, because it's really useful: you can dump out a database (its schema and data) to a script, using SQL Server Management Studio.

Right-click the database, choose "Tasks | Generate Scripts...", and then select to script specific database objects. Select the ones you want to copy over to the new DB (you probably want to select at least the Tables and Schemas). Then, for the "Set Scripting Options" screen, click "Advanced", scroll down to "Types of data to script" and select "Schema and data". Click OK, and finish generating the script. You'll see that this has now generated a long script for you that creates the database's tables and inserts the data into them! You can then create a new database, and change the USE [DbName] statement at the top of the script to reflect the name of the new database you want to copy the old one to. Run the script and the old database's schema and data will be copied to the new one!

This allows you to do the whole thing from within SQL Server Management studio, and there's no need to touch the file system.


Below is what I do to copy a database from production env to my local env:

  1. Create an empty database in your local sql server
  2. Right click on the new database -> tasks -> import data
  3. In the SQL Server Import and Export Wizard, select product env's servername as data source. And select your new database as the destination data.

I run an SP to DROP the table(s) and then use a DTS package to import the most recent production table(s) onto my development box. Then I go home and come back the following morning. It's not elegant; but it works for me.


I used EMS SQL Backup and it has the Database Shipping task which can be easily scheduled. You have to specify the shared network folder. Copying is performed via backup/copy/restore as well, but can be setup quickly. It looks like there is no ability to handle logins in the program and you will have to do it manually.


Its hard to detach your production dB or other running dB's and deal with that downtime, so I almost always use a Backup / restore method.

If you also want to make sure to keep your login's in sync check out the MS KB article on using the stored proc sp_help_revlogin to do this.


The detach/copy/attach method will take down the database. That's not something you'd want in production.

The backup/restore will only work if you have write permissions to the production server. I work with Amazon RDS and I don't.

The import/export method doesn't really work because of foreign keys - unless you do tables one by one in the order they reference one another. You can do an import/export to a new database. That will copy all the tables and data, but not the foreign keys.

This sounds like a common operation one needs to do with database. Why isn't SQL Server handling this properly? Every time I had to do this it was frustrating.

That being said, the only painless solution I've encountered was Sql Azure Migration Tool which is maintained by the community. It works with SQL Server too.





sql-server-2008