MSDE 2000 Installation How-To (continued)

Some Helpful Tips

As I come across helpful things I think are worth adding, I'll include them here.

Backup/Restore

A common question in the newsgroups is "How do I restore a SQL7/2000 database backup to my MSDE installation?" It is actually very simple... just run the following command in one of the GUI tools as if you were executing a SQL statement:

RESTORE DATABASE <name of your database>
FROM DISK='<path to your SQL BAK file>'
WITH
MOVE '<name of your database>_Data' TO '<path to the restored database file>.mdf',
MOVE '<name of your database>_Log' TO '<path to the restored database file>.ldf',
STATS=5

The backup should then restore itself to your MSDE system. Next, run the following in the GUI tool:

sp_updatestats

You should be able to connect to your MSDE and run queries on your restored tables.

Scheduled Backup

To perform a scheduled backup, create a batch (.bat) file and pass the backup parameters to OSQL.exe. Then use the Scheduler utility included with Windows to run the batch file. Personally I embed a random number into the filename and sort the directory by date... this way you are creating a daily backup file the way Enterprise Manager does it. This makes it easy to restore a previous day's version of your database.

Batch file example (all in one line, replace <>'s with what is appropriate for your setup):

osql -U sa -P <your sa password> -S <whatever appears in Server Manager as your Web Server name> /Q"BACKUP DATABASE <database name> TO DISK='<path to backup file, including filename>' WITH INIT"

If you want to embed a random number in your filename, use the %Random% parameter. For example, DISK='c:\backup\mydatabase_%Random%.bak' will create a filename that looks like 'c:\backup\mydatabase_9238.bak'. Sort the directory by file creation date and you'll know exactly which file corresponds to each day.