Restore SQL Server database with SQL code

We have options to restore a system database using SQL Server Management Studio, but we cannot always have it at hand, or especially when it is necessary to perform the procedure with many databases for analysis or expertise. of data.

Here we will see options for you to be able to restore the databases, without using the windows, just using the command line, to speed up the process.

The basic command to restore a database is as follows:

RESTORE DATABASE new_bank FROM DISK = 'X: \ XPTO \ BACKUP \ banco.bak' WITH REPLACE, RECOVERY

However, SQL Server works with logical files, and in this model, you can only restore the database itself that was backed up before, on the same base.

If you try to upload the database to another database (for example to keep 2 active), similar errors will occur below (addresses and lines have been replaced for example):

Msg 1834, Level 16, State 1, Line X

The file 'X: \ XPTO \ DATA \ banco.mdf' cannot be overwritten. It is being used by 'bank' database.

Msg 3156, Level 16, State 4, Line X

File 'banco' cannot be restored to 'X: \ XPTO \ DATA \ banco.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 1834, Level 16, State 1, Line X

The file 'X: \ XPTO \ DATA \ banco.ldf' cannot be overwritten. It is being used by 'bank' database.

Msg 3156, Level 16, State 4, Line X

File 'banco_log' cannot be restored to 'X: \ XPTO \ DATA \ banco.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line X

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line X

RESTORE DATABASE is terminating abnormally.

In order to resolve this issue, you must first examine your "X: \ XPTO \ BACKUP \ banco.bak" file and check your logical files (a bak can also have several other backups, in this example, we assume that it has only a single full backup ).

Use the command to check the logical files in the BAK file:

restore filelistonly from disk = 'X: \ XPTO \ BACKUP \ banco.bak'

It will then display the logical name of the files contained within this backup file.

Then use the logical names and change the path where the database system will save the MDF and LDF files.

As an example, follow the complete restore to perform for another database:

RESTORE DATABASE novo_banco FROM DISK = 'X: \ XPTO \ BACKUP \ banco.bak' WITH REPLACE, RECOVERY, MOVE 'banco' TO 'X: \ XPTO \ DATA \ novo_banco.mdf',

MOVE 'banco_log' TO 'X: \ XPTO \ DATA \ novo_banco.ldf';

If another error occurs when trying to use the database as:

Msg 927, Level 14, State 2, Line X

Database 'novo_banco' cannot be opened. It is in the middle of a restore.

Use the command:

alter database novo_bank set multi_user with rollback immediate;

Note:

The addresses X: \ XPTO \ DATA, refer to the path where your SQL Server saves the databases (mdf and ldf files). They usually have the files master.mdf and master.ldf in the same folder. Example: "C: \ Program Files \ Microsoft SQL Server \ MSSQL12.MSSQLSERVER \ MSSQL \ DATA".

The addresses X: \ XPTO \ BACKUP, refer to any path where your system has the backup file, the media "banco.bak".