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 ;-))

Ausfhrt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
nicht mglich, 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 ?"FoxRunner" <claus.rauchschindel@.dds.de> wrote in message
news:b7c717fb.0312090728.1b374ba1@.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 ;-))
> Ausfhrt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
> nicht mglich, 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|||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.

claus.rauchschindel@.dds.de (FoxRunner) wrote in message news:<b7c717fb.0312090728.1b374ba1@.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 ;-))
> Ausfhrt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
> nicht mglich, da die Datenbank gerade verwendet wird. [SQLSTATE
> 42000] (Fehler 3101) RESTORE DATABASE wird fehlerbedingt beendet.
> [SQLSTATE 42000] (Fehler 3013). Fehler bei Schritt|||wwphx@.hotmail.com (P.D.N. Tame) wrote in message news:<bcc5d277.0312091343.5151cb59@.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
;-))|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3fd61c16_2@.news.bluewin.ch>...
> "FoxRunner" <claus.rauchschindel@.dds.de> wrote in message
> news:b7c717fb.0312090728.1b374ba1@.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 ;-))
> > Ausfhrt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
> > nicht mglich, 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|||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" <claus.rauchschindel@.dds.de> wrote in message
news:b7c717fb.0312090728.1b374ba1@.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 ;-))
> Ausfhrt als Benutzer: dbo. Exklusiver Zugriff auf die Datenbank ist
> nicht mglich, 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 ?

Restore DataBase to another server

Condolences are in order. (Black bunting and soft music please...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"bdickert" <bdickert@.discussions.microsoft.com> wrote in message
news:912D44AC-FEA5-4DED-B9EB-97A66319A718@.microsoft.com...
> Recently we lost our DBA.Restoring to a new server will serve you well.
You are correct that restoring to a server will update the master db.
However, you could restore to a different database name (if you have a DEV
copy of the database on the server and don't want to muck that up.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"bdickert" <bdickert@.discussions.microsoft.com> wrote in message
news:912D44AC-FEA5-4DED-B9EB-97A66319A718@.microsoft.com...
> Recently we lost our DBA.
> When trying to restore a database from one physical server to another, the
> master will get updated, correct?
> We are doing a restore audit on our databases / SQL 2000 SP3.
> The plan was to restore from tape to a server which worked BData_Bat.BAK
> was
> the restored database file, then use the restore wizard to reassemble the
> database and the files that make up the database, and place them on the
> server, but this would update the master on that target / developer
> server,
> so this isn't going to work and would probably cause issues.
> The new plan is to create a new server with SQL 2000 and restore to this
> new
> server and have the developers access this new server and test the
> application that way.
> Just thought I would post to see if this seems like the right approach and
> then I want to find out DBA duties, monitoring and performance and
> recommendations for a reliable environment -
> Thanks in advance
> BD|||Recently we lost our DBA.
When trying to restore a database from one physical server to another, the
master will get updated, correct?
We are doing a restore audit on our databases / SQL 2000 SP3.
The plan was to restore from tape to a server which worked BData_Bat.BAK was
the restored database file, then use the restore wizard to reassemble the
database and the files that make up the database, and place them on the
server, but this would update the master on that target / developer server,
so this isn't going to work and would probably cause issues.
The new plan is to create a new server with SQL 2000 and restore to this new
server and have the developers access this new server and test the
application that way.
Just thought I would post to see if this seems like the right approach and
then I want to find out DBA duties, monitoring and performance and
recommendations for a reliable environment -
Thanks in advance
BD|||Condolences are in order. (Black bunting and soft music please...)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"bdickert" <bdickert@.discussions.microsoft.com> wrote in message
news:912D44AC-FEA5-4DED-B9EB-97A66319A718@.microsoft.com...
> Recently we lost our DBA.|||Restoring to a new server will serve you well.
You are correct that restoring to a server will update the master db.
However, you could restore to a different database name (if you have a DEV
copy of the database on the server and don't want to muck that up.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"bdickert" <bdickert@.discussions.microsoft.com> wrote in message
news:912D44AC-FEA5-4DED-B9EB-97A66319A718@.microsoft.com...
> Recently we lost our DBA.
> When trying to restore a database from one physical server to another, the
> master will get updated, correct?
> We are doing a restore audit on our databases / SQL 2000 SP3.
> The plan was to restore from tape to a server which worked BData_Bat.BAK
> was
> the restored database file, then use the restore wizard to reassemble the
> database and the files that make up the database, and place them on the
> server, but this would update the master on that target / developer
> server,
> so this isn't going to work and would probably cause issues.
> The new plan is to create a new server with SQL 2000 and restore to this
> new
> server and have the developers access this new server and test the
> application that way.
> Just thought I would post to see if this seems like the right approach and
> then I want to find out DBA duties, monitoring and performance and
> recommendations for a reliable environment -
> Thanks in advance
> BD|||Well,
Here is what I did - loaded the personal edition locally and then restored
it and it seems to be ok - thanks for your help and remember "SQL Security
is very Picky"
BD
"Arnie Rowland" wrote:

> Restoring to a new server will serve you well.
> You are correct that restoring to a server will update the master db.
> However, you could restore to a different database name (if you have a DEV
> copy of the database on the server and don't want to muck that up.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "bdickert" <bdickert@.discussions.microsoft.com> wrote in message
> news:912D44AC-FEA5-4DED-B9EB-97A66319A718@.microsoft.com...
>
>|||Well,
Here is what I did - loaded the personal edition locally and then restored
it and it seems to be ok - thanks for your help and remember "SQL Security
is very Picky"
BD
"Arnie Rowland" wrote:

> Restoring to a new server will serve you well.
> You are correct that restoring to a server will update the master db.
> However, you could restore to a different database name (if you have a DEV
> copy of the database on the server and don't want to muck that up.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "bdickert" <bdickert@.discussions.microsoft.com> wrote in message
> news:912D44AC-FEA5-4DED-B9EB-97A66319A718@.microsoft.com...
>
>

Restore Dabatase MSSQL

Dear all I need your help in order to restore a database.

I did a backup of one SQL database hosted on my web service, and after I copy the file on my C:\Wutemp disk.

I'd like to import this database on my MSSQL installed on my local pc in order to have the same web site in local.

I try to restore it with enterprise manager (..using Restore) but I receive a lot of error message:
(I translate from Italian so..be patient..)

The filename 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mioDB_DATA.mdf' could be not correct.
Impossible to restore the file 'miodb_Data' in 'C:\Program Files\Micorosft SQL Server\MSSQL\Data\mioDB_DATA.mdf'. Use WITH MOVE in oreder to identify a valid location for the file
The filename 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mioDB_Log.LDF'could be not correct
Impossible to restore the file 'miodb_Log' in 'C:\Program Files\Micorosft SQL Server\MSSQL\Data\mioDB_Log.ldf'.

Any suggestions?you need to set the path it will restore to in the options section of the restore dialog. make sure it points to a valid local directory. if in doubt, look this up in Books Online - it's very clearly explained.