automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jj
jj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

automating restore

Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You ca
n
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or tes
t
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytim
e
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pa
ss
> various parameters to SP to tell where is source, destination, domain, etc
.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
Ramu
If you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>
|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf youâ're using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
nightâ's backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If thatâ's what you want.
John Vandervliet
"Ramu" wrote:
> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

Automating Bkp & restore from command line

Currently developing a small app (Access) I'm using the same app on 3 SQL
Databases:
1 - Production: MS SQL 2000 server
2 - Test: MS SQL 2000 server
3 - Test: MSDE
As I'm modifyng extensively db structure I have to manually move modified
dbs between the 3 servers daily. multiple times, and got bored to manually
select each time paths and options (overwrite, force restore, devices etc)
So I wrote a small script in QA and thought if possible to runnit from
commandline (OSQL would do the job?). But I still miss a statement to
specifiy to wich DB the script must connect before doing th rest:
Here's my code and I've got a trutsed connection, so y would run:
osql -q "Restore.sql" -E:
The script contains:
USE MASTER
GO
RESTORE DATABASE Test
FROM DISK = 'C:\test.BAK'
WITH REPLACE,
MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
What am I missing to connect to the prper server?
Thanks
You first say:

> But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
And then:

> What am I missing to connect to the proper server?
Which one is it? Database or server? To specify a server, you use the -S switch for OSQL. The db is a bit more
problematic as it is included in the RESTORE command, but my guess is that it is server you are after.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atlas" <atlaspeak@.my-deja.com> wrote in message news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||osql -S [servername] ?
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>
|||Hi,
OSQL -S Server_name -E -i c:\Restore.sql -oc:\restore.log
Note: -q option is to give the query in the OSQL itself. Use iption "i" to
input a file.
Thanks
Hari
MCDBA
"Atlas" <atlaspeak@.my-deja.com> wrote in message
news:CMJuc.17145$G%.462@.tornado.fastwebnet.it...
> Currently developing a small app (Access) I'm using the same app on 3 SQL
> Databases:
> 1 - Production: MS SQL 2000 server
> 2 - Test: MS SQL 2000 server
> 3 - Test: MSDE
> As I'm modifyng extensively db structure I have to manually move modified
> dbs between the 3 servers daily. multiple times, and got bored to manually
> select each time paths and options (overwrite, force restore, devices etc)
> So I wrote a small script in QA and thought if possible to runnit from
> commandline (OSQL would do the job?). But I still miss a statement to
> specifiy to wich DB the script must connect before doing th rest:
> Here's my code and I've got a trutsed connection, so y would run:
> osql -q "Restore.sql" -E:
> The script contains:
> USE MASTER
> GO
> RESTORE DATABASE Test
> FROM DISK = 'C:\test.BAK'
> WITH REPLACE,
> MOVE 'Test_Log' TO 'C:\DBMS\Microsoft SQL Server
2000\MSSQL\Data\test.LDF',
> MOVE 'Test_dat' TO 'C:\DBMS\Microsoft SQL Server 2000\MSSQL\Data\test.MDF'
>
> What am I missing to connect to the prper server?
> Thanks
>

Automating a Backup and Restore across Servers

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

MariaHi Maria,
try if putting the statement

SET XACT_ABORT OFF

in the TSQL-stream of the step solves your problem.

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
Tram
If you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
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/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegr oups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>
|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.
|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegr oups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
--
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
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/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>sql

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
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/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatically enable broker after restore or attach

Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.

Simplest approach would be to enabled it via a procedure that does have the appropiate permissions (using EXECUTE AS and code signing).

Other possiblity would be to create a server event notification and use an activate procedure in msdb to enable the database:

Code Snippet

use msdb;

go

create queue [dbevents]

create service [dbevents] on queue [dbevents] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification [dbbackuprestore]

on server for AUDIT_BACKUP_RESTORE_EVENT

to service 'dbevents', 'current database';

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
KlausAre the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name ='Your_job_name_goes_here', @.server_name ='The_Name_Of_The_Standby_Server_Goes_Here'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
--
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>sql

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
Klaus
Are the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name =
'Your_job_name_goes_here', @.server_name =
'The_Name_Of_The_Standby_Server_Goes_Here'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
KlausAre the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name =
'Your_job_name_goes_here', @.server_name =
'The_Name_Of_The_Standby_Server_Goes_Her
e'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>

Automatic Restore

This should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d +
'.bak'''
SELECT @.sql
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
I have a database .BAK file on disk that I want to resotre everynight.
RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
If I want to automate this everyday, how can I create yyyymmdd
variable?
Thanks,
hj|||This should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d +
'.bak'''
SELECT @.sql
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>|||Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work
Thanks in advance.
Tibor Karaszi wrote:[vbcol=seagreen]
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d
+ '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...|||This solution looks very interesting but I am getting bak'ed up db
files from a third server and I do not have access to msdb on that
server...
Tracy McKibben wrote:
> Hitesh Joshi wrote:
>
> Instead of assuming a given filename/format, why not just check to see
> what the most recent backup was, and restore that? The following should
> work for you, add whatever switches you need to the restore command:
> DECLARE @.DBName VARCHAR(50)
> DECLARE @.LastBackupFileName VARCHAR(255)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.DBName = 'YourDBName'
> SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
> FROM msdb.dbo.backupset backupset
> INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
> ON backupset.media_set_id = backupmediafamily.media_set_id
> WHERE backupset.type = 'D'
> AND backupset.database_name = @.DBName
> ORDER BY backupset.backup_start_date DESC
> SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
> DISK=''' + @.LastBackupFileName + ''''
> EXEC (@.Command)|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work
Thanks in advance.
Tibor Karaszi wrote:[vbcol=seagreen]
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d
+ '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily

Automatic Restore

Hi,
I have a database .BAK file on disk that I want to resotre everynight.
RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
If I want to automate this everyday, how can I create yyyymmdd
variable?
Thanks,
hjThis should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
SELECT @.sql
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>|||Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work :(
Thanks in advance.
Tibor Karaszi wrote:
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> >
> > Hi,
> >
> > I have a database .BAK file on disk that I want to resotre everynight.
> >
> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> > If I want to automate this everyday, how can I create yyyymmdd
> > variable?
> >
> > Thanks,
> > hj
> >|||This solution looks very interesting but I am getting bak'ed up db
files from a third server and I do not have access to msdb on that
server...
Tracy McKibben wrote:
> Hitesh Joshi wrote:
> > Hi,
> >
> > I have a database .BAK file on disk that I want to resotre everynight.
> >
> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> > If I want to automate this everyday, how can I create yyyymmdd
> > variable?
> >
> > Thanks,
> > hj
> >
>
> Instead of assuming a given filename/format, why not just check to see
> what the most recent backup was, and restore that? The following should
> work for you, add whatever switches you need to the restore command:
> DECLARE @.DBName VARCHAR(50)
> DECLARE @.LastBackupFileName VARCHAR(255)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.DBName = 'YourDBName'
> SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
> FROM msdb.dbo.backupset backupset
> INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
> ON backupset.media_set_id = backupmediafamily.media_set_id
> WHERE backupset.type = 'D'
> AND backupset.database_name = @.DBName
> ORDER BY backupset.backup_start_date DESC
> SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
> DISK=''' + @.LastBackupFileName + ''''
> EXEC (@.Command)|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:upB3v4VmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hitesh Joshi wrote:
> > This solution looks very interesting but I am getting bak'ed up db
> > files from a third server and I do not have access to msdb on that
> > server...
> >
> Create a linked server on your restore server that points to the "third"
> server you mentioned. Then query the backupset and backupmediafamily
> tables using their fully-qualified names:
> SERVERNAME.msdb.dbo.backupset
> SERVERNAME.msdb.dbo.backupmediafamily
>
BTW, this is what we used to do.
Though, if possible what works better if the source server is doing
transaction log backups is to do a full backup/restore and then restore only
the logs every N hours. This is quicker and can reduce your disaster
recovery time.
(google for log shipping for more info. It's possible to roll your own.)|||You can't do wildcards in the restore command. I suggest you go the path suggested by Tracy. Also
consider Log Shipping as suggested by Greg.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151350714.948572.273570@.r2g2000cwb.googlegroups.com...
> Hi,
> Thank you for the suggestion... it works awesome with file name like
> db_name_yyyymmdd.bak but my files have name something like
> db_name_yyyymmddhhmm.bak.
> and last hhmm is not always same. is there a way I can use wildchars?
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
> @.d + '*.bak'''
> I tried this but did not work :(
> Thanks in advance.
> Tibor Karaszi wrote:
>> This should get you started:
>> DECLARE @.sql nvarchar(300), @.d char(8)
>> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
>> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d + '.bak'''
>> SELECT @.sql
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
>> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
>> >
>> > Hi,
>> >
>> > I have a database .BAK file on disk that I want to resotre everynight.
>> >
>> > RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
>> > If I want to automate this everyday, how can I create yyyymmdd
>> > variable?
>> >
>> > Thanks,
>> > hj
>> >
>

automatic backup / restore to another db

we have a "snapshot" database used for development testing we it constantly
(wly only) synced with the development database (on same server) I wanted
to set up a schedule that will backup the database called development every
sunday morning, then restore that onto the "devsnapshot" database right
after that sucessfully completed... is there any easy way to do this in sql
server 2005? if so how? i dont want mirrored data (aka realtime change
tracking) but i want to have the DML and data all copied so its a 1:1 copy..
thanksIn your job that performs the backups why not just add one more job step.
It woudl contain something like this:
IF (SELECT DATENAME ( wday ,getdate() )) = 'Sunday'
BEGIN
RESTORE DATABASE...
--send email
--...anything else?
END
Keith Kratochvil
"Smokey Grindle" <nospamhere@.dontspam.net> wrote in message
news:ukwlBCniGHA.2220@.TK2MSFTNGP05.phx.gbl...
> we have a "snapshot" database used for development testing we it
> constantly (wly only) synced with the development database (on same
> server) I wanted to set up a schedule that will backup the database called
> development every sunday morning, then restore that onto the
> "devsnapshot" database right after that sucessfully completed... is there
> any easy way to do this in sql server 2005? if so how? i dont want
> mirrored data (aka realtime change tracking) but i want to have the DML
> and data all copied so its a 1:1 copy.. thanks
>|||thanks! thats just what I did
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OKeTrIniGHA.2456@.TK2MSFTNGP04.phx.gbl...
> In your job that performs the backups why not just add one more job step.
> It woudl contain something like this:
> IF (SELECT DATENAME ( wday ,getdate() )) = 'Sunday'
> BEGIN
> RESTORE DATABASE...
> --send email
> --...anything else?
> END
>
> --
> Keith Kratochvil
>
> "Smokey Grindle" <nospamhere@.dontspam.net> wrote in message
> news:ukwlBCniGHA.2220@.TK2MSFTNGP05.phx.gbl...
>

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that executes the following command "restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101 "unable to get exclusive use".
Absolutely no one is on this server when I do this
From which database are you executing the restore command? Make sure it is the master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this server when I do this
|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this
|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this

Automated restore of a user database

I am trying to restore a user database in a scheduled Job I created that exe
cutes the following command "restore database sqleligibilitydatabase from di
sk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101 "un
able to get exclusive use".
Absolutely no one is on this server when I do thisFrom which database are you executing the restore command? Make sure it is t
he master database. you can run an
sp_who just before and get the output from the jobstep to a file and examine
that file to see who is using the
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that executes
the following command
"restore database sqleligibilitydatabase from disk= 'd:\dbfrom01\sqleligibil
itydatabase_db_%.bak'". I get an
error 3101 "unable to get exclusive use". Absolutely no one is on this serv
er when I do this|||Hi,
Turn the database to single user before restore and make it multiuser after
restore:-
Change the script to do below :-
Alter database <dbname> set single_user with rollback immediate
go
restore database dbname from disk=...........
go
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this|||ALTER DATABASE sqleligibilitydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE sqleligibilitydatabase SET ONLINE
RESTORE DATABASE......
That will clear all users out of the database. Also, make sure your job
step does not execute in the context of the target database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:C9D0496B-0E92-48B2-A3EE-860048654DE4@.microsoft.com...
> I am trying to restore a user database in a scheduled Job I created that
executes the following command "restore database sqleligibilitydatabase from
disk= 'd:\dbfrom01\sqleligibilitydatabase_db_%
.bak'". I get an error 3101
"unable to get exclusive use". Absolutely no one is on this server when I
do this