|
How to automate a refresh of SQL Server
database without costly software:
The idea is very simple using the principles
of mirroring or Log-shipping.
First, robocopy all the required backups to where
destination database has access.
Second, use an automated script to generate a
restore recovery script from the source database with the required changes
for destination database.
Third, automaticly execute the timely steps and
reset users and some parameters after refresh.
1. source/production database is on full
recovery mode with transaction log backup.
2. (A), (B) below, continue copy full/diff/T-log
backup to destination server. I used Winrar command line to
compress and Robocopy to binary-copy.
3. (C) below, run the script against production database
to generate a restore/recover script for the QA database.
4. (D) below, run the
ClearSession script to kill all connected sessions. (only needed
if there are sessions still connected on the to-be-restored database)
5. (D)
below, run the generated script to restore/recover QA database.
6. (D) below, reset some
parameters such as full to simple and some custom parameters by
update.
7. (E) cleanup copies.
Schedule the following:
(A) MH_Test.bat
(B)
MH_TestTrans.bat
(C) MH_TestRefreshScript.bat
(D)
ExecuteMH_TestRestoreRecovery.bat
(E)
BackupCleanup.cmd
Here is the code to donwload.
|