Backing Up the Database
Any application that uses a DBMS needs to help users protect their data and recover from disasters. The backup strategy I describe here is only part of a more comprehensive backup regimen that includes periodically copying the backup file to an offsite device. SQL Server (MSDE) does a great job of protecting the data from corruption caused by unexpected crashes or power outages. However, it doesn't protect you from more catastrophic failures. Be sure to move the backup file to another (offsite) location to prevent a disaster from destroying the backup file along with the database.
You can use several techniques to back up an MSDE database. One of the easiest ways is to use Enterprise Manager wizards to set up a SQL Server Agent maintenance job. But if you don't want to set up a job, you can easily write the code yourself to back up the database. You can choose to have the user do the backup, or do it yourself without their knowledge.
My sample application lets users choose when to back up, but the application reminds them to back up if they haven't done so for a couple of days. Because this database doesn't have much data and changes are infrequent, skipping a couple of days shouldn't be a problem. The code at callout H shows the routine to back up the database. Note that I'm using an open ADO Connection cn to execute the backup action query. This code doesn't use SQL-DMO; it simply executes T-SQL's BACKUP DATABASE command with a few options to make sure the database backup file is initialized each time it's backed up. If you have a more complex or more heavily used database, you probably won't want to use the WITH INIT option but instead will let the BACKUP command append each backup to the existing backup set. That way, you can restore the database from any of the existing backups. The sample code also records the date of the last backup to the registry so that you can keep track of when to prompt for another backup.
When you set up your database script, be sure the user has rights to back up and restore the database. If you set up a special admin account, you'll have to use this account to open another connection before attempting the backup.
Restoring the Database
Saving the current state of the database to a file is only half (or less) of the backup/restore process: A backup is no good if you can't restore the data later. Unfortunately, the restore process is a little more complex. SQL Server requires that no users be connected to the database during the restore operation. In MSDE for SQL Server 7.0, you have to jump through the hoops I describe below to get the server stopped and restarted in single-user mode. In SQL Server 2000, switching to single-user mode isn't nearly as tough—you can simply execute an ALTER DATABASE command to change the database state. Callout I's ALTER DATABASE command, which my colleague Kimberly Tripp-Simonnet showed me, completes all transactions (or rolls them back) and disconnects all users from the database. This command tells the server to stop further processing on the server, stop any pending transactions, and roll back the operations if they don't end within 10 seconds. To execute this T-SQL command, you need to have sa or db-creator permissions or to log on as a member of the backup operators role.
Note that shutting down a live production server isn't a good idea unless you know it's a single-user DBMS. MSDE can run in multiuser mode, so if yours does, be sure to get all other users logged off when maintenance operations are taking place.
If the MSDE you're using is based on SQL Server 7.0, you need to switch the server to single-user mode. I've tried several techniques to accomplish this switch, but they don't always work consistently. Although you can shut down all ADO connections (including Enterprise Manager, Query Analyzer, and other tools), the OLE DB session pool keeps connections open for a minute or longer after you "close" them. To get around this problem, I had SQL-DMO shut down the server, wait a few seconds, then restart it. If you try to restart SQL Server too soon after shutting it down, it seems to stall.
After the server is shut down and you've waited a couple of seconds, you can attempt a restart. Again, you might want to adjust the timing for your target system. To execute the RESTORE command, you'll have to log on as sa, db-creator, or a member of the backup operators role. After restarting, you'll probably want to wait several seconds to let the server come up to speed. The routine at callout J restarts, waits, then executes the RestoreDatabase query. The error handler at callout K is ready to trap Server not found errors, then wait another couple of seconds and try again.
Next, the code at callout L sets the working database's SingleUser DBOption to True to prevent other connections from interfering with the restore operation. This setting is a requirement for SQL Server 7.0's RESTORE command. With your server in single-user mode, you can execute the RESTORE DATABASE command. In this case, you use the SQL-DMO Server.ExecuteImmediate method, which simply executes the action query that callout M shows—the T-SQL RESTORE DATABASE command.
If the restore succeeds, you can return the database to multiuser mode immediately. After closing the SQL-DMO Server object, you're ready to return to the application. Tell the user you're finished, then shut down the application to bring in the newly restored data. The code at callout N takes care of all these steps.
Packaging
When it's time to deploy your application, you'll need to include several additional pieces in your compressed cabinet format (CAB) deployment package that the Visual Studio Installer or the VB Package and Deployment Wizard created for you. The setup routine you created through the Package and Deployment Wizard needs to copy these files to your app.path directory. In addition, your code needs to extract these files out of this to-be-determined app.path directory. These files include
- The database (.mdf, .ldf) files you created with sp_detach_db. Depending on the technique you used to append the files, you might also have to save the transaction log.
- A Universal Data Link (UDL) file that points to your database and possibly includes the user login ID and password.
- The SetPermissions.SQL script that sets up the user and his or her database permissions. Remember, you created this script by using Enterprise Manager to script your working database to a file.
I expect most of the existing SQL-DMO functionality to appear in the new ADO.NET SqlClient provider before too long, so you won't have to use a separate data-access interface to manage your MSDE server. I also expect to see MSDE take an increasingly important role in the coming years as developers search for a more robust and better performing replacement for the Microsoft Access (Jet) database engine. But today, using SQL-DMO can help make your MSDE experience more productive.