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
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.