The following sections provide steps for using Microsoft SQL Server to dump your database to a backup device.
It is useful to keep the result sets of SELECT statements against the sysdatabases, sysdevices, and sysusages tables in the master database.
Note In some cases, it is also useful to store information from syslogins, sysremotelogins, sysservers, and sysconfigures.
To store these result sets
Make sure SQL Server is running.
In SQL Enterprise Manager, make a note of the available backup devices.
If you are unsure of how to do this, see "Backing Up and Restoring" in SQL Server Books Online.
Go to a query window.
Execute the following:
use master
go
print 'select * from sysdatabases'
select * from sysdatabases
print 'select * from sysdevices'
select * from sysdevices
print 'select * from sysusages'
select * from sysusages
Note If you've chosen to store other information, enter these additional lines:
print 'select * from syslogins'
select * from syslogins
print 'select * from sysremotelogins'
select * from sysremotelogins
print 'select * from sysservers'
select * from sysservers
print 'select * from sysconfigures'
select * from sysconfigures
Save the result sets to a directory other than \Sql, \Sql60, or any of their subdirectories.
Note This step assumes that you are dumping your result sets before upgrading to SQL Server version 6.5. If you choose to dump your SQL Server version 6.5 database, do not save the result sets to the \Mssql directory.
Stop SQL Server.
The next step to backing up your SQL Server database is to execute a series of consistency checks and a stored procedure on each database on your SQL Server.
Go to a Windows NT command prompt and change your directory to the \Sql\Binn or \Sql60\Binn directory.
Note If you are dumping your SQL Server version 6.5 database, change your directory to the \Mssql\Binn directory.
Execute the following:
sqlservr -c -m
Then, minimize the command-prompt window.
Note -c starts SQL Server independent of the Windows NT Service Control Manager; -m starts SQL Server in single-user mode.
Copy the Sqldump.sql script from the BackOffice Resource Kit CD-ROM to the \Sql\Binn or \Sql60\Binn directory.
Note If you are dumping your SQL Server version 6.5 database, copy Sqldump.sql to the \Mssql\Binn directory.
Open another Windows NT command-prompt window, change to the \Sql\Binn or \Sql60\Binn directory, and execute:
isql -Usa -P password -S server_name -n -iSqldump.sql oOutput.sql
The Sqldump.sql script creates a template that runs on each database.
Note If you are dumping your SQL Server version 6.5 database, change to the \Mssql\Binn directory before executing the above script.
Use the output file created in step 4 as the input file and create a new output file for the result set of the next query as follows:
isql -Usa -P password -S server_name -n -iOutput.sql -oNewoutput.sql
This step runs sp_helpdb, DBCC CHECKDB, and DBCC NEWALLOC on each database, then saves the output to a new file.
To dump your databases to the backup device
Check Newoutput.sql for any errors. Consult your system administrator if there are any errors.
If the databases are clean, proceed to dumping each database as follows:
isql -Usa -P password -S server_name -Q"dump database database_name to dump_device_name"
where dump_device_name is one of the backup devices you noted during the "Store Result Sets" section earlier in this part.
After this completes, take SQL Server out of single-user mode as follows: