Summary

This article shows one way of setting up a local database and populating it with data from the server database. Restriction here is that you might not have full admin access to the server db and you might not want all the tables.

There are several ways of copying a server database and setting it up locally. The Figure 1 shows Back Up / Restore, Copy Database, Take Offline (and then copy the database files), etc. All these options are available from Microsoft SQL Server 2005 Management Studio by right-clicking on the database that you want to copy.

Figure 1. Several ways of copying a database

Here we will take a slightly different approach to copying the server database – the Export route. This might be useful if you don’t have full admin privileges to copy / Take Offline the entire database on the server. And you might want only a few tables.

Create an empty database

First step is to create an empty database (presumably on your laptop/desktop – as the deal here is to set up local database). You can use the New Database … from the right-click menu of the local server in the Microsoft SQL Server Management Studio. Use the appropriate locations for the database files, and choose other options, etc.

After this you will have an empty database.

Export data from the server database

Now go to the server database that you want to copy the data from. From the right-click menu, choose Export Data … menu item.

Figure 2. Import and Export Wizard

Setup the foreign keys, indexes, etc.

With the above approach, the foreign key relationships won’t get setup for the tables in the destination database. You can choose one of the several ways to address that.

-You can use one of the other methods (copy, backup, etc.) for the first time and setup a base database. Then for the incremental data update use the Import Export wizard.

- Set up the database first with the generated scripts. Then export the tables in order (dependent tables exported after the tables that they depend on). Of course, if there are large numbers of dependencies, this would be a painful situation.

Setup the tables for ASP.NET authentication

Next step is to setup the membership info by running appropriate scripts on the local database (refer to this article):

Setting up the authentication (users and passwords) database in ASP.NET 2.0

With this you will have a working local database that works with the ASP.NET site. However, this will not be an exact copy of the server database.