SQL Server Restore

--Full restore

RESTORE DATABASE Demodata     from DISK = N'D:\Backup\demoData.bak';  

RESTORE DATABASE {database_name | @database_name_var}
[From <backup_device> [,... N]]
[With
{
[RECOVERY--Specifies that the restore operation rolls back all uncommitted transactions and makes the database available, and if subsequent restore operations need to continue from differential and transaction log backups, the NORECOVERY option must be used; RECOVERY option is used in the last restore command of the restoration operation.

| NORECOVERY--Specifies that the restore operation does not roll back uncommitted transactions, and that subsequent restore operations need to continue from a differential or transaction log backup, using the NORECOVERY option before the last Restore command of the restoration process.

| STANDBY =
{standby_file_name | @standby_file_name_var}--recovery (default) indicates that a rollback should be performed after the current backup is complete
]
| , <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]
]
[;]

--Restore part of the database (partial 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]
[;]

--Restore a specific file or filegroup to a database (file restore)
RESTORE DATABASE {database_name | @database_name_var}
<file_or_filegroup> [,... N]
[From <backup_device> [,... N]]
With
{
[RECOVERY | NORECOVERY]
[, <general_WITH_options> [,... N]]
} [,... N]
[;]

--Restore a specific page to a database (page restore)
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]]
[;]

--Restore the transaction log to the database (transaction log restore)
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]
]
[;]

--Restore the database to a point in time when the database snapshot was captured
RESTORE DATABASE {database_name | @database_name_var}
From database_snapshot = Database_snapshot_name

<backup_device>::=--Backup device definition
{
{Logical_backup_device_name |
@logical_backup_device_name_var}
| {DISK | TAPE | URL} = {' Physical_backup_device_name ' |
@physical_backup_device_name_var}
}

<files_or_filegroups>::=--File or filegroup definition
{
FILE = {Logical_file_name_in_backup | @logical_file_name_in_backup_var}
| FILEGROUP = {Logical_filegroup_name | @logical_filegroup_name_var}
| Read_write_filegroups-a backup created by specifying read_write_filegroups is called a "partial backup." Under the simple recovery model, filegroup backups are only allowed on read-only filegroups. Restored data backup type: Database backup, partial backup, or file backup. For database or partial backups, the sequence of log backups must start at the end of a database backup or a partial backup. For a set of file backups, the sequence of log backups must be continued from the beginning of the entire set of file backups.

}

<general_WITH_options> [,... N]::=--General with option definition
--Restore Operation options
MOVE ' logical_file_name_in_backup ' to ' operating_system_file_name '
[,... N]
| Replace-in a SQL Server instance, if the data to be restored has the same name as the existing database, specifying the REPLACE option causes SQL server to delete the database that already exists with the same name. If you do not specify the REPLACE option, SQL Server does a security check and does not delete the existing database with the same name
| RESTART--Restart the restore operation at the time of the database restore operation interruption (interrupt), restart the restore operation from the point of interruption
| Restricted_user | Credential--for newly restored databases, restrict (restrict) user access, only allow access to members of the role db_owner, dbcreator or sysadmin

--Backup settings options
| FILE = {Backup_set_file_number | @backup_set_file_number}
| PASSWORD = {PASSWORD | @password_variable}

--Media settings options
| MEDIANAME = {Media_name | @media_name_variable}
| Mediapassword = {Mediapassword | @mediapassword_variable}
| BLOCKSIZE = {BLOCKSIZE | @blocksize_variable}

--Data transaction options
| BUFFERCOUNT = {BUFFERCOUNT | @buffercount_variable}
| maxTransferSize = {maxtransfersize | @maxtransfersize_variable}

--Error detection options
| {CHECKSUM | No_checksum}--Detection checksum
| {Stop_on_error | Continue_after_error}

--Detection 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 ']
}

SQL Server Restore