Sql-server – Restore SQL Database From BAK File using a SQL Query

database-restorequerysqlsql serversql-server-2008

I have my database backing up to the hard disk in the format of BAK file. I am trying to restore that backup, but I unable to do so.

Here is my query:

ALTER DATABASE DBASE
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE DBASE
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\Restore\DBASE.BAK'
WITH
MOVE 'DBASE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE.MDF',
MOVE 'DBASE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE_1.LDF',
MOVE 'DBASE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE_2.LDF'

ALTER DATABASE DBASE SET MULTI_USER
GO

When I run the query I get the following error:

Executed as user: NT AUTHORITY\SYSTEM. RESTORE cannot process database 'DBASE' because it is in use by this session. It is recommended that the master database be used when performing this operation. [SQLSTATE 42000] (Error 3102) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I don't want to restore the master database. Can someone please tell me how I can restore my database with a BAK file using a SQL query?

Best Answer

It's not asking you to restore master. It's asking you to not be in DBASE while you're trying to restore DBASE.

Try:

USE MASTER
GO

ALTER DATABASE DBASE
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE    
GO

RESTORE DATABASE DBASE
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\Restore\DBASE.BAK'
WITH
MOVE 'DBASE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE.MDF',
MOVE 'DBASE_LOG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\DATA\DBASE_1.LDF', REPLACE    
GO

ALTER DATABASE DBASE SET MULTI_USER
GO
Related Topic