SQL Server restore job fails

0 pts.
Tags:
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'
ASKED: March 29, 2006  3:46 PM
UPDATED: October 16, 2013  9:12 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jaylou
    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
    0 pointsBadges:
    report
  • TuftsDBA
    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.
    0 pointsBadges:
    report
  • TuftsDBA
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/sql-server-restore-job-fails/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following