Restoring 6.5 databases
-
Can anyone tell me, or point me to a link, on how to restore a 6.5 database. Not just a user database, but the whole kit and kaboodle. Starting with installing the SQL Server 6.5 binaries.
It has been a long time since I have done this and am trying to help create a DR strategy.
Thanks,
Jeff
"Keep Your Stick On the Ice" ..Red Green -
Wow, you're asking a tough one. Not sure how much there is on the web. Swynk used to have some stuff, might search there (it's not dbjournal or something).
I do know that you need to know the exact size and order or creation dates for your devices to do the restore
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Thanks Steve. Not finding a lot of information out there
But, I believe somewhere I have a cd that contains some old restore scripts.
"Keep Your Stick On the Ice" ..Red Green -
Before you can reload the master database, you must start SQL Server in single-user mode.
To start SQL Server in single-user mode
1. If it is already running, stop the server.
2. From a command prompt, type:
sqlservr /c /dmaster_device /m
where
/c
Starts SQL Server independent of the Windows NT Service Control Manager.
/dmaster_device_ path
Specifies a physical name for the MASTER database device. For example:
/dc:\sql60\data\master.dat
/m
Specifies single-user mode.
For example:
sqlservr /c /dc:\sql60\data\master.dat /m
Restore the master Database from the Most Recent Backup
Reload the most recent dump of the master database using SQL Enterprise Manager or the LOAD DATABASE statement.
When using SQL Enterprise Manager, in the Server Manager window the server status may appear red since the server was started independent of the Windows NT Service Control Manager. However, you can ignore this status. You will be able to connect and then restore the master database from the Database Backup/Restore window.
You can also restore the master database using the LOAD statement from isql (or another query interface).
To restore the master database using the LOAD statement
· Type:
LOAD DATABASE dbname
FROM dump_device [, dump_device2 [..., dump_device32]]
where
dbname
Is the database name (in this case, master).
dump_device
Is the logical name of the dump device.
For example:
LOAD DATABASE master FROM tape0
Important When the load of master is complete, the server automatically shuts itself down. An error message appears, stating that the server connection has been broken. You can then restart the server normally.
Additional options can be used with the LOAD DATABASE statement. For information, see the Microsoft SQL Server Transact-SQL Reference.
Apply Changes to the master Database
If there have been no changes to the master database since the last dump, then you are done. Restart the server normally (in multiuser mode).
If login IDs or devices have been added to or dropped from the master database since the last dump, those changes must be reapplied. Restart the server and reapply the changes manually or from saved batch files.
Restore the msdb Database
The msdb database supports SQL Executive and provides a storage area for scheduling information. The schedules that you implement using SQL Enterprise Manager are maintained in the msdb database. This includes such things as the tasks that you schedule from the Task Scheduling window, the automatic backups you schedule from the Database Backup/Restore window, and all replication tasks (which are automatically created by the system if the server is configured as a replication distributor).
During installation of a server, the setup program automatically creates two devices (of 2 MB and 1 MB) on the same disk drive as the master database, and then places the msdb database on the 2 MB device (MSDBDATA) and its transaction log on the 1 MB device (MSDBLOG). Scheduling information is then stored in this database.
During a rebuild of the master database, the setup program drops and re-creates the msdb database, which results in a loss of all scheduling information. Therefore, after the master database has been restored, you must perform these steps to restore msdb:
To restore msdb
1. If necessary, expand the MSDBDATA device, and then allocate the expanded space to the msdb database.
The msdb database must have as much or more space allocated to it as was allocated to it before the master database was rebuilt.
2. Restore the msdb database from the most recent database backup.
3. Apply all transaction log dumps that were performed after that database dump.
4. Re-create any scheduled tasks that were implemented after the last transaction log dump.
In general, the msdb database can be treated as a user database, as described in Re-creating and Reloading Lost Databases.
To Restore User DB’s
/*First Recreate Devices*/
DISK INIT
NAME = 'DEVICE2',
PHYSNAME = 'd:\sqldata\device2.dat',
VDEVNO = 2,
SIZE = 8192
This example creates a 125 MB (64,000 2K pages) device called DEVICE3. This device is created on a third logical drive. The SQLDATA directory must exist prior to the execution of this statement.
DISK INIT
NAME = 'DEVICE3',
PHYSNAME = 'e:\sqldata\device3.dat',
VDEVNO = 3,
SIZE = 64000
This example creates a single database (sales) with the data portion on DEVICE3 and the log portion on DEVICE4.
CREATE DATABASE sales
ON DEVICE3 = 125
LOG ON DEVICE4 = 60
For SQL Server 6.5 information, see LOAD Statement in What's New for SQL Server 6.5.
Restores a backup copy of a user database and its transaction log (LOAD DATABASE) or only the transaction log (LOAD TRANSACTION) from a dump that was created using the DUMP statement. The LOAD statement can also be used to retrieve header information from a database dump (LOAD HEADERONLY).
Syntax
Loading a database:
LOAD DATABASE {dbname | @dbname_var}
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH options
[[,] STATS [ = percentage]]]
Loading a transaction log:
LOAD TRANSACTION {dbname | @dbname_var}
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH options]
Loading header information:
LOAD HEADERONLY
FROM dump_device
where
dump_device =
{ dump_device_name | @dump_device_namevar}
| {DISK | TAPE | FLOPPY | PIPE} =
{'temp_dump_device' | @temp_dump_device_var}}
[VOLUME = {volid | @volid_var}]
options =
[[,] {UNLOAD | NOUNLOAD}]
[[,] {SKIP | NOSKIP}]
[[,] {FILE = fileno}]
DATABASE
Specifies that the complete database and transaction log are to be restored.
TRANSACTION
Specifies that only the transaction log is to be applied to this database. Transaction logs must be applied in sequential order. SQL Server checks the timestamps on the dumped transaction log to make sure that the transactions are being loaded into the correct database and in the correct sequence.
HEADERONLY
Retrieves all the volume and dump header information for all dumps on a particular dump device. For details about the header information returned, see "Volume and Header Information," later in this topic.
dbname | @dbname_var
Specifies the database from which the transaction log or complete database is being dumped. This name can be specified as a string or as a char or varchar variable.
dump_device_name | @dump_device_namevar
Is the logical name of the dump device as created by sp_addumpdevice. The dump device name can be specified as a string or as a char or varchar variable.
fileno
Is the specific file number to be loaded from the tape or disk device that contains multiple database dumps. The default is 1; load the first dump on the device.
{DISK | TAPE | FLOPPY | PIPE} =
'temp_dump_device' | @temp_dump_device_var
Allows backups to be loaded directly from temporary dump devices. These devices must already exist when the load occurs; they do not have entries in sysdevices. The device types of DISK, TAPE, and FLOPPY should be specified with the actual address (for example, complete path and filename) of the device. A device type of PIPE should specify the name of the named pipe that will be used by the client application. If specified as a variable (@temp_dump_dev_var), the device name can be specified as a string or as a char or varchar variable. If you are using either a network server with a UNC name or a redirected drive letter, specify a device type of DISK.
Important When specifying a striped dump, you can use both logical device names (or variables) and temporary device names (or variables). PIPE devices have been added to allow third-party vendors a flexible and powerful way to connect their own software. For typical Transact-SQL use, the PIPE device will not be used.
VOLUME = volid | @volid_var
Specifies the volume ID, volid, a 6-byte character string. If dumping to a new tape, this value is the name of the ANSI VOL1 label. For SQL Server, the default is SQ0001. For subsequent dumps, this value is used to validate the volume name of the dump device. If specified as a variable (@volid_var), the volume name can be specified as a string or as a char or varchar variable. It is not necessary, or recommended, to explicitly specify the volume ID.
UNLOAD
Specifies that the tape is automatically rewound and unloaded when the dump is finished. UNLOAD is set by default when a new user session is started. It remains set until that user specifies NOUNLOAD. This option is used only for tape devices.
NOUNLOAD
Specifies that the tape will not be unloaded automatically from the tape drive after a dump. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.
SKIP
Instructs the server not to read the ANSI TAPE device headers.
NOSKIP
Specifies that the server will read ANSI TAPE device headers. This is the default.
STATS = percentage
Returns the percentage of pages loaded in increments optionally set at the time of the load. If the percentage is not specified the statistics will be shown for each 10 percent of the load. This option is available only for database loads.
-
eosullivan,
That's awesome!!!
"Keep Your Stick On the Ice" ..Red Green -
Jeff W,
Be sure you *practice* the master and msdb restores on a test server somewhere. There is nothing like real life experience for these two potential problems. Problems tend to creep up on them sometimes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply