August 12, 2011 at 3:08 am
Hi All,
I thought it was possible and before I break anything I just wanted to check to make sure that I can or cannot take a full backup of a database in Standby/Read-Only mode whilst the database is acting as a secondary database in a log shipping setup.
Can I do it? :w00t:
Many Thanks
August 12, 2011 at 3:23 am
It Depends.
You cannot take a native backup of the databases if they are in Standby/Read Only.
If your SQL Databases make use of SAN storage then you can take a snapshot of the data which can be used to present to other servers (mount snapshot as a new volume and attach the databases) I do a similar thing using SnapDrive on NetApp FAS. I would not recomend this as a backup plan.
Thanks
Chris
August 12, 2011 at 3:36 am
I sadly do not have anything that sophisticated. I do have a basic SAN and some transaction log's but nothing to snapshot.
Is there any chance I can rebuild a database just from transaction logs?
What other options do you think there are??
August 12, 2011 at 3:44 am
What is it that you are trying to acheive?
August 12, 2011 at 3:51 am
I basically just realised that if my primary database fails backing up and/or log shipping and I loose the database backups I'll have no really important production database data for my users. I say this because of late I have been having problems with the full backups from my primary whereby either I will have an incomplete backup because of disk space or another mishap will happen whereby another process will delete the archived backups. I am now finally on top of all of this and have managed to straighten out the kinks from other processes or people. So what I would really like to do is just take a full backup of the secondary database server without knocking the log shipping. What I have read is that I can put the secondary database into normal mode and then take a backup and then knock it back into standby/read-only mode all with T-SQL. I am still deciding whether I want to try and do this or not at the moment because it will of course affect the log shipping setup I current have running sweetly on the primary and secondary databases.
Thanks again
August 12, 2011 at 4:03 am
To make the secondary log shipping instance 'online' and accesible you need to restore a transaction log with recovery. This then means you would need to resetup log shipping (restore full backup and tx log backups).
If i understand this correctly you have full and transaction log backups on your primary server the transaction logs are copied and restored to your secondary server.
Do you copy the backups to a network location?
Do they get backed up to tape?
What version / edition of SQL Server are you running - Would you run the below SQL and post the results for both nodes please?
SELECT SERVERPROPERTY('Edition')
UNION
SELECT SERVERPROPERTY('EngineEdition')
UNION
SELECT SERVERPROPERTY('ProductVersion')
UNION
SELECT SERVERPROPERTY('ProductLevel')
Thanks
Chris
August 12, 2011 at 4:10 am
I really do not want to keep stopping and starting the transaction log backup process as I need my hands free for other projects. However you are correct in saying that I would need to bring the database back online.
I do copy to a network location.
|The data you asked for|
Primary
10.0.1600.22
RTM
Standard Edition (64-bit)
2
Secondary
10.0.2531.0
SP1
Standard Edition (64-bit)
2
August 12, 2011 at 4:51 am
Your environment looks to be sound then from a recovery point of view.
You have a warm standby in place with log shipping so if you loose your primary server you can recover on your secondary. If in the event of you loosing both servers your data is secure on a network location.
The only thing i would say make sure of is that you verify your backups. If you get corruption on one of your database on your primary server then this will be log shipped to your secondary server. This can happen at any point and you won't know about it until the page(s) that are corrupt are read from or written too.
If you are not verifying your backups and you encounter corruption then this too may exist in your backups so you could end up restoring the corruption. At this point you may be forced to allow data loss!!!
I would recommend running checkdb against all databases at least once a week and verifying your backups.
Hope this helps if there is anything else I can help with you can email me @ ckwmcgowan@gmail.com
Thanks
Chris
August 12, 2011 at 5:05 am
chris.mcgowan (8/12/2011)
The only thing i would say make sure of is that you verify your backups. If you get corruption on one of your database on your primary server then this will be log shipped to your secondary server. This can happen at any point and you won't know about it until the page(s) that are corrupt are read from or written too.
Corruption doesn't get logged, so except for some really odd cases it can't go across to the secondary via the log backups.
If the OP restores a full or diff that's already corrupt to initialise the secondary, then the that will also be corrupt.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2011 at 5:22 am
GilaMonster (8/12/2011)
chris.mcgowan (8/12/2011)
The only thing i would say make sure of is that you verify your backups. If you get corruption on one of your database on your primary server then this will be log shipped to your secondary server. This can happen at any point and you won't know about it until the page(s) that are corrupt are read from or written too.Corruption doesn't get logged, so except for some really odd cases it can't go across to the secondary via the log backups.
If the OP restores a full or diff that's already corrupt to initialise the secondary, then the that will also be corrupt.
The majority of corruption would be caused by a hardware fault in which case i agree you are correct that wouldn't get logged. I have however had an occurence in SQL Server 2005 where I had corruption which existed on both my primary and secondary servers.
August 12, 2011 at 5:39 am
Via the full/diff perhaps? It's possible for corruption to get into the log and hence into the log backup, but it requires bulk-logged recovery and an odd chain of events.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2011 at 6:17 am
Hi Both,
I have seen corruption only from within the transaction logs because of either timeout issues to SAN or resource hogging and bad writing again on the SAN. However this does and will stop the Log Shipping Secondary server from staying in a Standby/Read-Only state and will caus problems. Hence why one of my other previous posts dealt with identifying how to over come this issue, which resulted in me upping the SESSIONTIMEOUT variable in the windows registry. There are articles on the internet detailing how to do it and it is fairly straight forward. However this is far from the issue now. My environment is fairly redundant however it is not as resilient as I would like. It will not take a hammering if something like the network switch drops that the SAN is connected to, or allow me to backup from the Secondary Database so that I can get a daily backup of the database from the secondary database if I need to. I only want this extra option which seems a little far fetched at the moment because I have experienced the Network outage and overnight backup problem where I have lost a day's data because of issues. It all boils down to upgrading the infrastructure piece by piece so I can have this robust resilient architecture.
Could you hazard a guess as to how long it would take to bring a database online with a transaction log and then move the database forward again in an Standy/Read-Only state so that the log shipping can continue, anyway, I think you can only achieve that through the GUI as I have only been successful thus far using the GUI which isn't very good if you want to automate the process.
I think I am going to head off to the drawing board... let me know if you have any other suggestions?
Thanks
August 14, 2011 at 6:23 am
chris.mcgowan (8/12/2011)
To make the secondary log shipping instance 'online' and accesible you need to restore a transaction log with recovery.
you do not need to specifically restore a transaction log, the following will bring the standby database online
RESTORE DATABASE MyDB WITH RECOVERY
nathanr 81822 (8/12/2011)
It will not take a hammering if something like the network switch drops that the SAN is connected to
I\O Multi pathing protects against exactly this scenario.
Probably your best way forward would be to verify backups to ensure integrity and increase the disk space. May be worth looking at a 3rd party tool such as SQLBackup or Litespeed. You could also mirror the full backups to a separate location for extra protection\redundancy
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply