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)