July 1, 2010 at 10:43 am
We currently use our Standby Database as a source for a feed into our Datawarehouse which is on another database platform.
The Database is in Standby mode as we receive daily T-logs that are cut from a third party which we apply to our Standby Database on a daily basis. As the T-log is applied we perform the data extract into our datawarehouse. In other words, the Database will always have to be in Standby Mode as the T-logs are applied in a daily basis.
The way we are doing our so called Standby DB backups is as follows.
-Stop SQL Server
-copy the .mdf, .ldf, and undo*.bak files to another directory (done on a daily basis)
-Start SQL Server
If we are required to go back a few days, we simply copy back the mdf, ldf and undo*.bak files back into the original directory and apply the T-logs from that day forward.
Aside from this method, is there any other alternative in regards to backing it up?
July 2, 2010 at 6:36 am
Since the Standby server is just holding a copy of the production backups, I'm not sure why you'd want to also backup the Standby server. At that point you're creating a fourth copy of production. 1) Production itself, 2) An appropriate backup and log backup, 3) A standby server restored from production & the log. Making a fourth copy just seems like extra work. I'm as paranoid as the next dba, but this feels like overkill.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2010 at 6:38 am
Although, thinking about it, if the database is in Standby mode, you should be able to run a backup and use the COPY_ONLY option to avoid breaking the log chain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2010 at 7:27 am
Thanks for the reply.
We don't have access to the Production Database that is cutting the T-logs. The production database is with a third party. They simply provide us with the T-logs that we apply to our Standby Database.
The issue again is, if we I've applied T-logs for (Eg.) For Jun 1, Jun 2, Jun 3 and I want to back track to Jun 1. The only option I have is to copy back the MDF and LDF backup files from Jun 1 back into the Database directory and then proceed with applying Jun 2, Jun 3 etc etc again.
Will the copy_only option work with Standby Databases?
July 2, 2010 at 7:40 am
saad_Ahmad1 (7/2/2010)
Thanks for the reply.We don't have access to the Production Database that is cutting the T-logs. The production database is with a third party. They simply provide us with the T-logs that we apply to our Standby Database.
The issue again is, if we I've applied T-logs for (Eg.) For Jun 1, Jun 2, Jun 3 and I want to back track to Jun 1. The only option I have is to copy back the MDF and LDF backup files from Jun 1 back into the Database directory and then proceed with applying Jun 2, Jun 3 etc etc again.
Will the copy_only option work with Standby Databases?
I have not tested it, but yeah, I think it will and if it does, it won't interfere with the transaction log chain, which is the whole point.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2010 at 8:15 am
Just tried it, it isn't possible to back up a database that's in standby mode using the COPY_ONLY option. Given your situation, looks like you're already doing the only thing possible to 'back up' your standby database.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 2, 2010 at 8:35 am
Really? Huh. I haven't had the chance to test it, but I would have sworn it would work. Ah well, not first time I've been wrong. Sorry for offering hope.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply