0

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?

nate
  • 417
  • 5
  • 8
  • 17

3 Answers3

3

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
Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
0

you can restore your database backup by executing below command:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH REPLACE
GO

This will replace the existing database to the newer.

Jason Clark
  • 103
  • 5
  • Restoring from Query might bring error related to mdf file it would be easier to use SSMS to restore .bak https://qawithexperts.com/article/sql/restore-import-database-from-bak-file-in-sql-server-with-wit/123 – Vikas Lalwani Oct 22 '20 at 12:18
0

I'd start with verifying the backup file using the following statements:

restore headeronly from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\Restore\DBASE.BAK'

...returns a result set containing all the backup header information for all backup sets on a particular backup device.

restore verifyonly from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBASE\MSSQL\Restore\DBASE.BAK'

...Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical.

If the backup file is ok then you can continue with shutting down the database and ensuring nobody else accesses the database:

USE MASTER
GO
ALTER DATABASE DBASE SET OFFLINE WITH ROLLBACK IMMEDIATE;
go
ALTER DATABASE DBASE SET ONLINE
go
ALTER DATABASE DBASE SET RESTRICTED_USER   
GO

(make sure your SQL Server Management Studio doesn't have the database DBASE selected in the pulldown menu in the query window.)

After which you can commence with restoring the database using.

USE MASTER
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, 
RECOVERY,
STATS = 10
GO

If you aren't restoring the backup to a new location, then you can ommit the MOVE commands, as they are only required when restoring to a new location.

This should bring your database back online.

BTW, having multiple Transaction Log files doesn't benefit much unless you run out of space on your disk during an incident. Transaction log files are written serially and never in parallel. The only reason to temporarily having multiple transaction log files:

Alternatives for responding to a full transaction log include

  • Adding a log file on a different disk.

...as can be found here: Troubleshoot a Full Transaction Log (SQL Server Error 9002)

Good luck with your restore.

John K. N.
  • 2,055
  • 1
  • 17
  • 28