Search
Home
Articles
Backup
Books
Certification
FAQ
Products
Replication
Scripts
Seminars
Training
TSQL
MSDN Fourms
Philippine SSUG
Fort Worth SSUG
Oklahoma City SSDG
Resume
MHS Enterprises
BlowFrog Software
FilAm Software
AcrylicAcetate.com
Bargain Humidors
Western Humidor

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Differential backup a database to a tape device using a variable

declare @mydb sysname

select @mydb = ‘pubs’

exec sp_addumpdevice 'tape', 'tapebackup', '\\.\tape0'

backup database @mydb to tapebackup with differential, format, unload

Restoring a database

Restoring a database is something that you will hopefully never have to do on a production system. However, in the real world, this is something you will need to do at some point in your career.

The ability to restore a database is the final test of your backup plan. Oit has to be done correctly and the first time. You may ask why you need to do this correctly the first time? This is due to many factors. The first being time. You need to get the production system back onling as quickly as possible. Having a restore fail is one of the worts nightmares of any administrator. The largest reason though is in stability. You need to restore a database, because some problem occurred to corrupt the database. Having the restore fail usually puts the system in a worse state than it was in. The last thing you want to deal with during a crash scenario is creating more problems for yourself.

In previous versions of SQL Server, you needed to recreate the database with the same segmentation as it was originally if having to restore a corrupt database. This step was very time consuming, especially with large databases. Version 7.0 makes a significant improvement over this process. Due to the new storage methoc, you do not need to perform this step. The necessary files will be recreated as the restore is occuring.

Restoring a full database

Restoring a full database is your starting point for a recovery. Until you have restored the full database backup, you can not restore any differential or transaction log backups.

The full command for restoring a full database is as follows:

RESTORE DATABASE {database_name | @database_name_var}[FROM <backup_device> [,...n]][WITH [DBO_ONLY][[,] FILE = file_number][[,] MEDIANAME = {media_name | @media_name_variable}] [[,] MOVE 'logical_file_name' TO 'operating_system_file_name'] [,...n][[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}] [[,] {NOUNLOAD | UNLOAD}][[,] REPLACE][[,] RESTART][[,] STATS [= percentage]]]

Option Description
dbo_only Places the database in dbo use only at the beginning of the restore
file Specifies the number of the file within the backup set to restore
medianame Specifies the media name
move This option is used primarily for restoring the database to another server. By default, the database files are recreated in the same location as on the original system. The move option give you the ability to specify an alternate location or name for the database you are restoring.
norecovery/recovery/standby Specifies whether recovery should be performed or if the database should be placed in a standby mode. These options are discussed in deatil after the table.
nounload/unload Specifies whether or not to unload the tape from the tape device
replace Replace overrides the safety checks performed during a restore. This will allow you to overwrite a file, filegroup, or database even if it already exists on the server.
restart If restoring from tape, the restart operation allows you to pick up a restore where it left off. This can be useful is a tape drive happened to go offline during the restore. It is not available if restoring from a disk device.
stats Specifies the percentage interval with which to report the progress

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.