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 from a TRN file

I am suppose to create a database from a TRN file sent to me. I execute the
following in Query Analyzer:
RESTORE LOG omegaproposal from
disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
go
I get this error:
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
My question is: Can I create a database from a TRN file? If so, what is
wrong with my statement? The error says I did not specify WITH NORECOVERY
but I obviously did.
Thanks.No, you can not create a db from a transaction log. You can create a db,
restoring from a full db backup.
AMB
"Darin" wrote:
> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.|||You would have to have both the *BAK file (full database backup) and any
*.TRN files that were associated with that backup. A TRN file is only the
transactions since the last transaction log backup or full database backup.
"Darin" wrote:
> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.

Restore database fails only when source is read from another machine ( server)

When executing this from Query Analyzer on server1, where the target is
located,
restore database restoretest
from disk = 'd:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
stats=10,
REPLACE
... it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
but fails with " .. from disk = 'K:\backups\dottietest.bak' "
It fails when K: is used, i.e. the drive mapping to another win2k server's
D: drive, whereas it succeeds when the *.BAK file is on the local D; drive.
The sql server log shows:
"BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak' failed
to open. Operating system error = 5(Access is denied.)."
I can open all other files like XLS, etc from this server on K:.
The dottietest.BAK file was created by another SQL server, which is not on
server1.
Which permission am I missing?
Please help.
TIA
You can't use a mapped drive, so you'll have to use the UNC path instead -
that will do the trick....
Regards
Steen
PKuhne wrote:
> When executing this from Query Analyzer on server1, where the target
> is located,
> restore database restoretest
> from disk = 'd:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
> move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
> stats=10,
> REPLACE
> .. it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
> but fails with " .. from disk = 'K:\backups\dottietest.bak' "
> It fails when K: is used, i.e. the drive mapping to another win2k
> server's D: drive, whereas it succeeds when the *.BAK file is on the
> local D; drive. The sql server log shows:
> "BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak'
> failed to open. Operating system error = 5(Access is denied.)."
> I can open all other files like XLS, etc from this server on K:.
> The dottietest.BAK file was created by another SQL server, which is
> not on server1.
> Which permission am I missing?
> Please help.
> TIA

Restore database fails only when source is read from another machine ( server)

When executing this from Query Analyzer on server1, where the target is
located,
restore database restoretest
from disk = 'd:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
stats=10,
REPLACE
.. it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
but fails with " .. from disk = 'K:\backups\dottietest.bak' "
It fails when K: is used, i.e. the drive mapping to another win2k server's
D: drive, whereas it succeeds when the *.BAK file is on the local D; drive.
The sql server log shows:
"BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak' failed
to open. Operating system error = 5(Access is denied.)."
I can open all other files like XLS, etc from this server on K:.
The dottietest.BAK file was created by another SQL server, which is not on
server1.
Which permission am I missing?
Please help.
TIAYou can't use a mapped drive, so you'll have to use the UNC path instead -
that will do the trick....
Regards
Steen
PKuhne wrote:
> When executing this from Query Analyzer on server1, where the target
> is located,
> restore database restoretest
> from disk = 'd:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
> move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
> stats=10,
> REPLACE
> .. it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
> but fails with " .. from disk = 'K:\backups\dottietest.bak' "
> It fails when K: is used, i.e. the drive mapping to another win2k
> server's D: drive, whereas it succeeds when the *.BAK file is on the
> local D; drive. The sql server log shows:
> "BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak'
> failed to open. Operating system error = 5(Access is denied.)."
> I can open all other files like XLS, etc from this server on K:.
> The dottietest.BAK file was created by another SQL server, which is
> not on server1.
> Which permission am I missing?
> Please help.
> TIA

restore a database on MSDE

Hi,
I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager" or
"SQL Analyzer".
How can I restore a backup database?
This machine had MS Access XP, I can write program with VB6. Is there anyway
to execute a SQL script "RESTORE DATABASE ..."?
Thanks for help
Martin
Hi
Have a look at:
http://groups.google.ch/group/micros...346b3cfb2904bb
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Martin" <martinsm@.freenet.de> wrote in message
news:ON%23oAxn0FHA.164@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager"
> or
> "SQL Analyzer".
> How can I restore a backup database?
> This machine had MS Access XP, I can write program with VB6. Is there
> anyway
> to execute a SQL script "RESTORE DATABASE ..."?
> Thanks for help
> Martin
>
>
|||Look at http://www.asql.biz/DbaMgr.shtmn it's an graphical interface.

restore a bakup database on MSDE

Hi,
I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager" or
"SQL Analyzer".
How can I restore a backup database?
This machine had MS Access XP, I can write program with VB6. Is there anyway
to execute a SQL script "RESTORE DATABASE ..."?
Thanks for help
Martin
Hi,
You have 2 options:-
1. OSQL :- MSDE comes with command line utility OSQL. Using this you can
login to SQL Server and execute any TSQL command. For
Restore you can use RESTORE DATABASE command from OSQL.
2. Webadmin:- Web based free tool from microsoft. Download from below site:-
http://www.microsoft.com/downloads/d...displaylang=en
Thanks
Hari
SQL Server MVP
"Martin" <martinsm@.freenet.de> wrote in message
news:ulVz$wn0FHA.164@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager"
> or
> "SQL Analyzer".
> How can I restore a backup database?
> This machine had MS Access XP, I can write program with VB6. Is there
> anyway
> to execute a SQL script "RESTORE DATABASE ..."?
> Thanks for help
> Martin
>
>
|||Option 3:
make a little program, logon to master database and execute restore database
statement
RESTORE DATABASE <dbname> FROM DISK <location> WITH MOVE <file name>, TO
<file name>, RECOVERY, REPLACE
(MOVE and TO twice, for ldf and mdf (more if ndf's))
Check out BOL for exact statement
Lee-Z
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23bq4c3s0FHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You have 2 options:-
> 1. OSQL :- MSDE comes with command line utility OSQL. Using this you can
> login to SQL Server and execute any TSQL command. For
> Restore you can use RESTORE DATABASE command from OSQL.
> 2. Webadmin:- Web based free tool from microsoft. Download from below
> site:-
> http://www.microsoft.com/downloads/d...displaylang=en
> Thanks
> Hari
> SQL Server MVP
>
> "Martin" <martinsm@.freenet.de> wrote in message
> news:ulVz$wn0FHA.164@.TK2MSFTNGP10.phx.gbl...
>
|||Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:#bq4c3s0FHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You have 2 options:-
> 1. OSQL :- MSDE comes with command line utility OSQL. Using this you can
> login to SQL Server and execute any TSQL command. For
> Restore you can use RESTORE DATABASE command from OSQL.
> 2. Webadmin:- Web based free tool from microsoft. Download from below
site:-
>
http://www.microsoft.com/downloads/d...798-C57A-419E-
ACBC-2A332CB7F959&displaylang=en[vbcol=seagreen]
> Thanks
> Hari
> SQL Server MVP
>
> "Martin" <martinsm@.freenet.de> wrote in message
> news:ulVz$wn0FHA.164@.TK2MSFTNGP10.phx.gbl...
Manager"
>

restore a backup db on MSDE

Hi,
I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager" or
"SQL Analyzer".
How can I restore a backup database?
This machine had MS Access XP, I can write program with VB6. Is there anyway
to execute a SQL script "RESTORE DATABASE ..."?
Thanks for help
Martin
Yes, you can use OSQL.EXE and from there execute your RESTORE command. Both OSQL and RESTORE are
documented in Books Online, which you can find here:
http://www.microsoft.com/sql/techinf...0/default.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Martin" <martinsm@.freenet.de> wrote in message news:uE7SAxn0FHA.164@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager" or
> "SQL Analyzer".
> How can I restore a backup database?
> This machine had MS Access XP, I can write program with VB6. Is there anyway
> to execute a SQL script "RESTORE DATABASE ..."?
> Thanks for help
> Martin
>
>
|||Am Sun, 16 Oct 2005 15:36:34 +0200 schrieb Martin:

> Hi,
> I habe a MSDE 2000 on a server machine. I don't habe "Enterprise Manager" or
> "SQL Analyzer".
> How can I restore a backup database?
> This machine had MS Access XP, I can write program with VB6. Is there anyway
> to execute a SQL script "RESTORE DATABASE ..."?
> Thanks for help
> Martin
You can use DbaMgr2k from http://www.asql.biz/DbaMgr.shtm
A free and mighty Admin-Tool for SQL-Server and MSDE, can backup and
restore too.
bye,
Helmut