Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use this query in SQL Server 2008 R2 to restore DB1 database:

RESTORE DATABASE [DB1] 
FROM DISK = N'D:\new.bak' 
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

The following error is displayed:

Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'DB1' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

share|improve this question

migrated from stackoverflow.com Dec 20 '12 at 23:32

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

up vote 4 down vote accepted

The message seems pretty clear: you cannot be using the DB1 database when trying to restore it - instead, use the master database!

So in SQL Server Management Studio, first close all windows that are open and using that database! Then run this:

USE master
GO

RESTORE DATABASE [DB1] 
FROM DISK = N'D:\new.bak' 
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
share|improve this answer
    
can not use 'Use master' in SP. error display. –  Ahmad Kazemi Dec 19 '12 at 6:00
    
@AhmadKazemi: in what database does your stored procedure exist? –  marc_s Dec 19 '12 at 6:01
    
i want use this query in SP. Can I do it? –  Ahmad Kazemi Dec 19 '12 at 6:03
1  
It depends on what database that stored procedure is created inside of. Obviously, you cannot have that stored procedure inside the database you're trying to restore .... –  marc_s Dec 19 '12 at 6:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.