I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) appended with the previous month name. For example, on April 1st, I create a DB called "mydatabase_march2021" which is a copy of "mydatabase" current as of April 1st at 12:01am.
My current process involves removing the DB from the AG on the 3rd node promptly at 12:01am, renaming it, and then starting the slow process of backing up and restoring the 5TB DB to the 3rd node and adding it to the AG. Is there some other way I can do this without having to fully reinitialize the DB on the 3rd node? I need to have this copy available to business users by 5am.
I have tried removing the DB from the AG, offlining SQL, making a copy of the DB files, but there is no way to attach those files and keep the DB in recovering (which is necessary to reinitialize).
Additional Information:
Windows Server 2016 Standard
SQL Server 2012 SP4 Enterprise (will be upgrading to SQL 2016 or 2019 at some point in the next year)
Node 1 is in datacenter A
Node 2 and Node 3 are in datacenter B
Best Answer
"I could use a tranlog backup, and then roll it to 12:01am"
Do that. You can restore the last full backup, then apply log backups with NORECOVERY in the days or hours before the first of the month, then apply the last log backup with STOPAT and RECOVERY or STANDBY.
Or you could create a Database Snapshot at midnight on the first of the month. That would avoid copying all 5TB at the beginning, and the snapshot will only store pages that change over the course of the month.