SQL Database Restore – Process in use | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Database Restore – Process in use

I’m am backing up a SQL 2000 database and copying it over to a SQL 2005 box.
If I restore it manually it works fine. If I try to create a job, I get errors. The step is written using: RESTORE DATABASE MyDB
FROM DISK = ‘E:SQLBackupsMyDB.bak’
WITH MOVE ‘MyDB_Data’ TO ‘E:program FilesMicrosoft SQL Servermssql.1mssqlDataMyDB.mdf’,
MOVE ‘MyDB_Log’ TO ‘E:program FilesMicrosoft SQL Servermssql.1mssqlDataMyDB.ldf’
I was getting a syntax error so I removed – RECOVERY, REPLACE – that were after the WITH statement. Now I get this error:
Executed as user: DOMAINUser. RESTORE cannot process ‘MyDB’ because it is in use by this session
May be you are running your restore in the context of the same mydb database…
try
USE MASTER
GO RESTORE DATABASE MyDB
FROM DISK = ‘E:SQLBackupsMyDB.bak’
WITH MOVE ‘MyDB_Data’ TO ‘E:program FilesMicrosoft SQL Servermssql.1mssqlDataMyDB.mdf’,
MOVE ‘MyDB_Log’ TO ‘E:program FilesMicrosoft SQL Servermssql.1mssqlDataMyDB.ldf’ MohammedU.
Moderator
SQL-Server-Performance.com
– If you’ve generated you restore statement don’t use char(13), but use char(10) if you want to start a new line. [B)] – the second time you restore, you must specify "replace" because you’re restoring to an existing database.
Thank you both for the help! I incorporated all the answers. – USE MASTER solved the ‘session in use’ error
– ‘REPLACE’ was needed.
– I had written the script in Notepad (or something similar) to get more viewing room, well the single quote ( ‘ )
was being read incorrectly, so I replaced them in SQL Job Step window and bingo! Thanks again!
I don’t think you get more viewing room in notepad than SSMS…close object explorer window to get more viewing room… For tsql always use SSMS or QA so that you can avoid this type of errors… MohammedU.
Moderator
SQL-Server-Performance.com
]]>