1.RESTORE FILELISTONLY
Returns a result set
containing a list of the
database and log files contained in the backup set.
--The following
example returns the information from a backup device named
AdventureWorksBackups. The example uses the FILE option to specify the second
backup set on the device.
RESTORE FILELISTONLY FROM
AdventureWorksBackups
WITH FILE=2;
GO
2.RESTORE HEADERONLY
Returns a result set
containing all the backup
header information for all backup sets on a particular backup device.
/*The following
example returns the information in the header for the disk file
C:\AdventureWorks-FullBackup.bak.
UNLOAD -
Specifies that the tape is automatically rewound and unloaded when the backup
is finished. UNLOAD is the default when a session begins.
NOUNLOAD -
Specifies that after the RESTORE operation the tape will remain loaded on the
tape drive.
*/
RESTORE HEADERONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'
WITH NOUNLOAD;
GO
3.RESTORE LABELONLY
The RESTORE
LABELONLY option
allows you to see the backup media information for
the backup device. So if a backup device, such as a backup file, has multiple backups you will only get
one record back that gives you information about the media set, such as the software that was used to create the backup, the date the
media was created, etc...
This information can only be
returned using T-SQL
there is not a
way to get this
information from SQL
Server Management Studio.
The RESTORE
LABELONLY option
can be simply issued as follows for a backup that exists on disk.
--Get labelonly
information from a backup file
RESTORE LABELONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO
4.RESTORE VERIFYONLY
Verifies the backup
but does not restore it, and checks to see that the backup set is complete and
the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to
verify the structure of the data contained in the backup volumes. In
Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do
additional checking on the data to increase the probability of detecting
errors. The goal is to be as close to an actual restore operation as practical.
For more information, see the Remarks.
If the backup is
valid, the SQL Server Database Engine returns a success message.
/*Check a backup
file on disk
The following
command will check the backup file and return a message of whether the file is
valid or not. If it is not valid, this means the file is not going to be
usable for a restore and a new backup should be taken. One thing to note is
that if there are multiple backups in a file, this only checks the first file.*/
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
GO
/*Check a backup
file on disk for a particular backup
--This command
will check the second backup in this backup file. To check the contents in a
backup you can use RESTORE HEADERONLY and use the Position column to specify
the FILE number.*/
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2
GO
5.RESTORE REWINDONLY
Rewinds and closes
specified tape devices that were left open by BACKUP or RESTORE statements
executed with the NOREWIND option. This command is supported only for tape
devices.
RESTORE REWINDONLY is an alternative to RESTORE LABELONLY FROM
TAPE = <name> WITH REWIND. You can get a list of opened tape drives from
the sys.dm_io_backup_tapes dynamic
management view.
--To close the
device and unload the tape, use this:
RESTORE REWINDONLY FROM '\\.\tape0' WITH UNLOAD
--To close the
device and not unload the tape, use:
RESTORE REWINDONLY FROM '\\.\tape0' WITH NOUNLOAD
