Microsoft SQL Server 2000 Database Backup

How to create a database backup (Transact-SQL)

To create a database backup

1. Execute the BACKUP DATABASE statement to create the database backup, specifying:
• The name of the database to back up.
• The backup device where the database backup will be written.
2. Optionally, specify:
• The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
• The SKIP and INIT clauses to overwrite the backup media even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
• The FORMAT clause when using media for the first time to completely initialize the backup media and rewrite any existing media header.

The INIT clause is not required if the FORMAT clause is specified.

Important  Use extreme caution when using the FORMAT or INIT clauses of the BACKUP statement, as this will destroy any backups previously stored on the backup media.

Examples

This example backs up the entire MyNwind database to tape:

USE MyNwind

GO

BACKUP DATABASE MyNwind

   TO TAPE = '\\.\Tape0'

   WITH FORMAT,

   NAME = 'Full Backup of MyNwind'

GO

 

 

 

How to restore a database backup (Transact-SQL)

To restore a database backup

Important  The system administrator restoring the database backup must be the only person currently using the database to be restored.

1. Execute the RESTORE DATABASE statement to restore the database backup, specifying:
• The name of the database to restore.
• The backup device from where the database backup will be restored.
• The NORECOVERY clause if you have a transaction log or differential database backup to apply after restoring the database backup.
2. Optionally, specify:
• The FILE clause to identify the backup set on the backup device to restore.

Examples

This example restores the MyNwind database backup from tape:

USE master

GO

RESTORE DATABASE MyNwind

   FROM TAPE = '\\.\Tape0'

GO

 

 

 

How to restart an interrupted backup operation (Transact-SQL)

To restart an interrupted backup operation

• Execute the interrupted BACKUP statement again, specifying:
• The same clauses used in the original BACKUP statement.
• The RESTART clause.

Examples

This example restarts an interrupted database backup operation:

-- Create a database backup of the MyNwind database

BACKUP DATABASE MyNwind

   TO MyNwind_1

-- The backup operation halts due to power outage.

-- Repeat the original BACKUP statement specifying WITH RESTART

BACKUP DATABASE MyNwind

   TO MyNwind_1 

   WITH RESTART

 

 

How to restart an interrupted restore operation (Transact-SQL)

To restart an interrupted restore operation

Important  The system administrator restoring the backup must be the only person currently using the database to be restored.

• Execute the interrupted RESTORE statement again, specifying:
• The same clauses used in the original RESTORE statement.
• The RESTART clause.

Examples

This example restarts an interrupted restore operation:

-- Restore a database backup of the MyNwind database

RESTORE DATABASE MyNwind

   FROM MyNwind_1

GO

-- The restore operation halted prematurely.

-- Repeat the original RESTORE statement specifying WITH RESTART

RESTORE DATABASE MyNwind 

   FROM MyNwind_1

   WITH RESTART

GO

 

 

How to create a transaction log backup (Transact-SQL)

To create a transaction log backup

1. Execute the BACKUP LOG statement to back up the transaction log, specifying:
• The name of the database to which the transaction log to back up belongs.
• The backup device where the transaction log backup will be written.
2. Optionally, specify:
• The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
• The SKIP and INIT clauses to overwrite the backup media even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
• The FORMAT clause, when using media for the first time, to completely initialize the backup media and rewrite any existing media header.

The INIT clause is not required if the FORMAT clause is specified.

Important  Use extreme caution when using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.

Examples

This example creates a transaction log backup for the MyNwind database to the previously created named backup device, MyNwind_log1:

BACKUP LOG MyNwind

   TO MyNwind_log1

GO

 

 

How to back up the transaction log when the database is damaged (Transact-SQL)

To create a backup of the currently active transaction log

1. Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:
• The name of the database to which the transaction log to back up belongs.
• The backup device where the transaction log backup will be written.
• The NO_TRUNCATE clause to back up the transaction log without truncating the inactive part of the transaction log.

This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file(s) is accessible and undamaged.

2. Optionally, specify:
• The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
• The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
• The FORMAT clause, when using media for the first time, to completely initialize the backup media and rewrite any existing media header.

The INIT clause is not required if the FORMAT clause is specified.

Important  Use extreme caution when using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.

Examples

This example backs up the currently active transaction log for the MyNwind database even though MyNwind has been damaged and is inaccessible. The transaction log, however, is undamaged and accessible:

BACKUP LOG MyNwind

   TO MyNwind_log1

   WITH NO_TRUNCATE

GO

 

 

How to apply a transaction log backup (Transact-SQL)

It is not possible to apply a transaction log backup:

• Unless the database or differential database backup preceding the transaction log backup is restored first.
• Unless all preceding transaction logs created since the database or differential database were backed up are applied first.
• If the database has already recovered and all outstanding transactions have either been rolled back or rolled forward.

To apply a transaction log backup

1. Execute the RESTORE LOG statement to apply the transaction log backup, specifying:
• The name of the database to which the transaction log will be applied.
• The backup device where the transaction log backup will be restored from.
• The NORECOVERY clause if you have another transaction log backup to apply after the current one, otherwise specify the RECOVERY clause.
2. Repeat Step 1 for each transaction log backup you need to apply.

Examples

A. Applying a single transaction log backup

This example applies a transaction log backup to the MyNwind database.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH RECOVERY

GO

B. Applying multiple transaction log backups

This example applies multiple transaction log backups to the MyNwind database.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH NORECOVERY

GO

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH NORECOVERY

GO

RESTORE LOG MyNwind

   FROM MyNwind_log3

   WITH RECOVERY

GO

 

 

 

How to create a differential database backup (Transact-SQL)

To create a differential database backup

Important  It is not possible to create a differential database backup unless the database has been backed up first.

1. Execute the BACKUP DATABASE statement to create the differential database backup, specifying:
• The name of the database to back up.
• The backup device where the database backup will be written.
• The DIFFERENTIAL clause, to specify that only the parts of the database that have changed after the last database backup was created are backed up.
2. Optionally, specify:
• The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
• The SKIP and INIT clauses to overwrite the backup media even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
• The FORMAT clause when using media for the first time to completely initialize the backup media and rewrite any existing media header.

The INIT clause is not required if the FORMAT clause is specified.

Important  Use extreme caution when using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media.

Examples

This example creates a full and a differential database backup for the MyNwind database.

-- Create a full database backup first.

BACKUP DATABASE MyNwind 

   TO MyNwind_1 

   WITH INIT

GO

-- Time elapses.

-- Create a differential database backup, appending the backup

-- to the backup device containing the database backup.

BACKUP DATABASE MyNwind

   TO MyNwind_1

   WITH DIFFERENTIAL

GO

 

 

 

How to restore a differential database backup (Transact-SQL)

To restore a differential database backup

1. Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the database backup preceding the differential database backup.
2. Execute the RESTORE DATABASE statement to restore the differential database backup, specifying:
• The name of the database to which the differential database backup will be applied.
• The backup device where the differential database backup will be restored from.
• The NORECOVERY clause if you have transaction log backups to apply after the differential database backup is restored, otherwise specify the RECOVERY clause.

Examples

A. Restoring a database and differential database backup

This example restores a database and differential database backup of the MyNwind database.

-- Assume the database is lost at this point. Now restore the full 

-- database. Specify the original full backup and NORECOVERY.

-- NORECOVERY allows subsequent restore operations to proceed.

RESTORE DATABASE MyNwind

   FROM MyNwind_1

   WITH NORECOVERY

GO

-- Now restore the differential database backup, the second backup on 

-- the MyNwind_1 backup device.

RESTORE DATABASE MyNwind

   FROM MyNwind_1

   WITH FILE = 2,

      RECOVERY

GO

B. Restoring a database, differential database, and transaction log backup

This example restores a database, differential database, and transaction log backup of the MyNwind database.

-- Assume the database is lost at this point. Now restore the full 

-- database. Specify the original full backup and NORECOVERY.

-- NORECOVERY allows subsequent restore operations to proceed.

RESTORE DATABASE MyNwind

   FROM MyNwind_1

   WITH NORECOVERY

GO

-- Now restore the differential database backup, the second backup on 

-- the MyNwind_1 backup device.

RESTORE DATABASE MyNwind

   FROM MyNwind_1

   WITH FILE = 2,

      NORECOVERY

GO

-- Now restore each transaction log backup created after

-- the differential database backup.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH NORECOVERY

GO

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH RECOVERY

GO

 

 

 

How to recover a database without restoring (Transact-SQL)

To recover a database without restoring

• Execute the RESTORE DATABASE statement, specifying:
• The name of the database to be recovered.
• The RECOVERY clause.

Examples

This example recovers the MyNwind database without restoring from a backup.

-- Restore database using WITH RECOVERY.

RESTORE DATABASE MyNwind

   WITH RECOVERY

 

 

How to restore to the point of failure (Transact-SQL)

To restore to the point of failure

1. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log.
2. Execute the RESTORE DATABASE statement using the NORECOVERY clause to restore the database backup.
3. Execute the RESTORE LOG statement using the NORECOVERY clause to apply each transaction log backup.
4. Execute the RESTORE LOG statement using the RECOVERY clause to apply the transaction log backup created in Step 1.

Examples

This example backs up the currently active transaction log of the MyNwind database, even though MyNwind is inaccessible, and then restores the database to the point of failure using previously created backups:

-- Back up the currently active transaction log.

BACKUP LOG MyNwind

   TO MyNwind_log2

   WITH NO_TRUNCATE

GO

-- Restore the database backup.

RESTORE DATABASE MyNwind

   FROM MyNwind_1

   WITH NORECOVERY

GO

-- Restore the first transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH NORECOVERY

GO

-- Restore the final transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH RECOVERY

GO

 

 

How to set up, maintain, and bring online a standby server (Transact-SQL)

Setting up a standby server generally involves creating a database backup and periodic transaction log backups at the primary server, and then applying those backups, in sequence, to the standby server. The standby server is left in a read-only state between restores. When the standby server needs to be made available for use, any outstanding transaction log backups, including the backup of the active transaction log, from the primary server, are applied to the standby server and the database is recovered.

To create backups on the primary server

1. Execute the BACKUP DATABASE statement to create the database backup.
2. Execute the BACKUP LOG statement to create a transaction log backup.
3. Repeat Step 2 for each transaction log you want to create over time.

To set up and maintain the standby server

1. Execute the RESTORE DATABASE statement using the STANDBY clause to restore the database backup created in Step 1 on the primary server. Specify the name of the undo file that contains the contents of data pages before uncommitted transactions affecting those pages were rolled back.
2. Execute the RESTORE LOG statement using the STANDBY clause to apply each transaction log created in Step 2 on the primary server.
3. Repeat Step 2 for each transaction log created on the primary server.

To bring the standby server online (primary server failed)

1. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back up the currently active transaction log. This is the last transaction log backup that will be applied to the standby server when the standby server is brought online.
2. Execute the RESTORE LOG statement using the STANDBY clause to apply all transaction log backups, including the active transaction log backup created in Step 1, that have not yet been applied to the standby server.
3. Execute the RESTORE DATABASE WITH RECOVERY statement to recover the database and bring up the standby server.

Examples

This example sets up the MyNwind database on a standby server. The database can be used in read-only mode between restore operations.

-- Restore the initial database backup on the standby server.

USE master

GO

RESTORE DATABASE MyNwind

   FROM MyNwind_1 

   WITH STANDBY = 'c:\undo.ldf'

GO

-- Apply the first transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH STANDBY = 'c:\undo.ldf'

GO

-- Apply the next transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH STANDBY = 'c:\undo.ldf'

GO

-- Repeat for each transaction log backup created on the 

-- primary server.

--

-- Time elapses.. .. ..

--

-- The primary server has failed. Back up the 

-- active transaction log on the primary server.

BACKUP LOG MyNwind

   TO MyNwind_log3

   WITH NO_TRUNCATE

GO

-- Apply the final (active) transaction log backup

-- to the standby server. All preceding transaction

-- log backups must have been already applied.

RESTORE LOG MyNwind

   FROM MyNwind_log3

   WITH STANDBY = 'c:\undo.ldf'

GO

-- Recover the database on the standby server, 

-- making it available for normal operations.

RESTORE DATABASE MyNwind

   WITH RECOVERY

GO

 

 

How to restore to a point in time (Transact-SQL)

To restore to a point in time

1. Execute the RESTORE DATABASE statement using the NORECOVERY clause.
2. Execute the RESTORE LOG statement to apply each transaction log backup, specifying:
• The name of the database to which the transaction log will be applied.
• The backup device from where the transaction log backup will be restored.
• The RECOVERY and STOPAT clauses. If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.

Examples

This example restores a database to its state as of 10:00 A.M. on July 1, 1998, and illustrates a restore operation involving multiple logs and multiple backup devices.

-- Restore the database backup.

RESTORE DATABASE MyNwind

   FROM MyNwind_1, MyNwind_2

   WITH NORECOVERY

GO

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

GO

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

GO

 

 

How to restore the master database (Transact-SQL)

To restore the master database

1. Start Microsoft® SQL Server™ in single-user mode.
2. Execute the RESTORE DATABASE statement to restore the master database backup, specifying:
• The backup device from where the master database backup will be restored.

Examples

This example restores the master database backup from tape without using a permanent (named) backup device.

USE master

GO

RESTORE DATABASE master

   FROM TAPE = '\\.\Tape0'

GO

 

 

How to back up files and filegroups (Transact-SQL)

To back up files and filegroups

• Execute the BACKUP DATABASE statement to create the file and filegroup backup, specifying:
• The name of the database to back up.
• The backup device where the database backup will be written.
• The FILE clause for each file to back up.
• The FILEGROUP clause for each filegroup to back up.

Examples

This example performs a backup operation with files and filegroups for the MyNwind database.

-- Back up the MyNwind file(s) and filegroup(s)

BACKUP DATABASE MyNwind

   FILE = 'MyNwind_data_1',

   FILEGROUP = 'new_customers',

   FILE = 'MyNwind_data_2', 

   FILEGROUP = 'first_qtr_sales'

   TO MyNwind_1

GO

 

 

How to restore files and filegroups (Transact-SQL)

To restore files and filegroups

Important  The system administrator restoring the files and filegroups must be the only person currently using the database to be restored.

1. Execute the RESTORE DATABASE statement to restore the file and filegroup backup, specifying:
• The name of the database to restore.
• The backup device from where the database backup will be restored.
• The FILE clause for each file to restore.
• The FILEGROUP clause for each filegroup to restore.
• The NORECOVERY clause. If the files have not been modified after the backup was created, specify the RECOVERY clause.
2. If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
• The name of the database to which the transaction log will be applied.
• The backup device from where the transaction log backup will be restored.
• The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.

The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).

Examples

This example restores the files and filegroups for the MyNwind database. Two transaction logs will also be applied, to restore the database to the current time.

USE master

GO

-- Restore the files and filesgroups for MyNwind.

RESTORE DATABASE MyNwind

   FILE = 'MyNwind_data_1',

   FILEGROUP = 'new_customers',

   FILE = 'MyNwind_data_2',

   FILEGROUP = 'first_qtr_sales'

   FROM MyNwind_1

   WITH NORECOVERY

GO

-- Apply the first transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH NORECOVERY

GO

-- Apply the last transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH RECOVERY

GO

 

 

How to restore files and filegroups over existing files (Transact-SQL)

To restore files and filegroups over existing files

Important  The system administrator restoring the files and filegroups must be the only person currently using the database to be restored.

1. Execute the RESTORE DATABASE statement to restore the file and filegroup backup, specifying:
• The name of the database to restore.
• The backup device from where the database backup will be restored.
• The FILE clause for each file to restore.
• The FILEGROUP clause for each filegroup to restore.
• The REPLACE clause to specify that each file can be restored over existing files of the same name and location.
• The NORECOVERY clause. If the files have not been modified after the backup was created, specify the RECOVERY clause.
2. If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
• The name of the database to which the transaction log will be applied.
• The backup device from where the transaction log backup will be restored.
• The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.

The transaction log backups, if applied, must cover the time when the files and filegroups were backed up.

Examples

This example restores the files and filegroups for the MyNwind database, and replaces any existing files of the same name. Two transaction logs will also be applied to restore the database to the current time.

USE master

GO

-- Restore the files and filesgroups for MyNwind.

RESTORE DATABASE MyNwind

   FILE = 'MyNwind_data_1',

   FILEGROUP = 'new_customers',

   FILE = 'MyNwind_data_2',

   FILEGROUP = 'first_qtr_sales'

   FROM MyNwind_1

   WITH NORECOVERY,

   REPLACE

GO

-- Apply the first transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH NORECOVERY

GO

-- Apply the last transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH RECOVERY

GO

 

 

How to restore files to a new location (Transact-SQL)

To restore files to a new location

Important  The system administrator restoring the files must be the only person currently using the database to be restored.

1. Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the database backup.
2. Execute the RESTORE DATABASE statement to restore the database backup, specifying:
• The name of the database to restore.
• The backup device from where the database backup will be restored.
• The MOVE clause for each file to restore to a new location.
• The NORECOVERY clause. If the files have not been modified since the backup was created, specify the RECOVERY clause.
3. If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
• The name of the database to which the transaction log will be applied.
• The backup device from where the transaction log backup will be restored.
• The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.

The transaction log backups, if applied, must cover the time when the files and filegroups were backed up.

Examples

This example restores two of the files for the MyNwind database that were originally located on the C:\ drive to new locations on the D: \drive. Two transaction logs will also be applied to restore the database to the current time. The RESTORE FILELISTONLY statement is used to determine the number and logical and physical names of the files in the database being restored.

USE master

GO

-- First determine the number and names of the files in the backup.

RESTORE FILELISTONLY

   FROM MyNwind_1

-- Restore the files for MyNwind.

RESTORE DATABASE MyNwind

   FROM MyNwind_1

   WITH NORECOVERY,

   MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf', 

   MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'

GO

-- Apply the first transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log1

   WITH NORECOVERY

GO

-- Apply the last transaction log backup.

RESTORE LOG MyNwind

   FROM MyNwind_log2

   WITH RECOVERY

GO

 

 

How to restore a database with a new name (Transact-SQL)

To restore a database with a new name

1. Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the database backup.
2. Execute the RESTORE DATABASE statement to restore the database backup, specifying:
• The new name for the database.
• The backup device from where the database backup will be restored.
• The NORECOVERY clause if you have transaction log backups to apply after the file backups are restored. Otherwise, specify the RECOVERY clause.

The transaction log backups, if applied, must cover the time when the files were backed up.

• The MOVE clause for each file to restore to a new location if the file names already exist. For example, creating a copy of an existing database on the same server for testing purposes may be necessary. In this case, the database files for the original database already exist, and so different file names need to be specified when the database copy is created during the restore operation.

Examples

This example creates a new database called MyNwind2_Test. MyNwind2_Test is a copy of the existing MyNwind2 database that comprises two files: MyNwind2_data and MyNwind2_log. Because the MyNwind2 database already exists, the files in the backup need to be moved during the restore operation. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored.

USE master

GO

-- First determine the number and names of the files in the backup.

-- MyNwind_2 is the name of the backup device.

RESTORE FILELISTONLY

   FROM MyNwind_2

-- Restore the files for MyNwind2_Test.

RESTORE DATABASE MyNwind2_Test

   FROM MyNwind_2

   WITH RECOVERY,

   MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf', 

   MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'

GO