March 11, 2016 at 2:30 am
Scenario:
2 backup strategy-
QA: weekly full, daily differential and hourly T log backup
Prod: weekly full backup, nightly full copy_only, and hourly transaction logs
Need the database as-is on server2 for further processing used by other project
Problem: to build code and test for other project dev team needs same backup strategy on QA and Prod. DBA's take: not possible for various reasons
What can be probable solution so that dev teams build one solution used in qa and prod.
Snapshot backup? copy_only backup ?
SQL Server 2014 and DB on 2012 compatability
March 11, 2016 at 2:35 am
Prod: weekly full backup, nightly full copy_only, and hourly transaction logs
Can I ask- why are you planning to take copy-only backups in Production if you're not taking any differential backups anyway?
Also, are you aware that if you need to do a restore you could have a LOT of t-log backups to restore. Why not take a nightly full backup rather than a weekly one?
March 11, 2016 at 2:46 am
Full_backup with copy_only is required for some other purpose.
and as for weekly full backup rather than daily, there are few overheads like space (we have 8 diff QA env, with 20db all in gb's,) and most important these backups are downloaded everyday by various teams . we don't want network to be clogged
March 11, 2016 at 3:28 am
Why not just take a full backup for that other purpose, though?
March 11, 2016 at 6:15 am
Agreed. COPY_ONLY is of use with DIFFERENTIAL backups. If you're not using DIFFERENTIAL, then there's no need to use COPY_ONLY. It won't do anything for you.
Instead, the QA process makes more sense in production. Weekly FULL, nightly DIFFERENTIAL, and logs (although hourly means that you're ready to accept up to one our of data loss, is that really the Recovery Point Objective [/url](RPO) agreed to by the business?).
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply