August 8, 2016 at 3:22 am
Hi I am in the middle of moving our Sage DB to a new server.
To minimise downtime in the day I plan to let the normal 2am full backup run and then do a diff backup in the maintenance window - are these the correct steps to restore a diff backup to the new server?
Restore Full backup
Open the Restore Database window in Microsoft SQL Server Management Studio
Ensure the To database field is filled in with the name you want.
Choose From device as the Source for restore.
Choose the full backup file you want to restore. In most cases this is the most recent full backup file.
Click the Options page on the left navigation.
Choose Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY). This is the most important step!!!
Restore Differential backup
Open the Restore Database window in Microsoft SQL Server Management Studio
Ensure the To database field is filled in with the name you want. The same that you specified in step 2 for the Restore Full backup
Choose From device as the Source for restore.
Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.
Click the Options page on the left navigation.
Choose the default: Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY) Make sure to choose this if you want to use your database after the restore.
August 8, 2016 at 5:06 pm
No one can really say at this point without knowing if data loss is allowed and what the recovery model is for the database.
In general though, you want to restore everything with norecovery until the final file is restored. With norecovery allows you to restore additional files. The final restore is usually the last log though which is why I asked about data loss and recovery model. In your scenario it looks like you will lose any data after the last differential which may be okay with the business.
I wouldn't do this through the GUI though - a script for the restore process isn't very complex, less prone to errors (you aren't remembering which checkbox where on which screen), tends to be a more repeatable process, can be documented much more easily just in the script itself, can be used with source control, etc etc etc etc.
Sue
August 9, 2016 at 2:53 am
Hi,
The recovery mode is Full.
During the maintenance window I will restrict access to just me so the diff backup will be correct?
August 9, 2016 at 10:22 am
Probably but you would need to last log backup to ensure this.
You can restore the full backup onto the new server anytime after it completes. Restore with no recovery.
You can take the differential and restore that when the maintenance window starts or a bit before. Restore with no recovery.
The last thing to is then backup the log and then restore it on the new server with recovery.
You would probably want to time this with whatever applications are hitting the database and coordinate when those change to point to the new server. And if you haven't done a move like this, you would want to practice it and pay attention to the time it takes for the restore process. And if you practiced it in advance, you could use scripts to do it and not worry about following screen shots of doing it through the GUI.
Sue
August 9, 2016 at 10:25 am
Hi,
Actually I'm probably just going to do a Full DB backup with everyone out - will that be enough or does it need a log backup?
August 9, 2016 at 10:28 am
Will it be easier to switch the mode to Simple before the migration?
August 9, 2016 at 10:46 am
I wouldn't as the time you save with one last log backup and restore you would spend making sure you have reset the database recovery model back to full after migration.
You have a pretty safe scenario with the migration for "practicing" a live recovery. If you were in a crisis situation, you wouldn't be able to change the database recovery model to try to simplify things as it just doesn't work that way. When you go through a migration and restoring things onto the new server, you'll gain more confidence for when you hit a problem and have to restore live production databases. I would use the migration to your advantage to get the real practice.
Keep in mind too that on occasion, migrations can have weird things happen and they go wrong and you have to go back to having everything on their original servers. So you generally want to keep things "as is" in case you have to fall back to the original production setup.
It can be a good reference as well to go back and check the original server - someone always complains that xyz worked fine on the old server. And they usually have a typo or something that actually didn't work right on the old server.
Sue
August 9, 2016 at 10:50 am
Hi,
That's ok then.
So I need to do a full backup
Then a log backup
And restore both in that order?
s.
August 9, 2016 at 11:02 am
A diff is used to save time. If you can do a full + log in your maintenance window, I diff likely doesn't help. Usually we do a diff if we're restoring the full outside the maintenance window and then trying to shrink the maintenance window with a diff and smaller restore.
I usually restore everything with norecovery, even the last log. When I'm sure I have everything, run
restore database mydatabase with recovery
to make it useable. If you're concerned, restore with the full and log WITH STANDBY and you can query the db to see if you've got everything you need. One way to do this is add a new table on the old server just before the full or log backup. Make sure this exists prior to running with recovery. Or go look for missing log backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 9, 2016 at 12:33 pm
Steve has some great advice on that. Differentials are often used to manage the size issues and can provide quicker restores than restoring a lot of logs. I've used them on some migrations for databases that are hundreds of GBs or larger in size that do frequent log backups - I can get a diff in there that is relatively small and saves a bit of time on the migration process. And then just backup the log at the end to get everything. But if the log backup is going to be small or if you won't have a ton of logs to restore since the last full backup, you can often just do the full and the log. It really depends the volume and type of activity you have in the database.
A couple of other things to keep in mind for your process - if you have agent jobs running, especially backups, do you have any of those that are going to interfere with your process? If you start your migration process and a log backup is run from Agent, that can add a twist to how you are doing things. I usually disable anything that hits that database at some point. Disable rather than delete since you want to be in a position to fail back to the original server if needed.
If you are going to use the GUI for the process and are planning on setting the database to single user, you'll need to be careful of any other connections to the database, including your own in object explorer or a query window. The advantage there with scripting it all again - you can set the database to single user in the script and then just keep running your stuff in that same script that is the connection to the database.
Sue
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply