March 31, 2016 at 8:20 am
All, I need to restore the DB which is close to 1.5 TB from Prod to QA. It has close to 1200 logical file. This is what I have tried so far. Restore the DB through GUI and script it out and run it in QA environment but it times out every time I select the restore file and script it out. I also need to apply all the transactional log file. So Full backup from Saturday, Diff backup from last night and all the Tlogs. Any help will be highly appreciated.
March 31, 2016 at 8:39 am
You can use RESTORE FILELISTONLY with the path to the backup file and then use the information returned to build your restore script using the WITH MOVE options if the files are in different physical locations on the QA server.
March 31, 2016 at 8:43 am
This is the third time I have mentioned this this week, but you must make sure your non-production environments have Instant File Initialization enabled. Otherwise your restore will zero out every bit of every byte of the entire file size of your restore BEFORE IT ACTUALLY STARTS THE RESTORE!! I have seen multi-day restores drop to just a few hours with this issue corrected.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2016 at 8:45 am
1200 logical files? That is a lot!
I recommend using this opportunity as a test case to check how long the restore will take. Then compare that with the recovery time objective (RTO) that is included in the SLA. If the production database goes down, are you even able to meet that RTO? If not, you will either have to renegotiate the SLA or change the backup/restore strategy to ensure that restores can be performed fast enough.
March 31, 2016 at 8:58 am
TheSQLGuru (3/31/2016)
This is the third time I have mentioned this this week, but you must make sure your non-production environments have Instant File Initialization enabled. Otherwise your restore will zero out every bit of every byte of the entire file size of your restore BEFORE IT ACTUALLY STARTS THE RESTORE!! I have seen multi-day restores drop to just a few hours with this issue corrected.
Instant File Initialization is already enabled. It is part of the company standard the way we set up SQL Server environment.
March 31, 2016 at 9:00 am
Hugo Kornelis (3/31/2016)
1200 logical files? That is a lot!I recommend using this opportunity as a test case to check how long the restore will take. Then compare that with the recovery time objective (RTO) that is included in the SLA. If the production database goes down, are you even able to meet that RTO? If not, you will either have to renegotiate the SLA or change the backup/restore strategy to ensure that restores can be performed fast enough.
We have HA in place so just in case if this Prod goes down.
March 31, 2016 at 9:07 am
Chitown (3/31/2016)
Hugo Kornelis (3/31/2016)
1200 logical files? That is a lot!I recommend using this opportunity as a test case to check how long the restore will take. Then compare that with the recovery time objective (RTO) that is included in the SLA. If the production database goes down, are you even able to meet that RTO? If not, you will either have to renegotiate the SLA or change the backup/restore strategy to ensure that restores can be performed fast enough.
We have HA in place so just in case if this Prod goes down.
That's a good start.
Now what if someone accidentally forgets to add a WHERE clause to "DELETE FROM OrderData"?
March 31, 2016 at 10:11 am
That's a good start.
Now what if someone accidentally forgets to add a WHERE clause to "DELETE FROM OrderData"?
I will personally kick his/her butt :-). Since it's prod, no one is allowed to make any changes in the DB. Only DBAs run script to make any changes in the DB. But of course we have to look at the script before we run it. Like you mentioned We have to look for stuff like "Delete something without the where clause". But it usually NEVER happens.
March 31, 2016 at 11:35 am
Chitown (3/31/2016)
That's a good start.
Now what if someone accidentally forgets to add a WHERE clause to "DELETE FROM OrderData"?
I will personally kick his/her butt :-). Since it's prod, no one is allowed to make any changes in the DB. Only DBAs run script to make any changes in the DB. But of course we have to look at the script before we run it. Like you mentioned We have to look for stuff like "Delete something without the where clause". But it usually NEVER happens.
You do realize that the word USUALLY here is the crux of the issue, right: "... it usually NEVER happens." There is absolutely NO WAY you cannot GUARANTEE that someone doesn't screw up the data. And WHEN that happens you had better be able to recover within your SLA requirements or you can literally wind up (or be sued into) going out of business. And yes, I HAVE seen that happen in real life.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2016 at 3:22 pm
It can even be as subtle as accidentally highlihgting part of a script when executing from SSMS. I have not only seen that happen, I have personally done that. (Luckily never with critical data --- yet)
Also, allow me to reverse the question - if your restore strategy is to use your HA and you have procedures in place that are supposed to prevent any data loss by human error, why are you even making backups?
I know this sounds weird, but if you never ever plan to use them, why make them?
(And now figure out how to explain to upper management that you don't need backups to meet your SLA...)
NOTE: The above is deliberately argumentative. Of course you need backups and I would never seriously consider not making them. Because no matter how good your HA solution is, you can always run in a scenario where you need them.
But I did this to drive home my point - that if you take your backups seriously, then you should also take the restore seriously, which means that you need to make sure that you can restore within the RTO.
March 31, 2016 at 3:24 pm
March 31, 2016 at 4:34 pm
Use Backup file Striping with the methods described in the below blog. I could reduce to backup times from few hours to with in an hour for large databases.
March 31, 2016 at 4:36 pm
I didn't see backup compression mentioned before, assuming you're already using it.
March 31, 2016 at 6:45 pm
Chitown (3/31/2016)
I will personally kick his/her butt .
BWAAA_HAAAA-HAAAA-HAAAA!!! Finally! A true kindred spirit! 😛 You made my day!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2016 at 2:16 am
I'd also be kicking the butt of whoever accepted this into service without doing any type of BC/DR testing (including restoring the database from a full backup). And 1200 logical files? Was that by design?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply