November 17, 2015 at 2:47 am
Good morning Experts,
The productio database is 1 TB in size. I want to keep a copy of prod database in UAT. It should be synchronized weekly basis. Backup/Restore will take lot of time.Could you please let me know the solution.
November 17, 2015 at 2:53 am
Backup/restore will the the easiest and probably the fastest.
You could buy a tool like Red Gate's SQLCompare and SQL DataCompare to sync the data and schema, but it's probably not going to be faster than a restore, unless only a small portion of the DB changes in a week.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2015 at 5:18 am
What kind of concurrency are you looking at? Can it be a day behind? Does it need to be instantly (or near instantly) current?
What kind of backups are you already taking of the database in production? Are the servers in the same data center / domain or will you be crossing domains / data centers to do restores?
November 17, 2015 at 10:19 am
Hi Brandie,
Once every 2 weeks, I want my UAT database to be same as prod database.The servers are in the same domain
November 17, 2015 at 10:31 am
If you only need to restore once every two weeks, then restoring off the production backups (as Gail suggested) is your best bet.
Create a T-SQL job that is scheduled to run in the evening or early in the morning every two weeks. It should check for the most recent FULL backup and restore it, then the most recent DIFFERENTIAL (if you use those), and all transaction logs after the last point of restore.
November 17, 2015 at 12:34 pm
If your network latency between prod and UAT is too high - the restore process could fail, not to mention the fact that you will be using the network on prod during this restore which may cause performance issues for your users.
There are ways to setup volumes on the SAN - depending on which SAN you are using - where you can split the volume and present that to a secondary server (UAT). You can then restore from a 'local' copy of the backup files to your UAT environment.
Once the restore is completed you re-establish the mirrored volume on the SAN and let it synchronize.
This process eliminates the requirements to copy a backup file across the network or to restore the database across the network - both of which will take a long time for a 1TB database.
If your only option is to copy the backup file - make sure the file is compressed before copying to the UAT server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 18, 2015 at 2:53 am
If a SAN snapshots aren't in the budget and compression isn't good enough (or not possible), read http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx.
November 18, 2015 at 3:28 am
Striping the backup to multiple backup files can help you reduce the backup/restore time.
I've also found that writing the backup files to a local drive and not over the network, and then using robocopy to copy the files to a local drive on the restore server helps to speed things up too.
Of course, if you are already taking log backups and/or differential backups on the production server you may also be able to use these files to reduce the time taken for the synchronization.
November 18, 2015 at 4:26 am
tripleAxe (11/18/2015)
Striping the backup to multiple backup files can help you reduce the backup/restore time.
In my experience, I have seen no difference between restoring striped files and a single file. Copying them to another environment may be faster, but not the restore itself.
November 18, 2015 at 4:59 am
In my experience backup time was significantly reduced. I had a 1TB database that was taking 5 hours to backup. Changed the backup to stripe to 8 files and the backup time reduced to 1 and a half hours. I can't remember what difference it made to the restore time. This was quite a few years ago.
November 18, 2015 at 5:02 am
Brandie Tarvin (11/18/2015)
tripleAxe (11/18/2015)
Striping the backup to multiple backup files can help you reduce the backup/restore time.In my experience, I have seen no difference between restoring striped files and a single file.
It can help if the backup files are on different drives, because restore, like backup, is an IO-bound operation. If they're all on the same drive, not so much of an advantage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2015 at 5:31 am
GilaMonster (11/18/2015)
Brandie Tarvin (11/18/2015)
tripleAxe (11/18/2015)
Striping the backup to multiple backup files can help you reduce the backup/restore time.In my experience, I have seen no difference between restoring striped files and a single file.
It can help if the backup files are on different drives, because restore, like backup, is an IO-bound operation. If they're all on the same drive, not so much of an advantage.
And all the ones I've seen, in several places I've worked, have put the stripes all on the same drive, which explains it.
November 18, 2015 at 5:45 am
Brandie Tarvin (11/18/2015)
GilaMonster (11/18/2015)
Brandie Tarvin (11/18/2015)
tripleAxe (11/18/2015)
Striping the backup to multiple backup files can help you reduce the backup/restore time.In my experience, I have seen no difference between restoring striped files and a single file.
It can help if the backup files are on different drives, because restore, like backup, is an IO-bound operation. If they're all on the same drive, not so much of an advantage.
And all the ones I've seen, in several places I've worked, have put the stripes all on the same drive, which explains it.
That can help too, as you get more threads reading the backup files and more memory buffers, but backup/restore is usually IO-bound rather than memory or CPU.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2015 at 6:01 am
Thanks to each one of you for providing suggestions and advise.
November 18, 2015 at 6:54 am
GilaMonster (11/17/2015)
Backup/restore will the the easiest and probably the fastest.You could buy a tool like Red Gate's SQLCompare and SQL DataCompare to sync the data and schema, but it's probably not going to be faster than a restore, unless only a small portion of the DB changes in a week.
You know I love Redgate, but I wouldn't recommend this approach. It won't work as well as backup & restore, not by a long shot.
We are working on a new tool that will let you do a near instantaneous restore of a database, and this would be a great use case for it. But it's not complete yet. Keep an eye on Red-gate.com for news. It should be out soon.
"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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply