Different Restore options in SQL Server 2008:
1. Restore can be done for an entire database from a full database backup (a complete restore).
2. Restore can be done for a part of a database (a partial restore).
3. Restore can be done for specific files or filegroups to a database (a file restore).
4. Restore can be done to specific pages to a database (a page restore).
5. Transaction log Restore can be done onto a database (a transaction log restore).
6. We also have a option to Revert a database to the point in time captured by a database snapshot.
Restoring a full databaseThe following example restores a full database backup from the AdventureWorks2008R2.bak file.
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2008R2.bak'
WITH NORECOVERY;
Restoring full and differential database backups
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2008R2.bak'
WITH NORECOVERY;
Having No Recovery option leaves the database not yet to be recovered to allow other backup to be applied on top of this like differential and Transaction log backups.
Restoring a database and move files
RESTORE FILELISTONLY: Can be used to find location of the data and log files of a backup set.
RESTORE FILELISTONLY
FROM AdventureWorks2008R2Backups ;
Move option enables the moving of the data and log files to different location
RESTORE DATABASE AdventureWorks2008R2
FROM AdventureWorks2008R2Backups
WITH NORECOVERY,
MOVE 'AdventureWorks2008R2_Data' TO
'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataNewAdvWorks2008R2.mdf',
MOVE 'AdventureWorks2008R2_Log'
TO 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataNewAdvWorks2008R2.ldf'
The above method can be used for copying a database using BACKUP and RESTORE
Restoring to a point-in-time using STOPAT
“STOPAT” is useful in point in time restores like say a database needs to be restored to its state as of 12:00 AM on January 15, 2012 and shows a restore operation that involves multiple log backups.
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2008R2.bak'
WITH NORECOVERY;
RESTORE LOG AdventureWorks2008R2
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2008R2.trn'
WITH NORECOVERY, STOPAT = 'Jan 15, 2012 12:00 AM';
RESTORE LOG AdventureWorks2008R2
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2008R2_2.trn'
WITH NORECOVERY, STOPAT = 'Jan 15, 2012 12:00 AM';
RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY;
Restoring using FILE and FILEGROUP syntax using Backup Device “BackupDeviceDemo”
RESTORE DATABASE MyDemo
FILE = 'MyDemo_data_1',
FILE = 'MyDemo_data_2',
FILEGROUP = 'new_customers'
FROM BackupDeviceDemo
WITH
FILE = 9,
NORECOVERY;
GO
-- Restore the log backups.
RESTORE LOG MyDemo
FROM BackupDeviceDemo
WITH FILE = 10,
NORECOVERY;
GO
RESTORE LOG MyDemo
FROM BackupDeviceDemo
WITH FILE = 11,
NORECOVERY;
GO
RESTORE LOG MyDemo
FROM BackupDeviceDemo
WITH FILE = 12,
NORECOVERY;
GO
--Recover the database:
RESTORE DATABASE MyDemo WITH RECOVERY;
GO
Reverting from a database snapshot
USE master
RESTORE DATABASE AdventureWorks2008R2 FROM DATABASE_SNAPSHOT = 'AdventureWorks2008R2_dbss1800';
GO