Through experience I have found it necessary to maintain an up-to-date copy of the various SQL Server settings. It is also important to hold this information separately from the live server in case of complete server failure.
The following method is one that I have implemented successfully using
three stored procedures and a scheduled task. This task dumps the following
SQL Server information to a the text file
Recovery.out on a floppy
disk along with SQL scripts to create all existing databases.
- Date of Generation
- Server Name and User Name
- Current SQL Server Version
- Current Sort Order & Character Set
- Current Standard & Advanced Configuration Options
- Current Database Structure
- Current Device Structure
In order to execute the commands contained within the stored procedures, the SA login is used, but this exposes the SA password. In order to hide the password, it is stored within a task, created by the SA. Since only the SA can examine SA created tasks through the 'Manage Scheduled Tasks' window the password cannot be seen by users. This does not fully protect the password though, because the DBO of database MSDB can examine the system table SYSTASKS, which contains the task information (see table below).
|User||SA Task||SYSTASKS Table|
The 'Recovery Disk' task is created through the execution of the stored procedure, sp_recoverytask. This task will contain the SA password within the command, but this will only be visible by SA and MSDB DBO. The SA password [VarChar (30)] is passed to sp_recoverytask as a variable, this ensures that the password is not hard coded. Along with the password, the name for the task and an indicator are passed to the stored procedure.
The task name [VarChar (100)] is the name that the task will be referred to, and the overwrite indicator [TinyInt] determines if an existing task of the same name, will be overwritten. By default this is set to 0 and the task will not be overwritten. To overwrite set to 1.
This EXECUTE command will create a task Recovery Disk, scheduled to run at 12:00 am each day, with the command:EXECUTE sp_recoverytask 'Recovery Disk', 'SQLServer1', 1
This task, Recovery Disk, will execute sp_recoverydisk, which in turn executes sp_structurescript to produce file recovery.out, and sp_help revdatabase to produce script files for each database (e.g. master.sql and mydb.sql) and one script file for all databases (alldbs.sql).EXECUTE sp_recoverydisk "SQLServer1"
Creation of stored procedures in master database:
- Download the file sqlsecur.sql (8kB)
- Open file sqlsecur.sql in SQL Query tool, with master database selected under SA login
- Execute the script
Creation of a task to execute the stored procedure:
- Execute procedure, sp_recoverytask, with parameters Task Name, SA Password and Overwrite Indicator:
- Confirm the task works correctly by manually running it, to create the files on floppy disk
- Confirm the output within the file is correct and contains no errors (e.g. login failures)
EXECUTE sp_recoverytask 'Recovery Disk', 'SQLServer1', 1
The floppy disk can now be left permanently in the drive of the server, though I recommend changing the boot options of the machine so as to boot from the hard drive first, to ignore the non-bootable disk.
The scheduled time of the task can be modified.
Altough the SA password is been held within table msdb..systasks, and only visible by SA and DBO, SELECT permissions on this table should be restricted.