What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

"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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply