Copying an existing database : Creating a local development database in MS SQL Part 1

One of the first tasks I set myself at my new role was to create my own local development database. Currently, the developers were developing against a populated database shared amongst developers and testers.

I couldn’t find the scripts to recreate the database schema from scratch. The Copy Database Wizard in MS SQL Server Management Studio (SMS) failed with authentication problems. Backup Database stored the sqldump on the remote server hard disk which I didn’t have access to. I was ready to give up when a colleague from another team told me about the scripting functionality in SMS.

Database Objects Scripts

In SMS, right click on the database you want to replicate, then select Tasks -> Generate Scripts. A dialogue will pop up, where you can select the database objects you want to copy. You can copy specific tables, views, stored procedures etc, as shown in the following diagram.

Then in the next dialogue, under the advanced options, there are two options I found especially useful.

• Script DROP and CREATE
• Types of data to script: options are schema only, data only and both schema and data

Once the script was generated, it can be applied in a query window for the target database.