How can I copy a SQL Server 2005 database from production to development?

Question

We have a production SQL Server 2005 database server with the production version of our application's database on it. I would like to be able to copy down the data contents of the production database to a development server for testing.

Several sites (and Microsoft's forums) suggest using the Backup/Restore options to copy databases from one server from another, but this solution is unworkable for several reasons (I don't have backup authority on our production database, I don't want to overwrite permissions on the development server, I don't want to overwrite structure changes on the development server, etc...)

I've tried using the SQL Import/Export Wizard in SQL Server 2005, but it always reports primary key violations. How can I copy the contents of a database from the production server to development without using the "Backup/Restore" method?

Answer 1

Well without the proper rights it really becomes more tedious and less than ideal.

One way that I would recommend though is to drop all of your constraints and indexes and then add them again once the data has been imported/exported.

Not an elegant solution but it'll process really fast.

EDIT: Another option is to create an SSIS package where you specifically dump the tables in an order that won't violate the constraints.

Answer 2

I'd contact someone that does have access to backup the database. Permissions are usually there for a reason.

Answer 3

I often use SQL Data Compare (http://www.red-gate.com/products/sql_data_compare/index.htm) for this task: the synchronization scripts it writes will remove the relationships during the transfer and reapply them, but that is OK in most development cases. It works especially well with smaller databases or subsets of databases.

If your database is large, I would recommend finding someone with the keys to the kingdom. Doing an out of sequence backup could mess with the ability to restore the database from the primary backup (if they are doing partials during the week for example) by marking records backed up when they are only in your backup, so don't try to bypass that security if you are unsure why it is there.

Answer 4

The import/ export wizard is notorious for this sort of thing, and actually has a bug that makes it even less useful in working out the dependencies (sorry, don't have the details to hand).

SSIS does a much better job, but you'll have to add each table copy task by hand (in fact a datasource, copy task and data destination objects. It's a little tedious to set up (more than it should be), but a lot simpler than writing your own code.

One tip: avoid generating an SSIS project with the import/ export wizard, thinking it will be easier to just tweak it. It generates something that most people would find unrecognisable, even with some SSIS experience!

Answer 5

Assuming that you can connect to both DB's from the same machine (which almost always you can - I do it with my production servers via a VPN).

For each table

DELETE FROM devserv.dbo.tablename;
SET identity_insert [devserv.dbo.tablename] ON;
INSERT into devserv.dbo.tablename SELECT * from prodserv.dbo.tablename;
SET identity_insert [devname.dbo.tablename] OFF;

It is obviously worth noting that you will need to do this in a certain order if your tables have foreign key constraints.

Answer 6

If you do not have backup permission on the production server, I guess this is because you are using a shared SQL Server from a webhoster. In this case, check if your webhoster provides the tool called myLittleBackup. It allows installing a db from one server to another in a few clicks...

Answer 7

On your Dev machine, setup a linked server to your production machine. Then just

INSERT dev.db.dbo.table (fieldlist)

SELECT (fieldlist) from prod.db.dbo.table

Answer 8

I might consider getting a backup as there will be one wether you run it or not (t least in theory a Prod DB is being backed up :) )

Then just restore to a brand new database on your dev box so you dont conflict with anything or anyone else.

If you restore to a new DB you could also pull the tables and data across manually if you wanted and since you create the DB you give yourself rights and it's all ok. There's a number of other methods, all tedious.

Answer 9

It is obviously worth noting that you will need to do this in a certain order if your tables have foreign key constraints.

Answer 10

We just use the SQL Server Database Publishing Wizard at work.

You would use this little utility to generate a T-SQL script that describes your production database (including all its data). Then connect to your dev server and run the generated script.