Different Restore options in SQL Server 2008

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

Leave a Reply

Your email address will not be published. Required fields are marked *