Deployment of a Complete Database: Traditional Approach

The idea behind this method is to use some means of moving the complete database so that relationships between individual database objects do not have to be managed once they are established. There are several options:

• Detach and reattach the database in Transact-SQL

• Attach and detach the database in Management Studio

• Backup and restore

Detach and Reattach the Database in Transact-SQL

The idea behind this option is to detach the database from the server, copy the database files to the production server, and then attach the database files to the new server (and reattach the database files to the original server, if applicable). To detach the Asset5 database manually, you can use the following script:

 EXEC sp_detach_db 'Asset5' 

SQL Server checks the integrity of the database, flushes everything that is in memory to disk, stops further changes to the database, and releases database files.

Note 

You must have exclusive use of the database to perform this function.

You can then copy the files (in this case, Assets.mdf and Asset5_log.ldf) from the …\mssql\data folder to a data folder on the target server. To attach the Asset5 database, you can use

 EXEC sp_attach_db @dbname = 'Assets',                   @filename1 = 'c:\Program Files\Microsoft SQL ',                              + 'Server\MSSQL.1\mssql\data\Asset5.mdf'                   @filename2 = 'c:\Program Files\Microsoft SQL '                              + 'Server\MSSQL.1\mssql\data\Asset5_log.ldf' 

If your database consists of more files, simply add them to the list of parameters. But if your database contains just one data file, you can use an alternative command:

 EXEC sp_attach_single_file_db                 @dbname = 'Assets',      @physname = 'c:\Program Files\Microsoft SQL ',                  + 'Server\MSSQL.l\mssql\data\Asset5.mdf' 

Tip 

There is no harm in dropping the transaction log file and attaching just the data file (as long as you do not have some special reason, such as replication, to preserve the log).

You can execute these Transact-SQL statements manually in the Query window of Management Studio, from SQLCMD or from the setup program. The setup program can use the command-prompt utility SQLCMD to run a script file or use ADO.NET to execute the script.

Note 

I have chosen this method for deployment of the sample database to your computer.

Attach and Detach in Management Studio

The previous operations are possible through Management Studio commands and windows. To detach a database:

1. Open the context-sensitive menu of the database and select Tasks | Detach.

2. The program will open the Detach Database window that shows if the database is ready for the operation. For example, it is possible that some users are still connected to the database (see Figure 18-1).

   
   Figure 18-1: Detach Database in Management Studio

3. You can decide to wait and e-mail users to exit or you can use the Drop Connections option.

To attach a database on another server, you need to

1. Copy data and log files to a data folder on the target server.

2. Open the context-sensitive menu of the Databases node in the Object Browser and choose Attach.

3. The program will open the Attach window. Click the Add button and browse for the data file (.mdf) of your database. This will automatically load all the remaining files that are part of the database (see Figure 18-2).

   
   Figure 18-2: Attach Databases window in Management Studio

Backup and Restore

Another solution is based on creating a backup of the database on a development server and then restoring the database on a test or a production server. Again, this can be performed manually or it can be scripted and included in the setup program.

Potential Problems

Unfortunately, these techniques will not restore the links between server logins and database users. Server logins are stored in the master database; on different servers, different logins will have different IDs. Database users are stored in each user database. One of the parameters for a database user is the ID of the login to which it is attached.

However, that ID is likely to refer to a different login on the production server. The simplest way to handle this problem is either to create all users again using Management Studio or a script that you have prepared in advance, or to use roles instead of users as the foundation of your security solution. See Chapter 19 for more information. SQL Server offers another solution to this problem—see "Synchronization of Login and Usernames" in Chapter 19.

Another disadvantage to these methods is that you have to maintain a "clean" database—a database that contains just database objects and seed data. Such a database can be delivered to a customer, but it cannot be used for development and testing. In both development and test environments, you need to add test data in order to test all features. You need to develop either scripts for adding test data or, alternatively, scripts for removing test data from a development database.