Restore Job Script Error

Question

Post reply

Restore Job Script Error

sqldba-294117

Hall of Fame

Points: 3486
More actions
April 28, 2006 at 9:21 am

#117414

I am scheduling new RESTORE JOB that will run once in every month after my first job which is about backup.
First Job - Backup
Second Job - Restore
The problem is on my restore script. i am getting following error even though i am only one connected to the SQL SERVER 2000 & Query Analyzer is not opened. I am SA to this Server disk path & Database name is correct. i ahve two database.
1> CCS_SITE (Production)
2> CCS_SITE_Monthly (Reporting)
I am taking Backup of production database & Restoring to Reporting Database.
SCRIPT:-
restore database CCS_SITE_Monthly
from disk = 'E:\Temp\ccs_site.bak'
with replace;
ERROR MESSAGE:-
Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
please let me know if my script is wrong or suggestion. Any help will be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Well the error is Self Explanatory... When you've seen it more than oce

You need to remove all the user conections on the DB.There are polite ways of doing that and nasty ones too.

Here is the quick ( read nasty) one.

Alter Database 'Yourdatabase' SET SINGLE_USER With ROLLBAK IMMEDIATE

Then run your restore

Cheers,

* Noel

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 2

I tried that option & still same error.

Alter Database CCS_SITE_Monthly

SET SINGLE_USER

With ROLLBACK IMMEDIATE

restore database CCS_SITE_Monthly

from disk = 'E:\Temp\CCS_Site.bak'

with replace

noeld SSC Guru Points: 96590 More actions · Answer 3

Try a GO between the two statements and by the way don't for get to put it backl to multiuser after the restore

Alter Database CCS_SITE_Monthly

SET SINGLE_USER

With ROLLBACK IMMEDIATE

GO

restore database CCS_SITE_Monthly

from disk = 'E:\Temp\CCS_Site.bak'

with replace

GO

Alter Database CCS_SITE_Monthly

SET MULTI_USER

GO

* Noel

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 4

i did what you mentioned about to SET Multi_User & still getting same error. any help will be appreciated.

noeld SSC Guru Points: 96590 More actions · Answer 5

sqldba,

Lets step back for a minute. The important part of the script I posted was not the Multi user part , that is an after the fact feature.

I don't know who is connected to your db but these are the steps to verify that things are as expected.

1. execute sp_who and determine if there are other processes connected to the DB

2. Run

Alter Database CCS_SITE_Monthly SET SINGLE_USER With ROLLBAK IMMEDIATE

GO

3. execute sp_who and verify you are the ONLY connection. If not you need to kill the processes (other than yours) connected and that somehow escaped to the single user statement. In that case you can use the KILL command

4. Check the backup that you are trying to restore:

RESTORE FILELISTONLY FROM DISK = ''E:\Temp\ccs_site.bak'

Verify that the info is what you expected!! The Files are name the same as the ones you are trying to replace, the same path etc... This is important for replace to work.

5. RESTORE VERIFYONLY FROM DISK = 'E:\Temp\ccs_site.bak'

you should get a valid backup set confirmation.

6. If all that is successful you should be able to restore your backup using the same stament you just posted. By the way I am assuming this backup is a FULL backup

HTH

* Noel

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 6

I tried as above mentioned & nothing is working, stilli am getting same error even though i am only one connected with SA priviledge.