SQL Server restore job fails
Backup & recovery, Database, DB2, Desktops, Management, Microsoft Windows, Oracle, Oracle administration, OS, Security, Servers, SQL, SQL Server
I've created a Restore Job under SQL Server Agent. Job owner is sa, Run as User dbo.
There are no users connected to the database or processes running.
The job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:PFAIDSrestorePRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:MSSQLDATADBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:MSSQLDATADBA_pfaids_test_Log.LDF'
ALTER DATABASE DBA_pfaids_test SET MULTI_USER
EXEC sp_revokedbaccess 'pfgldnusr'
EXEC sp_grantdbaccess 'pfgldnusr', 'pfgldnusr'
EXEC sp_addrolemember 'db_owner', 'pfgldnusr'
EXEC sp_addrolemember 'pfgldngrp', 'pfgldnusr'
EXEC sp_revokedbaccess 'PFNetPartner'
EXEC sp_grantdbaccess 'PFNetPartner', 'PFNetPartner'
EXEC sp_addrolemember 'db_owner', 'PFNetPartner'
EXEC sp_addrolemember 'pfgldngrp', 'PFNetPartner'
EXEC sp_revokedbaccess 'LGIOVINA'
EXEC sp_grantdbaccess 'TUFTSLGIOVINA', 'LGIOVINA'
EXEC sp_addrolemember 'db_owner', 'LGIOVINA'
EXEC sp_revokedbaccess 'CKOW01'
EXEC sp_grantdbaccess 'TUFTSCKOW01', 'CKOW01'
EXEC sp_addrolemember 'db_owner', 'CKOW01'
EXEC sp_revokedbaccess 'RDEMAS01'
EXEC sp_grantdbaccess 'TUFTSRDEMAS01', 'RDEMAS01'
EXEC sp_addrolemember 'db_owner', 'RDEMAS01'
Software/Hardware used:
Software/Hardware used:
ASKED:
March 29, 2006 3:46 PM
UPDATED:
March 31, 2006 12:55 PM
Answer Wiki:
Here is a discussion which lists some possibilities for the exclusive user error you are getting. Hope this helps.
http://www.red-gate.com/messageboard/viewtopic.php?t=1220&sid=1c650ab81043e1997e5ce76b7cdf4772
To see all answers submitted to the Answer Wiki: View Answer History.
If you put the database to single user mode before you start no one else can access the database while you are running the restore. You need to first makes sure there are no connections before you start the restore, if there are any users in the DB kick them out and try again.
HTH,
Joe
stephen09, thank you for the link to red-gate. It had information that resolved my problem. Here is the section that pertained to my issue: (I changed the db name in the dropdown and it worked!).
This could be caused by the first issue, but it could also be a configuration problem with the job. Every SQL Agent job runs in the context of a databse. If you examine the properies of the job step in Enterprise Manager for restoring the logs to the server, you will see a Database dropdown. If the database name specified there is the same one you are restoring to, you will get an error message when the job runs because SQL Agent is making a connection to it. To fix this, simply change the database name in the job step to master or some other database.