Getting error Exclusive access could not be obtained during restore

[appi]

Hi all
I just try to restore a database on a second server with a nightly batch. I started a job backup the DB into a local drive on destination server.
Now I set up the following job:

RESTORE DATABASE MWP52
FROM DISK = 'D:\backup_mwp\MWP51.bak' WITH
MOVE 'TMWPPRIM_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPPRIM_01.mdf',
MOVE 'TMWPSD_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPSD_01.ndf',
MOVE 'TMWPLD_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPLD_01.ndf',
MOVE 'TMWPSI_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPSI_01.ndf',
MOVE 'TMWPLI_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPLI_01.ndf',
MOVE 'TMWPUSR_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPUSR_01.ndf',
MOVE 'TMWPMVX_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPMVX_01.ndf',
MOVE 'TMWPTREE_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPTREE_01.ndf',
MOVE 'TMWPLNG_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPLNG_01.ndf',
MOVE 'TMWPCODA_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPCODA_01.ndf',
MOVE 'TMWPREPL_01' TO 'D:\Microsoft SQL Server\MSSQL\Data\TMWPREPL_01.ndf',
MOVE 'TMWPTRANL_01' TO 'D:\Microsoft SQL Server\MSSQL\Log\TMWPTRANL_01.ldf'

I got the following error:

Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101)

But there are no open connections to this Database.
I also tried to change the script with setting the database into SINGLE USER at the beginning of the restore

But the problem still persists.
What can I do to solve this ?
regards
appi

 

[SQLDenis]

ALTER DATABASE MWP52 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do your restore here


-- Make the DB multi user again
ALTER DATABASE MWP52 SET MULTI_USER

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[appi]

Hi Denis,

This was what I have done.
But the error came up ....
I could not understand this, because if I send the statements via SQL-Analyzer it works fine. Olnly as a scheduled job, or ran out from hand as a job it fails.

 

[SQLBill]

Do you have Enterprise Manager or Query Analyzer open when the job is being run? If so, that's two connections (Enterprise Manager AND the job agent or QA and the job agent). Do you have any other jobs running at the same time? Every job is a separate connection. Opening EM and running the job 'by hand' is two connections. Running a script in QA is only one connection.

-SQLBill

Posting advice: FAQ481-4875

 

[appi]

Hi Bill,

no, I have only EM open as the job ran. And this is the only job by now which is inside this DB.
Even if I start the Service and try again it work first time, but second time it stopped with this error.

I hoped that bringing the DB in single user solved this problem. I set this as a seperate job with Job Step 1 and the Restore with Step 2 - but it brought up the "Non-Exclusive" error.

appi

 

[SQLDenis]

what about putting all in 1 step

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[appi]

same result - this was the first solution

 

[SQLBill]

I think you misunderstood me...a job runs under the SQL Server Job Agent - that is one connection. If you have EITHER Enterprise Manager OR Query Analyzer open (doesn't matter which one) that is a second connection.

If you are stopping and starting the service and it works right away and then fails from that time on...there is another job running. Stopping the service stopped all jobs. Restarting the service and running your job, meant that at that time your job was the only one running. But then apparently another job starts and you can't run your job again.

Find out what job is running, besides your job.

-SQLBill

Posting advice: FAQ481-4875

 

[appi]

Hi Bill,

But I started the SET SINGLE_USER on the top of the restore.
If I looked into the Job-Agent there is only my job in there.
So I do not see any other job disturbing mine.

--appi

 

[appi]

Hi all,

I just found my main problem.
For the step I used the database I want to restore, but I need to use the master for the ALTER DATABASE .....
Didn't focussed on this, but now it was working

thanks for your helps so far
--appi

 

[mrdenny]

For the restore you should also be in the master database (or any database other than the one you are restoring).

When scheduling a restore it's best to kill all active connections which are in the database you are trying to restore.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com