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?

Improve this question
2
  • I guess this question should be actually posted in the DATABASE ADMINISTRATORS Stack Exchange.
    – John K. N.
    Feb 22, 2016 at 7:58
  • @hot2use, bis chances might be better there, but I believe the question also has a place at SF.
    – Daniel
    Feb 23, 2016 at 6:24

3 Answers 3

Reset to default
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
Improve this answer
5
  • I try that, now I getting the error: Only a single WITH MOVE clause should be specified for any logical file name.
    – nate
    Jan 21, 2015 at 22:07
  • Edited. I used two log files in my example that had the same logical file name because that was your example. Now there's just the one, and they're named different things.
    – Katherine Villyard
    Jan 21, 2015 at 22:13
  • I change the logical name to match my log file, and now I get the error: Logical file 'DBASE_1' is not part of database 'DBASE'
    – nate
    Jan 22, 2015 at 15:19
  • My log file is named DBASE_1.LDF
    – nate
    Jan 22, 2015 at 15:21
  • Hopefully this will help. msdn.microsoft.com/en-us/library/ms186865.aspx
    – Katherine Villyard
    Jan 22, 2015 at 20:56
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.

Improve this answer
1
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .