Backup restore linking

Hi guys,
I need some help with backups and restores.
I want to run a backup to disk on server1 followed by a restore on server2
(Sort of replication)
I know how to do this with separate jobs or procedures on each server.
But, is there any way to run this as a single job on server2?
The goal is to abort restore if backup fails rather than relying on "timing"
I'm having problems specifying connections. Not sure if it's doable.
Thanks,
Jim
PS: DTS is too big a mystery...
(Both servers are SQL 2000)Server 1 should already have a preset backup routine that includes a FULL
backup. I would recommend you use that backup instead otherwise you run the
risk of interfering with the existing backup and or restore strategy
depending on how it is set up. And why duplicate the effort in the first
place. Then just make sure that the account on SQL Server on Server 2 has
the proper permissions to view the share where the backups are being sent
and the proper permissions to do a restore on Server 2 and you can do it all
from one job on Server 2.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> I need some help with backups and restores.
> I want to run a backup to disk on server1 followed by a restore on server2
> (Sort of replication)
> I know how to do this with separate jobs or procedures on each server.
> But, is there any way to run this as a single job on server2?
> The goal is to abort restore if backup fails rather than relying on
> "timing"
> I'm having problems specifying connections. Not sure if it's doable.
> Thanks,
> Jim
> PS: DTS is too big a mystery...
> (Both servers are SQL 2000)
>|||Hi Andrew,
Thanks for the help.
The goal is to have an "off-line spare" for disaster recovery.
They want to be able to replace the main server quickly.
For complex reasons it's not possible to use clustering or replication.
So the only solution left is to have a second server that is kept up to date
with regular backups and restores.
I realise the potential for conflicts with backups on both machines.
I can take care of that... I hope... :-)
What I am trying to eliminate is the need to rely on timing between tasks.
I want to be able to run the backup and restore within the same job so as to
handle possible backup failures.
On seperate servers, the restore wouldn't know if the backup failed and
would needlessly restore a previous set.
Not the end of the world but I'm aiming to be as efficient as possible.
The problem is that I can't figure out how to run a baclup on the donor
server from the recipient server.
Is that possible?
Thanks,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Server 1 should already have a preset backup routine that includes a FULL
> backup. I would recommend you use that backup instead otherwise you run
> the risk of interfering with the existing backup and or restore strategy
> depending on how it is set up. And why duplicate the effort in the first
> place. Then just make sure that the account on SQL Server on Server 2 has
> the proper permissions to view the share where the backups are being sent
> and the proper permissions to do a restore on Server 2 and you can do it
> all from one job on Server 2.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>|||Try using stored procedures to handle the remote server actions (be it
backup or restore).
I have set up a system like this for a client with almost 6600 databases on
one server:
backups occur via a scheduled job that calls a sproc that loops the
databases and performs the correct backup type depending on various
settings. there is a table that stores backup information (maintained by
the backup sproc). at the end of the backup sproc a 'prepare restores'
sproc is run on the production server. it copies the necessary information
from the backup log table to a driver table and fires off a scheduled job on
the standby server. that job fires a sproc that gets the data from the 'to
restore' table and executes restore statements based on that information.
Works like a charm!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a FULL
>> backup. I would recommend you use that backup instead otherwise you run
>> the risk of interfering with the existing backup and or restore strategy
>> depending on how it is set up. And why duplicate the effort in the first
>> place. Then just make sure that the account on SQL Server on Server 2 has
>> the proper permissions to view the share where the backups are being sent
>> and the proper permissions to do a restore on Server 2 and you can do it
>> all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>|||See answers in-line:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
If you are on SQL2005 then this sounds like a perfect case for database
mirroring.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
How? You better figure out all the nuances before you implement.
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
I don't see the problem. This technique is used every day by thousands of
sites with no problems. The implementations may vary slightly depending on
requirements but there is no reason you need to redo restores. One way is to
have the backup job on Server A copy the file to another folder or network
share. Then the job on Server B restores the file when it sees it. When done
it removes it so it doesn't try that same file again. Another method is to
name the backup files with a monotomically increasing ID or timestamp and
track which files were restored last so you know which to do next. Neither
of these techniques require duplicating the backup operation although they
may make a copy of the backup file. If the backup fails the files don't get
copied so there is no issues.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
If you log in with the correct accounts you can issue any command you have
the rights to using oSql or SqlCmd. This can be startign a job, calling a
stored procedure or a command directly.
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a FULL
>> backup. I would recommend you use that backup instead otherwise you run
>> the risk of interfering with the existing backup and or restore strategy
>> depending on how it is set up. And why duplicate the effort in the first
>> place. Then just make sure that the account on SQL Server on Server 2 has
>> the proper permissions to view the share where the backups are being sent
>> and the proper permissions to do a restore on Server 2 and you can do it
>> all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>|||Further questions inline below:
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unQTa$jTIHA.6060@.TK2MSFTNGP05.phx.gbl...
> See answers in-line:
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
> If you are on SQL2005 then this sounds like a perfect case for database
> mirroring.
We're working with SQL 2000, I only wish it was 2005.......
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
> How? You better figure out all the nuances before you implement.
That's why I'm taking it slow and methodical. I plan to dump all backups in
favour of the new structure.
(Eventually.. - I am accutely aware of potential conflicts which is why I
didn't just jump right in)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so as
>> to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
> I don't see the problem. This technique is used every day by thousands of
> sites with no problems. The implementations may vary slightly depending on
> requirements but there is no reason you need to redo restores. One way is
> to have the backup job on Server A copy the file to another folder or
> network share. Then the job on Server B restores the file when it sees it.
Ah now, there's the rub. How does it know the file is there?
Remember I am relatively unskilled at SQL, I'm not well versed in t-sql.
I don't know how to remove the file on completed backup.
Although, I suppose I could find it, that's a good pointer / idea, thanks.
How would you do this in sql script?
if exists(backupfile) = false then exit with error
Baring in mind that the backups run to the same network folder that the
restore pulls from.
If the backup is late or still running or gets interupted then the restore
can't run successfully...
that's what I'm trying to rule out.
>When done it removes it so it doesn't try that same file again. Another
>method is to name the backup files with a monotomically increasing ID or
>timestamp and track which files were restored last so you know which to do
>next.
Drive space is an issue, they only have room for one set of backups...
Sucks, but what can you do when they won't buy new hardware...
>Neither of these techniques require duplicating the backup operation
>although they may make a copy of the backup file. If the backup fails the
>files don't get copied so there is no issues.
>
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
> If you log in with the correct accounts you can issue any command you have
> the rights to using oSql or SqlCmd. This can be startign a job, calling a
> stored procedure or a command directly.
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise you
>> run the risk of interfering with the existing backup and or restore
>> strategy depending on how it is set up. And why duplicate the effort in
>> the first place. Then just make sure that the account on SQL Server on
>> Server 2 has the proper permissions to view the share where the backups
>> are being sent and the proper permissions to do a restore on Server 2
>> and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>|||Thanks Kevin,
One question though,
How do you call the sproc situated on the remote machine?
The core of my questioning is how to you specify "run this on machine x" in
t-sql?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqafj3koojca0@.corp.supernews.com...
> Try using stored procedures to handle the remote server actions (be it
> backup or restore).
> I have set up a system like this for a client with almost 6600 databases
> on one server:
> backups occur via a scheduled job that calls a sproc that loops the
> databases and performs the correct backup type depending on various
> settings. there is a table that stores backup information (maintained by
> the backup sproc). at the end of the backup sproc a 'prepare restores'
> sproc is run on the production server. it copies the necessary
> information from the backup log table to a driver table and fires off a
> scheduled job on the standby server. that job fires a sproc that gets the
> data from the 'to restore' table and executes restore statements based on
> that information. Works like a charm!
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so as
>> to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise you
>> run the risk of interfering with the existing backup and or restore
>> strategy depending on how it is set up. And why duplicate the effort in
>> the first place. Then just make sure that the account on SQL Server on
>> Server 2 has the proper permissions to view the share where the backups
>> are being sent and the proper permissions to do a restore on Server 2
>> and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>|||see linked server in BOL. This allows you do to this:
exec remoteservername.remotedatabasename.dbo.somesproc
Note that the Distributed Transaction Coordinator can be a PITA to get set
up correctly! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Thanks Kevin,
> One question though,
> How do you call the sproc situated on the remote machine?
> The core of my questioning is how to you specify "run this on machine x"
> in t-sql?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600 databases
>> on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained by
>> the backup sproc). at the end of the backup sproc a 'prepare restores'
>> sproc is run on the production server. it copies the necessary
>> information from the backup log table to a driver table and fires off a
>> scheduled job on the standby server. that job fires a sproc that gets
>> the data from the 'to restore' table and executes restore statements
>> based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so
>> as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise
>> you run the risk of interfering with the existing backup and or restore
>> strategy depending on how it is set up. And why duplicate the effort
>> in the first place. Then just make sure that the account on SQL Server
>> on Server 2 has the proper permissions to view the share where the
>> backups are being sent and the proper permissions to do a restore on
>> Server 2 and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>>
>|||Thanks, I'll give it a try.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nt26t2jvudl81@.corp.supernews.com...
> see linked server in BOL. This allows you do to this:
> exec remoteservername.remotedatabasename.dbo.somesproc
> Note that the Distributed Transaction Coordinator can be a PITA to get set
> up correctly! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kevin,
>> One question though,
>> How do you call the sproc situated on the remote machine?
>> The core of my questioning is how to you specify "run this on machine x"
>> in t-sql?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600 databases
>> on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained
>> by the backup sproc). at the end of the backup sproc a 'prepare
>> restores' sproc is run on the production server. it copies the
>> necessary information from the backup log table to a driver table and
>> fires off a scheduled job on the standby server. that job fires a sproc
>> that gets the data from the 'to restore' table and executes restore
>> statements based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or replication.
>> So the only solution left is to have a second server that is kept up to
>> date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so
>> as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed and
>> would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as possible.
>> The problem is that I can't figure out how to run a baclup on the donor
>> server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise
>> you run the risk of interfering with the existing backup and or
>> restore strategy depending on how it is set up. And why duplicate the
>> effort in the first place. Then just make sure that the account on SQL
>> Server on Server 2 has the proper permissions to view the share where
>> the backups are being sent and the proper permissions to do a restore
>> on Server 2 and you can do it all from one job on Server 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi guys,
>> I need some help with backups and restores.
>> I want to run a backup to disk on server1 followed by a restore on
>> server2
>> (Sort of replication)
>> I know how to do this with separate jobs or procedures on each
>> server.
>> But, is there any way to run this as a single job on server2?
>> The goal is to abort restore if backup fails rather than relying on
>> "timing"
>> I'm having problems specifying connections. Not sure if it's doable.
>> Thanks,
>> Jim
>> PS: DTS is too big a mystery...
>> (Both servers are SQL 2000)
>>
>>
>>
>>
>|||EXCELLENT!
This is exactly what I was hoping for.
Works great.
Thanks Kevin.
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eQO43rwTIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Thanks, I'll give it a try.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nt26t2jvudl81@.corp.supernews.com...
>> see linked server in BOL. This allows you do to this:
>> exec remoteservername.remotedatabasename.dbo.somesproc
>> Note that the Distributed Transaction Coordinator can be a PITA to get
>> set up correctly! :-)
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kevin,
>> One question though,
>> How do you call the sproc situated on the remote machine?
>> The core of my questioning is how to you specify "run this on machine x"
>> in t-sql?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600
>> databases on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained
>> by the backup sproc). at the end of the backup sproc a 'prepare
>> restores' sproc is run on the production server. it copies the
>> necessary information from the backup log table to a driver table and
>> fires off a scheduled job on the standby server. that job fires a
>> sproc that gets the data from the 'to restore' table and executes
>> restore statements based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or
>> replication.
>> So the only solution left is to have a second server that is kept up
>> to date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job so
>> as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed
>> and would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as
>> possible.
>> The problem is that I can't figure out how to run a baclup on the
>> donor server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>> Server 1 should already have a preset backup routine that includes a
>> FULL backup. I would recommend you use that backup instead otherwise
>> you run the risk of interfering with the existing backup and or
>> restore strategy depending on how it is set up. And why duplicate
>> the effort in the first place. Then just make sure that the account
>> on SQL Server on Server 2 has the proper permissions to view the
>> share where the backups are being sent and the proper permissions to
>> do a restore on Server 2 and you can do it all from one job on Server
>> 2.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>>> Hi guys,
>>>
>>> I need some help with backups and restores.
>>>
>>> I want to run a backup to disk on server1 followed by a restore on
>>> server2
>>> (Sort of replication)
>>>
>>> I know how to do this with separate jobs or procedures on each
>>> server.
>>> But, is there any way to run this as a single job on server2?
>>> The goal is to abort restore if backup fails rather than relying on
>>> "timing"
>>>
>>> I'm having problems specifying connections. Not sure if it's doable.
>>>
>>> Thanks,
>>> Jim
>>>
>>> PS: DTS is too big a mystery...
>>> (Both servers are SQL 2000)
>>>
>>
>>
>>
>>
>|||Glad to be able to help you!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:ODIwsCxTIHA.5264@.TK2MSFTNGP02.phx.gbl...
> EXCELLENT!
> This is exactly what I was hoping for.
> Works great.
> Thanks Kevin.
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eQO43rwTIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Thanks, I'll give it a try.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nt26t2jvudl81@.corp.supernews.com...
>> see linked server in BOL. This allows you do to this:
>> exec remoteservername.remotedatabasename.dbo.somesproc
>> Note that the Distributed Transaction Coordinator can be a PITA to get
>> set up correctly! :-)
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>> Thanks Kevin,
>> One question though,
>> How do you call the sproc situated on the remote machine?
>> The core of my questioning is how to you specify "run this on machine
>> x" in t-sql?
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13nqafj3koojca0@.corp.supernews.com...
>> Try using stored procedures to handle the remote server actions (be it
>> backup or restore).
>> I have set up a system like this for a client with almost 6600
>> databases on one server:
>> backups occur via a scheduled job that calls a sproc that loops the
>> databases and performs the correct backup type depending on various
>> settings. there is a table that stores backup information (maintained
>> by the backup sproc). at the end of the backup sproc a 'prepare
>> restores' sproc is run on the production server. it copies the
>> necessary information from the backup log table to a driver table and
>> fires off a scheduled job on the standby server. that job fires a
>> sproc that gets the data from the 'to restore' table and executes
>> restore statements based on that information. Works like a charm!
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Hi Andrew,
>> Thanks for the help.
>> The goal is to have an "off-line spare" for disaster recovery.
>> They want to be able to replace the main server quickly.
>> For complex reasons it's not possible to use clustering or
>> replication.
>> So the only solution left is to have a second server that is kept up
>> to date with regular backups and restores.
>> I realise the potential for conflicts with backups on both machines.
>> I can take care of that... I hope... :-)
>> What I am trying to eliminate is the need to rely on timing between
>> tasks.
>> I want to be able to run the backup and restore within the same job
>> so as to handle possible backup failures.
>> On seperate servers, the restore wouldn't know if the backup failed
>> and would needlessly restore a previous set.
>> Not the end of the world but I'm aiming to be as efficient as
>> possible.
>> The problem is that I can't figure out how to run a baclup on the
>> donor server from the recipient server.
>> Is that possible?
>> Thanks,
>> Jim
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>>> Server 1 should already have a preset backup routine that includes a
>>> FULL backup. I would recommend you use that backup instead otherwise
>>> you run the risk of interfering with the existing backup and or
>>> restore strategy depending on how it is set up. And why duplicate
>>> the effort in the first place. Then just make sure that the account
>>> on SQL Server on Server 2 has the proper permissions to view the
>>> share where the backups are being sent and the proper permissions to
>>> do a restore on Server 2 and you can do it all from one job on
>>> Server 2.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
>>> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>>> Hi guys,
>>>
>>> I need some help with backups and restores.
>>>
>>> I want to run a backup to disk on server1 followed by a restore on
>>> server2
>>> (Sort of replication)
>>>
>>> I know how to do this with separate jobs or procedures on each
>>> server.
>>> But, is there any way to run this as a single job on server2?
>>> The goal is to abort restore if backup fails rather than relying on
>>> "timing"
>>>
>>> I'm having problems specifying connections. Not sure if it's
>>> doable.
>>>
>>> Thanks,
>>> Jim
>>>
>>> PS: DTS is too big a mystery...
>>> (Both servers are SQL 2000)
>>>
>>>
>>
>>
>>
>>
>>
>