Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many Thanks
Yes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping functionality questions

SQL2K sp3
I need to have a basic understanding of LS in order to
answer some Disaster Recovery questions today. I
understand that it takes TLog backups from BoxA(Production
Server in use) and restores them to BoxB(secondary server
that those backups are being sent too) using NoRecovery. I
also understand that if BoxA dies BoxB can perform a Role
Change and become the Production Box. This part Im clear
on.
But what Im not clear on once BoxA is back online what
takes place? Im pretty sure you can do another Role Change
make BoxA the Production Box again. But what actually
takes place here?
Does BoxA go through a Restore process of all its old
backups using NoRecovery, have the latest TLog backups
shipped from BoxB, restore those with Recovery, and then
become Production again? This is what Im guessing would
need to happen in order to accomplish a Role Change from
BoxB to BoxA. But Im not sure and could use some insight.
Of course I JUST found out I need to know this stuff
IMMEDIATELY from the higher ups or else I would just try
testing this out on my own. So sorry for the lazy
questions here.
TIA, ChrisRfull backup of B applied to A. Then apply Trnxn Logs From B To A and A is
"HOT" again.
not sure if that answers your question.
are you planning on using the maintenance plan stuff...aka "Log shipping
Wizard" ?
Greg Jackson
PDX, Oregon|||>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
So I actually have to do a full transfer over the line
from B to A? Is this correct? How do companies with large
db's use this as an Enterprise solution? Im not trying to
sound ungrateful for your help, just trying to get a clear
understanding.

>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
Yes.

>--Original Message--
>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
>
>not sure if that answers your question.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
>
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||it aint easy.
moving data of the wire SUX. Especially for large Dbs.
this is really a "Poor Man's Replication" so larger companies probably just
use replication.
I have done tons of this in the past and am doing a huge log shipping
implementation right now.
I recommend NOT using the Wizard Thingy.
Roll your own.
if you need scripts, have questions, etc let me know.
GAJ|||Thanks for the info Jaxon.

>--Original Message--
>it aint easy.
>moving data of the wire SUX. Especially for large Dbs.
>this is really a "Poor Man's Replication" so larger
companies probably just
>use replication.
>I have done tons of this in the past and am doing a huge
log shipping
>implementation right now.
>I recommend NOT using the Wizard Thingy.
>Roll your own.
>if you need scripts, have questions, etc let me know.
>
>GAJ
>
>.
>

Log Shipping functionality questions

SQL2K sp3
I need to have a basic understanding of LS in order to
answer some Disaster Recovery questions today. I
understand that it takes TLog backups from BoxA(Production
Server in use) and restores them to BoxB(secondary server
that those backups are being sent too) using NoRecovery. I
also understand that if BoxA dies BoxB can perform a Role
Change and become the Production Box. This part Im clear
on.
But what Im not clear on once BoxA is back online what
takes place? Im pretty sure you can do another Role Change
make BoxA the Production Box again. But what actually
takes place here?
Does BoxA go through a Restore process of all its old
backups using NoRecovery, have the latest TLog backups
shipped from BoxB, restore those with Recovery, and then
become Production again? This is what Im guessing would
need to happen in order to accomplish a Role Change from
BoxB to BoxA. But Im not sure and could use some insight.
Of course I JUST found out I need to know this stuff
IMMEDIATELY from the higher ups or else I would just try
testing this out on my own. So sorry for the lazy
questions here.
TIA, ChrisR
full backup of B applied to A. Then apply Trnxn Logs From B To A and A is
"HOT" again.
not sure if that answers your question.
are you planning on using the maintenance plan stuff...aka "Log shipping
Wizard" ?
Greg Jackson
PDX, Oregon
|||>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
So I actually have to do a full transfer over the line
from B to A? Is this correct? How do companies with large
db's use this as an Enterprise solution? Im not trying to
sound ungrateful for your help, just trying to get a clear
understanding.

>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
Yes.

>--Original Message--
>full backup of B applied to A. Then apply Trnxn Logs From
B To A and A is
>"HOT" again.
>
>not sure if that answers your question.
>are you planning on using the maintenance plan
stuff...aka "Log shipping
>Wizard" ?
>
>
>Greg Jackson
>PDX, Oregon
>
>.
>
|||it aint easy.
moving data of the wire SUX. Especially for large Dbs.
this is really a "Poor Man's Replication" so larger companies probably just
use replication.
I have done tons of this in the past and am doing a huge log shipping
implementation right now.
I recommend NOT using the Wizard Thingy.
Roll your own.
if you need scripts, have questions, etc let me know.
GAJ
|||Thanks for the info Jaxon.

>--Original Message--
>it aint easy.
>moving data of the wire SUX. Especially for large Dbs.
>this is really a "Poor Man's Replication" so larger
companies probably just
>use replication.
>I have done tons of this in the past and am doing a huge
log shipping
>implementation right now.
>I recommend NOT using the Wizard Thingy.
>Roll your own.
>if you need scripts, have questions, etc let me know.
>
>GAJ
>
>.
>

log shipping barfing

sql2k sp2
Log Shipping gets set up successfully. The db gets created
on the secondary box. All the TLog backups get transferred
to the secondary box. But then after some time it gets out
of sync. In the reporting file there is the message:
(snippet)
(SQL Server does not exist or access is denied)
How could this be since it was able to intialize the new
db? If it never did that and I got this message it would
make more sense than creating the db but not baing able to
connect after that.
TIA, ChrisChris,
Which job is failing? Sounds like the restore job on the secondary. Are you
sure SQL Agent on the secondary can connect to the secondary SQL Server?
Ron
--
Ron Talmage
SQL Server MVP
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:05e001c3a4ca$1a848f70$a301280a@.phx.gbl...
> sql2k sp2
> Log Shipping gets set up successfully. The db gets created
> on the secondary box. All the TLog backups get transferred
> to the secondary box. But then after some time it gets out
> of sync. In the reporting file there is the message:
> (snippet)
> (SQL Server does not exist or access is denied)
> How could this be since it was able to intialize the new
> db? If it never did that and I got this message it would
> make more sense than creating the db but not baing able to
> connect after that.
> TIA, Chris
>|||Are you
>sure SQL Agent on the secondary can connect to the
secondary SQL Server?
Not sure Im following on this one.
>--Original Message--
>Chris,
>Which job is failing? Sounds like the restore job on the
secondary. Are you
>sure SQL Agent on the secondary can connect to the
secondary SQL Server?
>Ron
>--
>Ron Talmage
>SQL Server MVP
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05e001c3a4ca$1a848f70$a301280a@.phx.gbl...
>> sql2k sp2
>> Log Shipping gets set up successfully. The db gets
created
>> on the secondary box. All the TLog backups get
transferred
>> to the secondary box. But then after some time it gets
out
>> of sync. In the reporting file there is the message:
>> (snippet)
>> (SQL Server does not exist or access is denied)
>> How could this be since it was able to intialize the new
>> db? If it never did that and I got this message it would
>> make more sense than creating the db but not baing able
to
>> connect after that.
>> TIA, Chris
>
>.
>

Log shipping and Manual backups

I have set up a log shipping on my master and backup database, where a full
backup of the master database is done each day at midnight and restored to
the backup database. The master databases transaction logs are then backed
up and restored to the backup database every 10 minutes.
My users want to also have the ability to do manual full database backups as
well. However I am unsure whether or not this will have the affect that the
log shipping operations.
Will this cause the master and backup databases to get out of synch until
the full backup and restore is performed at midnight?
Thanks in advance
No worries. Database backup does not break the log backup chain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Parks" <treatyourselfnow@.hotmail.com> wrote in message
news:%23OJ4KyGHGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have set up a log shipping on my master and backup database, where a full backup of the master
>database is done each day at midnight and restored to the backup database. The master databases
>transaction logs are then backed up and restored to the backup database every 10 minutes.
> My users want to also have the ability to do manual full database backups as well. However I am
> unsure whether or not this will have the affect that the log shipping operations.
> Will this cause the master and backup databases to get out of synch until the full backup and
> restore is performed at midnight?
> Thanks in advance
>

Log shipping and Manual backups

I have set up a log shipping on my master and backup database, where a full
backup of the master database is done each day at midnight and restored to
the backup database. The master databases transaction logs are then backed
up and restored to the backup database every 10 minutes.
My users want to also have the ability to do manual full database backups as
well. However I am unsure whether or not this will have the affect that the
log shipping operations.
Will this cause the master and backup databases to get out of synch until
the full backup and restore is performed at midnight?
Thanks in advanceNo worries. Database backup does not break the log backup chain.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Parks" <treatyourselfnow@.hotmail.com> wrote in message
news:%23OJ4KyGHGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have set up a log shipping on my master and backup database, where a full backup of the master
>database is done each day at midnight and restored to the backup database. The master databases
>transaction logs are then backed up and restored to the backup database every 10 minutes.
> My users want to also have the ability to do manual full database backups as well. However I am
> unsure whether or not this will have the affect that the log shipping operations.
> Will this cause the master and backup databases to get out of synch until the full backup and
> restore is performed at midnight?
> Thanks in advance
>

Log shipping and Manual backups

I have set up a log shipping on my master and backup database, where a full
backup of the master database is done each day at midnight and restored to
the backup database. The master databases transaction logs are then backed
up and restored to the backup database every 10 minutes.
My users want to also have the ability to do manual full database backups as
well. However I am unsure whether or not this will have the affect that the
log shipping operations.
Will this cause the master and backup databases to get out of synch until
the full backup and restore is performed at midnight?
Thanks in advanceNo worries. Database backup does not break the log backup chain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Parks" <treatyourselfnow@.hotmail.com> wrote in message
news:%23OJ4KyGHGHA.3120@.TK2MSFTNGP10.phx.gbl...
>I have set up a log shipping on my master and backup database, where a full
backup of the master
>database is done each day at midnight and restored to the backup database.
The master databases
>transaction logs are then backed up and restored to the backup database eve
ry 10 minutes.
> My users want to also have the ability to do manual full database backups
as well. However I am
> unsure whether or not this will have the affect that the log shipping oper
ations.
> Will this cause the master and backup databases to get out of synch until
the full backup and
> restore is performed at midnight?
> Thanks in advance
>

Log shipping and database backups how do they live together?

Hello,
We are planning to use log shipping as replication method for standby
database. My understanding that I have to make full database backup, restore
it on standby server (with NORECOVERY option) and then can just copy
transaction logs to standby server and apply them there periodically. I'm not
using enterprise edition so I'll use SimpleLogShipper from resource kit.
My question is what kind of backups I can make on primary server in order
for this to work? If I do transaction log backup my understanding transaction
log will be truncated and nothing will be shipped to standby server?
Second question if I may. What happens if I have tons of transactions which
modify small subsets of rows in primary database will shipping and restoring
differential backups will be a better method?
Log shipping works in the full and bulk logged recovery models. You need to
start off with a full backup, and then do transaction log dumps. Inactive
portions of the log are marked for reuse after the log is dumped, it is not
truncated. The file which you dump your transaction log is what you ship and
apply on the standby server.
If you have tones of logged activity (small subsets of rows) your
transaction log dumps will be larger and take longer to dump, copy and
restore on the standby server, but typically not appreciably longer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:B716A708-983D-4201-BA61-6AECEA33D6A3@.microsoft.com...
> Hello,
> We are planning to use log shipping as replication method for standby
> database. My understanding that I have to make full database backup,
> restore
> it on standby server (with NORECOVERY option) and then can just copy
> transaction logs to standby server and apply them there periodically. I'm
> not
> using enterprise edition so I'll use SimpleLogShipper from resource kit.
> My question is what kind of backups I can make on primary server in order
> for this to work? If I do transaction log backup my understanding
> transaction
> log will be truncated and nothing will be shipped to standby server?
> Second question if I may. What happens if I have tons of transactions
> which
> modify small subsets of rows in primary database will shipping and
> restoring
> differential backups will be a better method?
|||Can I make differential backups along with log dumps without screwing up log
shipping?
"Hilary Cotter" wrote:

> Log shipping works in the full and bulk logged recovery models. You need to
> start off with a full backup, and then do transaction log dumps. Inactive
> portions of the log are marked for reuse after the log is dumped, it is not
> truncated. The file which you dump your transaction log is what you ship and
> apply on the standby server.
> If you have tones of logged activity (small subsets of rows) your
> transaction log dumps will be larger and take longer to dump, copy and
> restore on the standby server, but typically not appreciably longer.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:B716A708-983D-4201-BA61-6AECEA33D6A3@.microsoft.com...
>
>
|||absolutely.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:1B0EAFC0-0E1E-441A-81CE-E8ED232C0EE6@.microsoft.com...[vbcol=seagreen]
> Can I make differential backups along with log dumps without screwing up
> log
> shipping?
> "Hilary Cotter" wrote:

Log Shipping and Backup Jobs = Conflict?

Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx
.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.

Log Shipping and Backup Jobs = Conflict?

Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.
As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.

Log Shipping and Backup Jobs = Conflict?

Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.

Log Shipping (sort of) with Standard Edition

I have about 30 Databases on a Production SQL Server STANDARD Edition 2000k Instance.

All Backups are performed using SQL Server Maintenance plans (Full Nightly backups and periodic Xaction log backups during business hours)

I would like the ability to copy the FULL Backups to another Server - perhaps on a nightly basis - and then restore them to this other Server as a means of having some sort of Disaster Recovery.

I know with Enterprise Edition, you can set up linked servers and do log shipping or replication, but I don't have that luxury here. I also don't have access to the MS Backoffice Resource Kit - which I understand has some canned scripts to do what I want to do here.

My problem really is that because I don't have control over the backup filenames, how to I automate the restore process on the second Server?

any help would be much appreciated.You could try using the Scripting.FileSystemObject to perform a directory listing of the files in a particular directory. Then use a DO...LOOP to scroll through the list, copying the file and then restoring it to a remote server.

Alternatively, SQL keeps track of the file names used for backups in the msdb database. Look at the following tables:

backupfile
backupmediafamily
backupmediaset
backupset

Finally, you might just consider having the backups run out over the network to a remote server. In the event the primary node fails, you could restore the backups to an alternate server pretty quickly. Just remember that you also need to script off logins, jobs and save DTS packages (as binary files). You then also need to "exercise" your disaster recovery plan on a periodic basis to validate/verify your processes.

Regards,

hmscott|||Thank you for your reply!

I am actually already doing backups locally as well as to another Server on the Network - My problem was how to 'programatically' restore these backups on the remote server.

I found this script - posted by someone else on another forum...

It is SWEET!!!! As long as my backups are in a certain format and all in the same directory, this store procedure will check the folder for the newest .bak file for a specific database and restore it!!!!

So now what I have done is set up a maintenance plan on my prod server to do a full set of backups to this 'network' server - then I just schedule this stored procedure to run on the 'network' server and my databases are automatically refreshed..

Thanks again for your help|||Hi,
Can you let me know where you got that script...
Looking to do something similar.

Thanks,
-jmr|||I would also like to see that script.

Log Shipping & Backups

I have a Win2K3 SQL Cluster that is currently log shipping to my DR SQL
Server and a DEV SQL Server. Everything is working perfectly. For piece of
mind, I would still like to do a backup of the production server everynight
but that is not possible when log shipping is implemented.
What is the best way I can do this on the DEV Server? As you know, the
database is in a read only state. Can I take the database offline and copy
the *.mdf file and attempt to do a restore to another database name? I am
not a SQL expert by any means but there has to be a way to restore the
database so I can test it and back it up. Any suggestions?
Thanks!!
I'm not too sure about this: "I would still like to do a backup of the
production server everynight
but that is not possible when log shipping is implemented" - why not? It's a
while since I've done this, but AFAIR a full backup on the prod server
shouldn't interrupt the log shipping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the response. I had a post about a month ago (See Log Shiping
8/7/2005 - 8/10/2005) that said you can not backup the database when log
shipping is active.
Here was the response:
"Logshipping bases on incremental backups. The regular backup every four
hours sets a new starting point for the TA-Log which will not be understood
by the standby server - it will need to be reinitialized with the new full
database backup ... I never found a method to backup my databases with
active logshipping - the standby server seems to be the only backup you get!
I gave up logshipping because of that and use a rather complex transactional
replication based scenario instead ..."
If you can backup the production database when log shipping is active I
would love to know how. What do you mean "AFAIR" a full backup? Thanks!!
"Paul Ibison" wrote:

> I'm not too sure about this: "I would still like to do a backup of the
> production server everynight
> but that is not possible when log shipping is implemented" - why not? It's a
> while since I've done this, but AFAIR a full backup on the prod server
> shouldn't interrupt the log shipping.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My experience is that this is incorrect. I just tested this - I did backups
in this order:
'c:\backups\backupdb1.bak'
'c:\backups\backuplog2.bak'
'c:\backups\backupdb3.bak'
'c:\backups\backuplog4.bak'
And the following restore path was successful:
restore database northwind from disk = 'c:\backups\backupdb1.bak' with
norecovery
restore log northwind from disk = 'c:\backups\backuplog2.bak' with
norecovery
restore log northwind from disk = 'c:\backups\backuplog4.bak' with
norecovery
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
So you had log shipping enabled on the Nortwind DB then you performed a
backup? Was there any special way you did the backup? What about the comment
"The regular backup every four hours sets a new starting point for the TA-Log
which will not be understood by the standby server" Is this comment not
true? Before I attempt a backup tonight I need to make sure. Thanks!!
"Paul Ibison" wrote:

> My experience is that this is incorrect. I just tested this - I did backups
> in this order:
> 'c:\backups\backupdb1.bak'
> 'c:\backups\backuplog2.bak'
> 'c:\backups\backupdb3.bak'
> 'c:\backups\backuplog4.bak'
> And the following restore path was successful:
> restore database northwind from disk = 'c:\backups\backupdb1.bak' with
> norecovery
> restore log northwind from disk = 'c:\backups\backuplog2.bak' with
> norecovery
> restore log northwind from disk = 'c:\backups\backuplog4.bak' with
> norecovery
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Now I'm confused - are you saying I made the comment? and if so, please
can you point me to the source. Anyway, I believe this comment is mistaken,
and certainly in my test that I related to you what you propose is indeed
possible - please try it oput for yourself. If not, everyone who implemented
log-shipping would be prevented from backing up their database using a main
backup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Sorry for the confusion. I didn't say you made the comment it was posted to
me in another Post. (Please see Log Shipping 8/7/2005) by Christian Donner.
He indicated you could not do a backup on the database that is log shipping
to a DR or Hot Stand By server. He says once you do the backup the on the
production server it "sets a new starting point for the TA-Log which will
not be understood
by the standby server - it will need to be reinitialized with the new full
database backup ..."
I hope this clears it up!!
"Paul Ibison" wrote:

> Now I'm confused - are you saying I made the comment? and if so, please
> can you point me to the source. Anyway, I believe this comment is mistaken,
> and certainly in my test that I related to you what you propose is indeed
> possible - please try it oput for yourself. If not, everyone who implemented
> log-shipping would be prevented from backing up their database using a main
> backup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
|||OK - thanks for the clarification, and this definitely contradicts my test
when doing the backups manually. I'll test this with a log shipping setup
when I get access to a network, but perhaps you could test it on yours
meanwhile.
Cheers,
Paul Ibison
|||Paul,
You were correct!! I was able to backup my production databases even though
log shipping was active on this server. I tested this in my DEV environment
first and had no issues. Thanks again for working through this.
I guess sometimes you get bad info on these boards.
"Paul Ibison" wrote:

> OK - thanks for the clarification, and this definitely contradicts my test
> when doing the backups manually. I'll test this with a log shipping setup
> when I get access to a network, but perhaps you could test it on yours
> meanwhile.
> Cheers,
> Paul Ibison
>
>
|||Thanks for the followup - glad it works.
Must admit that I was beginning to doubt myself and even got some other MVPs
to verify what I was saying
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

log shipping - synch

I have to schedule the automatic restore the standby database for
testing backups . The database was already in log shipping mode. Up to
what point do I need to restore the database without affecting the log
shipping sequence? Can we query the log shipping tables for the last
scn applied and recover up to that point? Any suggestoions are welcome.

Thanks

TramHi

Have you checked out other posts regarding re-syncing log shipping. Such as:
http://tinyurl.com/cot3k

If you want to look at manual log shipping check out
http://www.winnetmag.com/Artic*le/A...3231/23231.html
http://tinyurl.com/4ez3l
http://tinyurl.com/6rdth

John

"tram" <tram_e@.hotmail.com> wrote in message
news:1117879701.158308.305390@.o13g2000cwo.googlegr oups.com...
>I have to schedule the automatic restore the standby database for
> testing backups . The database was already in log shipping mode. Up to
> what point do I need to restore the database without affecting the log
> shipping sequence? Can we query the log shipping tables for the last
> scn applied and recover up to that point? Any suggestoions are welcome.
>
> Thanks
> Tram