Restore database with T-SQL Language

Source: Internet
Author: User
Tags filegroup
Restore database with T-SQL Language

The T-SQL language provides a Restore database statement to restore a database backup that restores full backup, differential backup, file, and file group backup. If you want to restore the transaction log backup, you can also use the restore log statement. Although the Restore database statement can restore full backup, differential backup, file and file group backup, there is a little difference in the syntax of restoring full backup, differential backup, and file group backup, the following describes restoration methods for several types of backups.

18.6.1 restore full backup

The syntax for restoring a full backup is as follows:

Restore database {database_name | @ database_name_var} -- Database Name

[From <backup_device> [,... n] -- backup device

[

[{Checksum | no_checksum}] -- checking and

[[,] {Continue_after_error | stop_on_error}] -- whether to continue the restoration failure

[[,] Enable_broker] -- start Service Broker

[[,] Error_broker_conversations] -- pair all sessions

[[,] File = {backup_set_file_number | @ backup_set_file_number}] -- file used for restoration

[[,] Keep_replication] -- set replication to be used with log Shipper

[[,] Medianame = {media_name | @ media_name_variable}] -- media name

[[,] Mediapassword = {mediapassword | -- media Password

@ Mediapassword_variable}]

[[,] Move 'logical _ file_name_in_backup 'to 'operating _ system_file_name'] -- restore data

[,... N]

[[,] New_broker] -- create a new service_broker_guid Value

[[,] Password = {password | @ password_variable}] -- backup set password

[[,] {Recovery | norecovery | standby = -- recovery mode

{Standby_file_name | @ standby_file_name_var}

}]

[[,] Replace] -- overwrite existing database

[[,] Restart] -- restart the interrupted restore operation

[[,] Restricted_user] -- restrict access to the restored Database

[[,] {Rewind | norewind}] -- whether to release or rewound the tape

[[,] {Unload | nounload}] -- whether to repeat and detach a tape

[[,] Stats [= percentage] -- restores to its status at the specified date and time.

[[,] {Stopat = {date_time | @ date_time_var} -- restore to the specified date and time

| Stopatmark = {'Mark _ name' | 'lsn: lsn_number '} -- restore to the serial number of the marked transaction or log

[After datetime]

| Stopbeforemark = {'Mark _ name' | 'lsn: lsn_number '}

[After datetime]

}]

]

[;]

<Backup_device >::=

{

{Logical_backup_device_name |

@ Logical_backup_device_name_var}

| {Disk | tape }={ 'physical _ backup_device_name '|

@ Physical_backup_device_name_var}

}

Most of the parameters have been introduced during data backup. The following describes some parameters that have not been described:

L enable_broker: start the service broker so that messages can be sent immediately.

L error_broker_conversations: ends all sessions when an error occurs, and generates an error indicating that the database has been attached or restored. At this time, the Service broke will remain disabled until this operation is completed and then enabled.

L keep_replication: Set replication to be used with log shipper. After this parameter is set, the replication settings cannot be deleted when the database is restored on the slave server. This parameter cannot be used together with the norecovery parameter.

L move: restores the data file or log file specified by the logical name to the specified location, which is equivalent to the [Restore database file to] function shown in Figure 18.14.

L new_broker: this parameter is used to create a new service_broker_guid value in the databases database and the restored database, and end all session endpoints by clearing. The service broker is enabled, but the message is not sent to the remote session endpoint.

L recovery: Roll Back uncommitted transactions to make the database available. Unable to restore other transaction logs

L norecovery: do not perform any operations on the database, and do not roll back uncommitted transactions. Other Transaction logs can be restored.

L Standby: make the database in read-only mode. Undo uncommitted transactions, but save the Undo operation in the backup file so that the recovery effect can be reversed.

L standby_file_name | @ standby_file_name_var: Specifies a backup file or variable that allows you to cancel restoration.

L replace: overwrite all existing databases and related files, including existing databases or files with the same name.

L restart: specify that SQL Serve should restart the interrupted Restoration Operation. Restar restarts the restoration operation from the breakpoint.

L restricted_user: The restored database is only available to members of db_owner, dbcreator, and SysAdmin.

L stopat: restores the database to its status at the specified date and time.

L stopatmark: restores to the serial number of the marked transaction or log. The recovery includes a transaction with a named tag or lsn. This commit can be performed only when the transaction was initially committed when the transaction was actually generated.

L topbeforemark: restores to the serial number of the marked transaction or log. The recovery does not include transactions with named tags or lsn. When using with recovery, the transaction will be rolled back.

Example 12: Use a backup device named "northwind backup" to restore the northwind database.CodeAs follows:

Use master

Restore database northwind

From northwind backup

In this example, no backup set specified in the backup device is used to restore the database backup. By default, the first backup set in the backup device is used to restore the database. If you want to specify which backup set to use to restore the database, you must use the file parameter to specify the backup set.

Example 13: Use the sixth backup set of the backup device named "northwind backup" to restore the northwind database. The Code is as follows:

Use master

Restore database northwind

From northwind backup

With file = 6

Example 14: Use a backup file named "backup. Bak" to restore the northwind database. The Code is as follows:

Use master

Restore database northwind

From disk = 'd: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \

Backup \ backup. Bak'

18.6.2 restore differential backup

The syntax for restoring differential backup is the same as that for restoring a full backup. You must restore the full backup Before restoring the differential backup, therefore, restoring differential backup must be completed in two steps. Full backup and differential backup data are stored in the same backup file or device, or in different backup files or devices. If it is in the same backup file or backup device, you must use the file parameter to specify the backup set. Whether the backup set is in the same backup file (backup device), except for the last restore operation, the norecovery or standby parameter must be added to all other restore operations.

Example 15: Use the first backup set of the backup device named "northwind backup" to restore the full backup of the northwind database, and then use the third backup set to restore the differential backup. The Code is as follows:

Use master

Restore database northwind

From northwind backup

With file = 1, norecovery

Go

Restore database northwind

From northwind backup

With file = 3

Go

If the differential backup is restored separately or the norecovery parameter is not added to the full backup code in this example, the differential backup information shown in 18.16 cannot be restored.

Figure 18.16 unable to restore differential backup

18.6.3 restore transaction log backup

In SQL Server 2005, transaction log backup has been viewed as the same backup set as full backup and differential backup. Therefore, restoring transaction log backup can be the same as restoring differential backup, as long as you know that it is the first file set in the backup file or backup device.

Similar to the restoration differential backup, the restoration transaction log backup must first restore the complete backup before it. Except for the last restoration operation, the norecovery or standby parameter must be added to all other restoration operations.

Example 16: Use the first backup set of the backup device named "northwind backup" to restore the full backup of the northwind database, and then use the second backup set to restore the transaction log backup. The Code is as follows:

Use master

Restore database northwind

From northwind backup

With file = 1, norecovery

Go

Restore database northwind

From northwind backup

With file = 2

Go

The restore log statement can also be used to restore the transaction log backup. The code for example 16 can also be changed to the following code:

Use master

Restore database northwind

From northwind backup

With file = 1, norecovery

Go

Restore log northwind

From northwind backup

With file = 2

Go

18.6.4 restore file and File Group Backup

You can also use the Restore database statement to Restore files and file group backups, however, you must add the "file" or "filegroup" parameter between the database name and from to specify the file or file group to be restored. Generally, after restoring the file and file group backups, You need to restore other backups to obtain the latest database status.

Example 17: Use a backup device named "northwind backup" to restore the file and file group, and then use 15th backup sets to restore the transaction log backup. The Code is as follows:

Use master

Restore database northwind

Filegroup = 'primary'

From northwind backup

Go

Restore log northwind

From northwind backup

With file = 15

Go

18.6.5 restore the database to a certain time point

The "Time Point" is mentioned a little in the previous chapter. The following example shows how to restore the database to a certain time point.

Assume that a database has performed a full backup at eight o'clock A.M. and a transaction log backup at 09:15. Now we find that a data update at is incorrect, can we recover the data to the database status at 09:14, or can we only restore the status of transaction log backup?

Transaction logs are used to record each data modification record. Therefore, in theory, they can be restored to any previous state. The data at 09:15 is incorrect. Restore the data to 09:14.

Example 18: Use the 17th backup sets of the backup device named "northwind backup" to restore the full backup of the northwind database, and then use the 18th transaction log backup sets to restore the database to 09:14, the Code is as follows:

Use master

Restore database northwind

From northwind backup

With file = 17, norecovery

Go

Restore log northwind

From northwind backup

With file = 18, stopat = '2017-9-21 9:14:00'

Go

Tip: You can perform the same operation in SQL Server Management studio by setting the [target time point] in the dialog box shown in Figure 18.12.

18.6.6 restore the file to a new location

The Restore database statement can also be used to create a new database using the backup file.

Example 19: Create a new database named "northwind_test" using the 17th backup sets of the backup device named "northwind backup". The Code is as follows:

Use master

Restore database northwind_test

From northwind backup

With file = 17,

Move 'northwind _ data' to 'd: \ northwind_data.mdf ',

Move 'northwind _ log' to 'd: \ northwind_log.ldf ',

Move 'northwind Custom Data File 'to 'd: \ northwind custom data file. ndf ',

Move 'northwind custom log file 'to 'd: \ northwind custom log file. ldf'

Go

Note:

when you use Restore database to restore a database, the file parameter is used multiple times to specify a backup set. How can I view the ID of this backup set? In the "view backup device content" section, we have introduced how to view the backup set on the backup device. The "postition" column in the cell 18.9 shows the number specified by the file parameter.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.