Different Types Of Restores In Sql Server

Different Types Of Restores In Sql Server

1. Restore an entire database from a full database backup (a complete restore).
2. Restore part of a database (a partial restore).
3. Restore specific files or filegroups to a database (a file restore).
4. Restore specific pages to a database (a page restore).
5. Restore a transaction log onto a database (a transaction log restore).
6. Revert a database to the point in time captured by a database snapshot.

Restore a full database
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups

Restore full and differential database backups
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\SQLServerBackups\AdventureWorks.bak'
NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\SQLServerBackups\AdventureWorks.bak'
RECOVERY;

Restore a database using RESTART syntax
-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups 

--Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups WITH RESTART

Restore a database and move files
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO
'C:\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'AdventureWorks_Log'
TO 'C:\MSSQL\Data\NewAdvWorks_log.ldf'
RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH RECOVERY

Copying a database using BACKUP and RESTORE
BACKUP DATABASE AdventureWorks
TO AdventureWorksBackups;
GO

RESTORE FILELISTONLY
FROM AdventureWorksBackups;
RESTORE DATABASE XYZ
FROM AdventureWorksBackups
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\XYZ.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\XYZ.ldf';
GO

Restoring to a point-in-time using STOPAT
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE=3, 

NORECOVERY;
 

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=4, 

NORECOVERY, 

STOPAT = 'Apr 20, 2010 12:00 AM';
 

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE=5, 

NORECOVERY, 

STOPAT = 'Apr 20, 2010 12:00 AM';
 

RESTORE DATABASE 

AdventureWorks WITH RECOVERY;

Restore the transaction log to a mark
USE AdventureWorks;
GO
BEGIN TRANSACTION ListPriceUpdate
WITH MARK 'UPDATE Product list prices';
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'KK-%';
GO
COMMIT TRANSACTION ListPriceUpdate;
GO

-- Time passes. Regular database.
-- and log backups are taken.
-- An error occurs in the database.

USE master
GO
RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH FILE = 3, 

NORECOVERY;
GO

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'ListPriceUpdate';
GO

Restore database From Tape
RESTORE DATABASE AdventureWorks
FROM TAPE = '\\.\tape0'

Restore using FILE and FILEGROUP syntax
RESTORE DATABASE MyDatabase
FILE = 'MyDatabase_data_1',
FILE = 'MyDatabase_data_2',
FILEGROUP = 'FileGroup_6'
FROM MyDatabaseBackups
WITH FILE = 9,
NORECOVERY;
GO

Restore the log backups.
RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 10,
NORECOVERY;
GO

RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 11,
NORECOVERY;
GO
 

RESTORE LOG MyDatabase
FROM MyDatabaseBackups
WITH FILE = 12,
NORECOVERY;
GO

Recover the database:
RESTORE DATABASE MyDatabase
WITH RECOVERY;
GO

Reverting from a database snapshot
USE master
GO
RESTORE DATABASE AdventureWorks
FROM
DATABASE_SNAPSHOT = 'AdventureWorks_dbss2100';
GO