SQL Server Backup – Full Backup Plus Differential/Copy-Only

backupsql serversql-server-2008-r2

We have a PROD full backups copied over to support envs daily and we restore to support environments to triage some issues, as the DB is growing big we would want to run the full backup copy only on the weekends. Daily backups are done as schedule but copying over to support env happens only on weekend. Copy-only or differential can we apply with one full backup ? or what is the best practice and effective way . 

Sunday :Full Backup taken 
Mon-Friday : Differential/Copy-only backups taken 
Saturday : Fullbackup + (Mon-Friday)Differential/Copy-only backups applied .

Will this work out? and one thing to remember we have daily full backups.

We do hourly log backups, and want to know the best practice for backup strategy and restore in support env

Best Answer

Since you're in full recovery and just need to keep the "support" DB up-to-date from production as-needed or once-a-week, then is log shipping not an acceptable option? You could have it restore once-a-day during off hours if it being in standby for the triage of issues would work. The transaction logs could copy over every hour, or whatever you feel is appropriate.

Otherwise, you may be able to setup a custom SQL Agent job (with TSQL scripting or maybe xp_cmdshell) to copy over the transaction logs from prod but have a disabled SQL Agent job (on support instance) to kick off ad hoc to as-needed to apply the copied over transaction logs to the support DB. This would allow you to have ad hoc log shipping essentially without standby assuming you setup the restores correctly to not be in standby.

The once-a-week backup file from prod could be copied over to the "support" server and overwrite or delete the existing file depending on naming conventions. You could then schedule that with SQL Agent job to do the once-a-week full backup and still let the transaction logs copy over without restore until you need to kick it off ad hoc as-needed.

You would setup a job to purge TRN files that are older than a certain time frame once the full backups complete so the old ones are deleted from the "support" server. This may be step 2 of your "support" full backup file restore and just plugin the age of files to purge as-needed. See below example and change arguments and variables as-needed per your requirements and test of course.

--This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'S:\MSSQL\Backup\TransactionLog\',N'trn', @DeleteDate,1
Related Question