467,188 Members | 1,406 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,188 developers. It's quick & easy.

Restore database via automated order

Hi folks,

I got a script which restores a database. It works fine
if it is running in my Query Analyzer.

It fails when I put this script in an automated schedule using the
SQL agent.

This is my script

RESTORE DATABASE [RestoreTest]
FROM DISK = N'E:\sqlbak\RestoreTest.BAK'
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY

and this is the error message from the scheduler (Sorry its in German)

Executing as User dbo. Exclusiv access to database not possible
because
it is in use (which is not).. Rest may be clear ;-))

Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
nicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE
42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.
[SQLSTATE 42000] (Fehler 3013). Fehler bei Schritt

Do you have any suggestion to me ?
Jul 20 '05 #1
  • viewed: 8802
Share:
5 Replies

"FoxRunner" <cl*****************@dds.de> wrote in message
news:b7**************************@posting.google.c om...
Hi folks,

I got a script which restores a database. It works fine
if it is running in my Query Analyzer.

It fails when I put this script in an automated schedule using the
SQL agent.

This is my script

RESTORE DATABASE [RestoreTest]
FROM DISK = N'E:\sqlbak\RestoreTest.BAK'
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY

and this is the error message from the scheduler (Sorry its in German)

Executing as User dbo. Exclusiv access to database not possible
because
it is in use (which is not).. Rest may be clear ;-))

Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
nicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE
42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.
[SQLSTATE 42000] (Fehler 3013). Fehler bei Schritt

Do you have any suggestion to me ?


Some process is accessing the database at the time you want to restore it.
This may be the job itself, if you set the database context for the restore
step to RestoreTest, instead of master.

Even if that is the issue, a better solution is to disconnect any open
connections. You can add a new first job step to do ALTER DATABASE
RestoreTest SET OFFLINE WITH ROLLBACK IMMEDIATE. Then, after restoring it,
use ALTER DATABASE RestoreTest SET ONLINE to make the database available
again. Make sure these steps use master as the database context. This
assumes, of course, that it is acceptable in your environment to disconnect
any client applications without warning.

Simon
Jul 20 '05 #2
I think you're looking at the most common cause of failure on a
restore: the database is not in DBO or single-user mode. I don't know
off-hand how you would force the DB into DBO mode in a job, but I'm
certain there is a way. The more I look at the error message the more
certain I am as to this is what is happening.

And you should be able to look up the error message numbers on TechNet
to get the English version.

cl*****************@dds.de (FoxRunner) wrote in message news:<b7**************************@posting.google. com>...
Hi folks,

I got a script which restores a database. It works fine
if it is running in my Query Analyzer.

It fails when I put this script in an automated schedule using the
SQL agent.

This is my script

RESTORE DATABASE [RestoreTest]
FROM DISK = N'E:\sqlbak\RestoreTest.BAK'
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY

and this is the error message from the scheduler (Sorry its in German)

Executing as User dbo. Exclusiv access to database not possible
because
it is in use (which is not).. Rest may be clear ;-))

Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
nicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE
42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.
[SQLSTATE 42000] (Fehler 3013). Fehler bei Schritt

Jul 20 '05 #3
ww***@hotmail.com (P.D.N. Tame) wrote in message news:<bc**************************@posting.google. com>...
I think you're looking at the most common cause of failure on a
restore: the database is not in DBO or single-user mode. I don't know
off-hand how you would force the DB into DBO mode in a job, but I'm
certain there is a way. The more I look at the error message the more
certain I am as to this is what is happening.

And you should be able to look up the error message numbers on TechNet
to get the English version.

Thanks a lot. I think I can proceed from here by myself
;-))
Jul 20 '05 #4
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<3f********@news.bluewin.ch>...
"FoxRunner" <cl*****************@dds.de> wrote in message
news:b7**************************@posting.google.c om...
Hi folks,

I got a script which restores a database. It works fine
if it is running in my Query Analyzer.

It fails when I put this script in an automated schedule using the
SQL agent.

This is my script

RESTORE DATABASE [RestoreTest]
FROM DISK = N'E:\sqlbak\RestoreTest.BAK'
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY

and this is the error message from the scheduler (Sorry its in German)

Executing as User dbo. Exclusiv access to database not possible
because
it is in use (which is not).. Rest may be clear ;-))

Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
nicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE
42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.
[SQLSTATE 42000] (Fehler 3013). Fehler bei Schritt

Do you have any suggestion to me ?


Some process is accessing the database at the time you want to restore it.
This may be the job itself, if you set the database context for the restore
step to RestoreTest, instead of master.

Even if that is the issue, a better solution is to disconnect any open
connections. You can add a new first job step to do ALTER DATABASE
RestoreTest SET OFFLINE WITH ROLLBACK IMMEDIATE. Then, after restoring it,
use ALTER DATABASE RestoreTest SET ONLINE to make the database available
again. Make sure these steps use master as the database context. This
assumes, of course, that it is acceptable in your environment to disconnect
any client applications without warning.

Simon

Simon,

Thats it. Your suggestion to change the context from Restore-Test to
Master was succesfull.

Embedding the ROLLBACK IMMEDIATE does not have any influence to the
result.
I let in because I think it is a better way of programming.

Thank Mate and best regards to Switzerland
Jul 20 '05 #5
To add to the other responses, check to ensure the database context for your
restore job is set to a database other than the one your are trying to
restore. It may be that the restore job itself is the culprit.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FoxRunner" <cl*****************@dds.de> wrote in message
news:b7**************************@posting.google.c om...
Hi folks,

I got a script which restores a database. It works fine
if it is running in my Query Analyzer.

It fails when I put this script in an automated schedule using the
SQL agent.

This is my script

RESTORE DATABASE [RestoreTest]
FROM DISK = N'E:\sqlbak\RestoreTest.BAK'
WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY

and this is the error message from the scheduler (Sorry its in German)

Executing as User dbo. Exclusiv access to database not possible
because
it is in use (which is not).. Rest may be clear ;-))

Ausführt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
nicht möglich, da die Datenbank gerade verwendet wird. [SQLSTATE
42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.
[SQLSTATE 42000] (Fehler 3013). Fehler bei Schritt

Do you have any suggestion to me ?

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Rajesh Kapur | last post: by
1 post views Thread by Rajesh Kapur | last post: by
reply views Thread by richlittle@gmail.com | last post: by
5 posts views Thread by chow.justy@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.