Restore database via automated order

FoxRunner

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

Simon Hayes

"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

P.D.N. Tame

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

FoxRunner

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

FoxRunner

"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

Dan Guzman

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