To recover a SQL Server database from a failure, a database administrator has to restore a set of SQL Server backups in a logically correct and meaningful restore sequence. SQL Server restore and recovery supports restoring data from backups of a whole database, a data file, or a data page, as follows:
- The database (a complete database restore)The whole database is restored and recovered, and the database is offline for the duration of the restore and recovery operations.
- The data file (a file restore)A data file or a set of files is restored and recovered. During a file restore, the filegroups that contain the files are automatically offline for the duration of the restore. Any attempt to access an offline filegroup causes an error.
- The data page (a page restore)Under the full recovery model or bulk-logged recovery model, you can restore individual databases. Page restores can be performed on any database, regardless of the number of filegroups.
Restoring a full database
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorks2012Backups;
Restoring full and differential database backups
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 9
RECOVERY;
Restoring a database and move files
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups
WITH NORECOVERY,
MOVE 'AdventureWorks2012_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'AdventureWorks2012_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
RESTORE LOG AdventureWorks2012
FROM AdventureWorksBackups
WITH RECOVERY;
Copying a database using BACKUP and RESTORE
BACKUP DATABASE AdventureWorks2012
TO AdventureWorksBackups ;
RESTORE FILELISTONLY
FROM AdventureWorksBackups ;
RESTORE DATABASE TestDB
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf',
MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf';
GO
Restoring to a point-in-time using STOPAT
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups
WITH FILE=3, NORECOVERY;
RESTORE LOG AdventureWorks2012
FROM AdventureWorksBackups
WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE LOG AdventureWorks2012
FROM AdventureWorksBackups
WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
Restoring the transaction log to a mark
USE AdventureWorks2012
GO
BEGIN TRANSACTION ListPriceUpdate
WITH MARK 'UPDATE Product list prices';
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
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 AdventureWorks2012
FROM AdventureWorksBackups
WITH FILE = 3, NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
FROM AdventureWorksBackups
WITH FILE = 4,
RECOVERY,
STOPATMARK = ListPriceUpdate;
Restoring using TAPE syntax
RESTORE DATABASE AdventureWorks2012
FROM TAPE = '\\.\tape0';
Restoring using FILE and FILEGROUP syntax
RESTORE DATABASE MyDatabase
FILE = 'MyDatabase_data_1',
FILE = 'MyDatabase_data_2',
FILEGROUP = 'new_customers'
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
Restoring from the Microsoft Azure Blob storage service
RESTORE DATABASE Sales
FROM URL = 'https://mystorageaccount.blob.core.windows.net/mysecondcontainer/Sales.bak'
WITH MOVE 'Sales_Data1' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_Data1.mdf',
MOVE 'Sales_log' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_log.ldf',
STATS = 10;
RESTORE DATABASE Sales
FROM URL = 'https://mystorageaccount.blob.core.windows.net/mysecondcontainer/Sales.bak'
WITH MOVE 'Sales_Data1' to 'H:\DATA\Sales_Data1.mdf',
MOVE 'Sales_log' to 'O:\LOG\Sales_log.ldf',
STATS = 10;
RESTORE DATABASE Sales
FROM DISK = 'E:\BAK\Sales.bak'
WITH MOVE 'Sales_Data1' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_Data1.mdf',
MOVE 'Sales_log' to 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer/Sales_log.ldf',
STATS = 10;
This command enables you to perform the following restore scenarios:
- Restore an entire database from a full database backup (a complete restore).
- Restore part of a database (a partial restore).
- Restore specific files or filegroups to a database (a file restore).
- Restore specific pages to a database (a page restore).
- Restore a transaction log onto a database (a transaction log restore).
- Revert a database to the point in time captured by a database snapshot.
--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <FILESTREAM_WITH_option>
| , <service_broker_WITH options>
| , \<point_in_time_WITH_options-RESTORE_DATABASE>
} [ ,...n ]
]
[;]
--To perform the first step of the initial restore sequence of a piecemeal restore:
RESTORE DATABASE { database_name | @database_name_var }
<files_or_filegroups> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
PARTIAL, NORECOVERY
[ , <general_WITH_options> [ ,...n ]
| , \<point_in_time_WITH_options-RESTORE_DATABASE>
] [ ,...n ]
[;]
--To Restore Specific Files or Filegroups:
RESTORE DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
{
[ RECOVERY | NORECOVERY ]
[ , <general_WITH_options> [ ,...n ] ]
} [ ,...n ]
[;]
--To Restore Specific Pages:
RESTORE DATABASE { database_name | @database_name_var }
PAGE = 'file:page [ ,...n ]'
[ , <file_or_filegroups> ] [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
NORECOVERY
[ , <general_WITH_options> [ ,...n ] ]
[;]
--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var }
[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , \<point_in_time_WITH_options-RESTORE_LOG>
} [ ,...n ]
]
[;]
--To Revert a Database to a Database Snapshot:
RESTORE DATABASE { database_name | @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name
<backup_device>::=
{
{ logical_backup_device_name |
@logical_backup_device_name_var }
| { DISK
| TAPE
| URL
} = { 'physical_backup_device_name' |
@physical_backup_device_name_var }
}
Note: URL is the format used to specify the location and the file name for the Microsoft Azure Blob. Although Microsoft Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seamless restore experience for all the three devices.
<files_or_filegroups>::=
{
FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
| READ_WRITE_FILEGROUPS
}
<general_WITH_options> [ ,...n ]::=
--Restore Operation Options
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
[ ,...n ]
| REPLACE
| RESTART
| RESTRICTED_USER | CREDENTIAL
--Backup Set Options
| FILE = { backup_set_file_number | @backup_set_file_number }
| PASSWORD = { password | @password_variable }
--Media Set Options
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
| BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
--Error Management Options
| { CHECKSUM | NO_CHECKSUM }
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
--Monitoring Options
| STATS [ = percentage ]
--Tape Options.
| { REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }
<replication_WITH_option>::=
| KEEP_REPLICATION
<change_data_capture_WITH_option>::=
| KEEP_CDC
<FILESTREAM_WITH_option>::=
| FILESTREAM ( DIRECTORY_NAME = directory_name )
<service_broker_WITH_options>::=
| ENABLE_BROKER
| ERROR_BROKER_CONVERSATIONS
| NEW_BROKER
\<point_in_time_WITH_options-RESTORE_DATABASE>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = 'lsn:lsn_number'
[ AFTER 'datetime']
| STOPBEFOREMARK = 'lsn:lsn_number'
[ AFTER 'datetime']
}
\<point_in_time_WITH_options-RESTORE_LOG>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
}