IMG-LOGO

How to restore database using SQL query in TSQL?

andy - 26 Feb, 2021 2653 Views 0 Comment

If you have a database backup .bak file extension and want to restore it using SQL query. You can use the built-in 'RESTORE DATABASE' function. Remember in order to be able to restore a database successfully. You need to be a database owner which has the role of db_owner. You also need to ensure that there is no open session use the database. Otherwise, if the database is in use. You will receive the following error message.

To overcome this issue, you need to alter the database by accessing the master table first and set the single user with the rollback immediate method.

Here is the TSQL query to restore a database in SQL Server.


USE master; 
ALTER DATABASE simpleblog SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [mydatabasename]
FROM DISK = 'E:/mydatabasename.bak'
WITH REPLACE

The 'WITH REPLACE' keyword indicates that you want to want to overwrite the existing database that you want to restore.

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...