Wednesday, April 28, 2010

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
Single table restore from backup. table backup and restore. how to do table restore from backup file. how to take single table backup. how to restore single table from old backup file. What are the types of backups in sql server 2008 R2. What are the types of backups in sql server 2012. What are the types of backups in sql server 2005. how many types of backups in sql server 2008.   how many types of backups in sql server 2012. how many types of backups in sql server 2008 R2. how many types of backups in sql server 2005. how many types of restores in sql server 2008. how many types of restores in sql server 2012. how many types of restores in sql server 2005.

0 comments:

Post a Comment