Backup and Restore (Level 2)
| SQL Server 6.x | SQL Server 2000 |
|---|---|
| Using the SKIP and INIT clauses of the DUMP statement together overwrote the contents of the backup device unconditionally. | The SKIP and INIT clauses of the BACKUP statement preserve the Microsoft Tape Format media header. In some situations, this prevents overwriting the backup contents. The FORMAT clause overwrites the media unconditionally, generating a new header, and is required for media used for the first time or when necessary to overwrite the media header.
Expect different results as compared to earlier versions of SQL Server. If the media is empty, SKIP and INIT act the same as the FORMAT clause of the BACKUP or DUMP statements and write a new media header. If the media is not empty, SKIP and INIT do not write a new media header. |
| The LOAD statement did not create the database automatically when restoring the database backup. | It is no longer necessary to create the database before restoring it. The RESTORE statement re-creates the database automatically, including all files. However, database devices are not re-created in sysdevices. These devices are supported only for backward compatibility. After restoration, databases originally created using devices (DISK INIT) appear as if they had been created using SQL Server 2000 file syntax.
Expect different results as compared to earlier versions of SQL Server. Consider using the new syntax in CREATE DATABASE and ALTER DATABASE for specifying files. |
| The NO_LOG clause of DUMP was used only when you ran out of space in the database and could not use DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log. The NO_LOG clause removes the inactive part of the log without making a backup copy of it, and saves space by not logging the operation. The TRUNCATE_ONLY clause of the DUMP statement removed the inactive part of the log without making a backup copy of it. | The NO_LOG and TRUNCATE_ONLY clauses of RESTORE are synonyms. Both clauses of BACKUP now remove the inactive part of the log without making a backup copy of it and truncate the log.
Expect different results as compared to earlier versions of SQL Server. Expect the NO_LOG and TRUNCATE_ONLY clauses of the BACKUP or DUMP statements to behave identically. |
| Recovery of multiple transaction logs could be performed without special keywords in the LOAD statement. | It is no longer possible to restore multiple transaction logs without using the WITH clauses of the RESTORE statement.
Expect different results as compared to earlier versions of SQL Server. Use the appropriate RESTORE syntax for restoring a database with multiple transaction logs as shown in the following examples. All but the last RESTORE statement should specify the NORECOVERY clause. |
| When loading a database, all database options of sp_dboption were unaffected and had to be set manually. | Changes to all sp_dboption database settings (except the offline, merge publish, published, and subscribed settings) are logged, like any other change. When a database is restored and recovered, all database options of sp_dboption are rolled forward. Every database option will be in its expected state at the time when recovery finished, consistent with the remainder of the database.
Expect different results as compared to earlier versions of SQL Server. It is no longer necessary to reset the database options after a RESTORE operation. |
Examples
A. Restore a database by applying a full database backup and multiple transaction logs
This example restores a database with multiple transaction log backups.
RESTORE DATABASE mydb
FROM mydb
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log1
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY
| SQL Server 6.x | SQL Server 2000 |
|---|---|
| A warm standby server could be brought up in read-only mode between recovery of each transaction log, provided that the no chkpt. on recovery option of sp_dboption was enabled. | A warm standby server can be brought up in read-only mode between transaction log restore operations if an undo file is used.
Expect different results as compared to earlier versions of SQL Server. Use an undo file for a warm standby server using the STANDBY clause of RESTORE, as shown in the following example. |
B. Restore a database using the STANDBY clause and an undo file
This example brings the server up to allow write operations on the databases by using a final, necessary RESTORE statement.
RESTORE DATABASE mydatabase
FROM mydb_backup
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log1
WITH RECOVERY STANDBY (FILENAME = 'c:\mssql\data\mydbundo.dat')
RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY STANDBY (FILENAME = 'c:\mssql\data\mydbundo.dat')
RESTORE DATABASE mydb
WITH RECOVERY