sql backup files: restores

Showing posts with label restores. Show all posts

Tuesday, March 20, 2012

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

Thursday, March 8, 2012

backup password

Hi
I have created a backup file of database with password. When I restore the backup file, it restores sucessfully without asking for the password. What is the use of password being supplied in the T-sql when it does not check for password when we restore it
Any commentsAre you sure you are restoring from the right backup?
Can you try this and see if it lets you restore without password?
USE Master
GO
BACKUP DATABASE Pubs TO DISK = 'Pubs.BAK' WITH PASSWORD = 'A123', INIT
GO
RESTORE DATABASE Pubs FROM DISK='Pubs.Bak'
--WITH PASSWORD = 'A123'
GO
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4BD6F-B978-410D-B4EF-2FE193E8285B@.microsoft.com...
Hi,
I have created a backup file of database with password. When I restore the
backup file, it restores sucessfully without asking for the password. What
is the use of password being supplied in the T-sql when it does not check
for password when we restore it.
Any comments ?

backup password

Hi,
I have created a backup file of database with password. When I restore the b
ackup file, it restores sucessfully without asking for the password. What is
the use of password being supplied in the T-sql when it does not check for
password when we restore itAre you sure you are restoring from the right backup?
Can you try this and see if it lets you restore without password?
USE Master
GO
BACKUP DATABASE Pubs TO DISK = 'Pubs.BAK' WITH PASSWORD = 'A123', INIT
GO
RESTORE DATABASE Pubs FROM DISK='Pubs.Bak'
--WITH PASSWORD = 'A123'
GO
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4BD6F-B978-410D-B4EF-2FE193E8285B@.microsoft.com...
Hi,
I have created a backup file of database with password. When I restore the
backup file, it restores sucessfully without asking for the password. What
is the use of password being supplied in the T-sql when it does not check
for password when we restore it.
Any comments ?

Monday, February 13, 2012

Backup jobs and restores reporting false success!

Below are the problems I am having with my SQL 7.0
server. The first problem is backup jobs created with
the backup wizard and scheduled with the backup wizard
fail. The latest problem I discovered is my databases
can not be restored. The restore problem is very
critical to solve.
I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
I am experiencing the same behavior on my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
>--Original Message--
>been researching a problem I discovered yesterday on the
>SQLSever 7.0 I administer. I discovered Call Heller
>posted a problem very similar to what I am
experiencing.
>
>Every morning I check to see if my SQL backup jobs run.
>No errors are reported. Yesterday, I was ask to restore
a
>database and discovered there was not output file i.e.
>teachercert_db_200308201656.BAK. I viewed job log,
>backup maintenance plan history and event viewer, all
>reported that the output file was created. Now, if I go
>in and manual backup the database directly from the
>database "All Task" option, an output file is created.
>
>Next I discovered all the database maintenance plans I
>viewed on changed yesterday, did not generate an output
>file when the scheduled job ran.
>
>My questions are:
>
>1. Why is the job not generating a output file and
all
>log report the output file was created?
>2. How do I open a case with Microsoft
>
>I'd appreciate any help you can offer.
>
>Kathy Long
>
>.
>It seems that you can create a backup job successfully just have
problems with the wizard.
Do you mean the maintenance exe? I always advise against using that so I
would say stick with simplest solution.
Instead of creating a device use backup dateabase dbname to disk = ...
This will create a backup file for you.
I always include the date and time in the filename to make
administration easier.
Here is an SP that will backup all databases on a server - you can get
some ideas from that:
http://www.nigelrivett.net/BackupAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
About the backup wizard to backup the database, I have provided solution
and you can try the steps I provided and if there is any problem, please
feel free to post any new message in that post and I am pleased and ready
to provide support there.
In this post, I will assist you to solve the prolem of restore your
database in SQL Server 7.0. I will work on it today. But please go to the
old post to check if the backup problem is solved.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
From the information you provided, your problem should be:
You want to restore database from a file. This process succeeded in SQL
Server 2000 but failed in SQL Server 7.
Actually, because the information you provide is so limited, I cannot give
an answer or solution this time. I wonder if you could provide the
information below for further analysis:
1) When using the Enterprise Manager to restore the database, what steps
have you taken? Could you give me more detailed information? Because you
just said you restored a database, but it is gone. I just want to the
detailed information of how you carry out this restore process, by T-SQL or
by Wizard? Have you got any message indicate that the restore is successful
or failed, what is these messages? Could you provide the System and
Application logs together with the output of SQLDiag if they are available
to you?
2) What kind of file you are using in this backup process? Is the file you
used the MDF file you mentioned above? If not, is it a SQL Server 2000
backup file or a SQL Server 7 backup file?
The feedback from you will be great helpful in clarifying the problem you
encountered and for us to analysis. I am waiting on your response and ready
to provide further help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||1. Steps to Enterprise manager is to right click on the
database to restore the backup file to and select the
file I want to use. Then on the Option tab I choice to
restore over the current database and change the path to
E:\Data\Test.mdb. The system clains it restore correctly
but when I attempt to open up the database I receive this
message: Error 911- could not locate entry in
sysdatabase 'Test.' No entry found with that name. Make
sure that the name is entered correctly.
I know the database is there because I still see it in
the file folder.
2. The file is created with SQL Server 7.0. I've used
backup devices and files.
When I do a manual using T-SQL restore to the same
database with the following command
RESTORE DATABASE PBDMI
FROM DISK = 'H:\PBDMI.bak'
I receive the following info in my results pane.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (WrapperRead()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General
network error. Check your network documentation.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 1.
Connection Broken
The database actually restores because I've deleted a row
in one of the tables and it comes back after the
restore. No luck restoring to a different database.
When I run the code to restore to a different database:
BACKUP DATABASE PBDMI
TO DISK = 'H:\PBDMI.bak'
RESTORE FILELISTONLY
FROM DISK = 'H:\PBDMI.bak'
RESTORE DATABASE PBDMI_New
FROM DISK = 'H:\PBDMI.bak'
WITH MOVE 'PBDMI' TO 'E:\Data\PBDMI_New_Data.mdf',
MOVE 'PBDMI_log' TO 'E:\Data\PBDMI_New_Log.ldf',
REPLACE
GO
Here is the message I receive. I pretty sure our
language is US-English
Changed language setting to us_english.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 6.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 6.
Backup or restore operation successfully processed 28969
pages in 38.095 seconds (6.229 MB/sec).
LogicalName
PhysicalName
Type
FileGroupName
Size
MaxSize
----
----
-- ---
----
----
----
--- --
-- ----
----
-- -- --
--
PBDMI_Data
e:\data\PBDMI_Data.MDF
D
PRIMARY
240582656 35184372080640
PBDMI_Log
e:\data\PBDMI_Log.LDF
L
NULL
704905216 35184372080640
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 9
File 'PBDMI' is not a database file for
database 'PBDMI_New'.
Server: Msg 3013, Level 16, State 1, Line 9
Backup or restore operation terminating abnormally.
I cannot send any file to your email address.
Thanks for your help.
Kathy
>--Original Message--
>Hi Kathy,
> Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
> From the information you provided, your problem should
be:
>You want to restore database from a file. This process
succeeded in SQL
>Server 2000 but failed in SQL Server 7.
>
>Actually, because the information you provide is so
limited, I cannot give
>an answer or solution this time. I wonder if you could
provide the
>information below for further analysis:
>1) When using the Enterprise Manager to restore the
database, what steps
>have you taken? Could you give me more detailed
information? Because you
>just said you restored a database, but it is gone. I
just want to the
>detailed information of how you carry out this restore
process, by T-SQL or
>by Wizard? Have you got any message indicate that the
restore is successful
>or failed, what is these messages? Could you provide
the System and
>Application logs together with the output of SQLDiag if
they are available
>to you?
>2) What kind of file you are using in this backup
process? Is the file you
>used the MDF file you mentioned above? If not, is it a
SQL Server 2000
>backup file or a SQL Server 7 backup file?
> The feedback from you will be great helpful in
clarifying the problem you
>encountered and for us to analysis. I am waiting on your
response and ready
>to provide further help!
>
>Best regards
>Baisong Wei
> Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>

Tuesday, March 20, 2012

Backup restore linking

Backup restore linking

Backup restore linking

Thursday, March 8, 2012

backup password

backup password

Monday, February 13, 2012

Backup jobs and restores reporting false success!

sql backup files

Blog Archive

About Me