MS SQL backup data import command

Asked By 0 points N/A Posted on -
qa-featured

Hi All, 

What is the MS SQL backup data import command, that will restore the Database to a new location. Also provide few similar commands in MS SQL with syntax.

Thanks & Regards,

Neil Rabon

SHARE
Answered By 0 points N/A #181841

MS SQL backup data import command

qa-featured

The basic Transact-SQL syntax for restoring the database to a new location and a new name is:

RESTORE DATABASE new_database_name

FROM backup_device [ ,…n ]

[ WITH

{

[ RECOVERY | NORECOVERY ]

[ , ] [ FILE ={ backup_set_file_number | @backup_set_file_number } ]

[ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,…n ]

}

;

The following table describes arguments of this RESTORE statement in terms of restoring a database to a new location. For more information about these arguments, see RESTORE (Transact-SQL).

new_database_name

The new name for the database.Note

If you are restoring the database to a different server instance, you can use the original database name instead of a new name.

backup_device [ ,…n ]

Specifies a comma-separated list of from 1 to 64 backup devices from which the database backup is to be restored. You can specify a physical backup device, or you can specify a corresponding logical backup device, if defined. To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } = physical_backup_device_name

For more information, see Backup Devices.
{ RECOVERY | NORECOVERY }

If the database uses the full recovery model, you might need to apply transaction log backups after you restore the database. In this case, specify the NORECOVERY option.

Otherwise, use the RECOVERY option, which is the default.
FILE = { backup_set_file_number | @backup_set_file_number }

Identifies the backup set to be restored. For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. You can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.

When this option is not specified, the default is to use the first backup set on the backup device.

For more information, see "Specifying a Backup Set," in RESTORE Arguments (Transact-SQL).
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,…n ]

Specifies that the data or log file specified by logical_file_name_in_backup is to be restored to the location specified by operating_system_file_name. Specify a MOVE statement for every logical file you want to restore from the backup set to a new location.
Option
Description

logical_file_name_in_backup
Specifies the logical name of a data or log file in the backup set. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.
Note
To obtain a list of the logical files from the backup set, use RESTORE FILELISTONLY.

operating_system_file_name
Specifies a new location for the file specified by logical_file_name_in_backup. The file will be restored to this location.

Optionally, operating_system_file_name specifies a new file name for the restored file. This is necessary if you are creating a copy of an existing database on the same server instance.

n
Is a placeholder indicating that you can specify additional MOVE statements.

Hi This Resolve Your Problem.

Login/Register to Answer

Related Questions