CODEWITHSUNDEEP
sql-serversql-server-2005
Adam Ness
Adam Ness

Reputation: 6283

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

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?

Upvotes: 2

Views: 5692

Answers (11)

BornToCode
BornToCode

Reputation: 10213

If you have to avoid backup/restore this is what I would recommend (these steps assuming you don't want to maintain the old schema NAME, just the structure) -

Download opendbdiff. Choose 'Compare' between source and (empty) destination. Choose sync. script tab and copy only the create table rows (without dbo.sysdiagrams tables etc.) paste into sql managment studio new query, delete all the schemas names appearing before the table names.

Now you have the full structure including primary keys, identity etc. Next step - use sql server import and export data like you did before (make sure you choose edit mappings and choose destination schema as dbo etc.). Also make sure you tick drop and recreate destination table.

Upvotes: 0

daharon
daharon

Reputation: 2010

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.

Upvotes: 0

Santosh marotha
Santosh marotha

Reputation:

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

Upvotes: 0

Robert
Robert

Reputation: 1845

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.

Upvotes: 0

ScottStonehouse
ScottStonehouse

Reputation: 24965

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

Upvotes: -1

Khayyam
Khayyam

Reputation:

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

Upvotes: 1

Richard Harrison
Richard Harrison

Reputation: 19403

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.

Upvotes: 2

philsquared
philsquared

Reputation: 22493

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!

Upvotes: 1

Godeke
Godeke

Reputation: 16281

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.

Upvotes: 2

Ady
Ady

Reputation: 4736

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

Upvotes: 0

Marcus King
Marcus King

Reputation: 1677

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.

Upvotes: 3

Related Questions