Sunday, February 27, 2011

Easy way to copy Database and table data in Microsoft SQL server

For years I have always found issues importing and exporting data using Microsoft SQL server on the same SQL Server instance. I was very happy with the release of SQL server 2008 and the COPY database feature. This works really well and avoids all the IDENT inserts and other assorted foreign key issues.

Well I recently took on a project that used SQL Server Express 2008 R2. Unfortunately, the Copy database is not available in the express version and with the complex data structure of the database I was working with, I was unable to create a beta version for testing alongside the live database on the live server.

I tried to script everything out and that worked fine, but importing my data did not.

I tried to start with a fresh database and had some issues as keys and indexes did not come over.

So I decided to poke around and noticed that while doing a backup, you can also do a restore from a backup to another database. So I tried this, wow, I feel like an idiot. For over 15 years I always had issues copying databases and their data while retaining their structure on the same instance. I could always get it to where I wanted it, but there were always issues along the way with complex data structures.

This was so simple to just take a backup from the live database. Then restore that bak file to a database on the same server , on he same instance, I just provide the name.

Hope this saves someone the years of trouble it has caused me, sad thing it has always been right there, I just assumed it would be messed up due to file naming.

Steps to copy database to another database on the same SQL Server Express 2008 R2 Management Studio.

1: Right click on the database you want a copy of. Choose TASKS,then choose BACKUP. Click OK.

2: Right click the database folder/node, NOT A DATABASE, Choose TASKS, then RESTORE, then DATABASE. (Note: if you right click on the database, it will overwright that database, if you click on the database node you will get the option to name a new database)

3: Type in the name you want your new database to be named in the To Database text box.

4: Choose the FROM DATABASE, and then below check thebackup you want restored to. (NOTE: You must have a backup for he Original database to appear in this dropdown)

5: Click OK, and SMILE!!!

wasn't that easy!!!!