Introduction to SQL Server Database Backup and Restore

hi my name is grant Fritsche I'm the scary DBA I work for red gate software I'm here to talk to you about backups backups and restores we're going to take a really basic walk through and hit some of the highlights of the best practices that you're going to use when running a backup and restore the thing is you need to think of backup and restore not as protection for data not as protection for your systems but as protection for your business so the most important thing you can do for backups is talk to your business people to understand what their expectations are in terms of disaster recovery from you you also need to make sure that those expectations are realistic and ones that you can achieve that said one of the most important things you can do for your disaster recovery scenarios is set up backup processing so what we're going to do today is I'm going to walk you through a full backup and I'm going to highlight some of the things that I like to do in a full backup how I like to do them I'm going to show you today just gooeys we're not we're only going to use the gooeys we're not going to automate anything today automation is a key but we won't be covering it I'm also going to show you restores and restore verification and why you need to do that stuff and some of the details around that I'm also going to cover log backups and recovery to a point in time using logs so we'll cover just the basics of backups and restores the thing is I want you guys to practice this stuff you need to know how to do it you need to practice it so let's get started right now with the task of backing up a database here it is you've got a basic backup screen you're ready to go you get to pick and choose which database you're going to do the recovery model on the database in this case is simple we'll change that in a little bit it only gives us two choices full and differential differentials or for another day copy onlys for another day yes we're going to backup the database we're not backing up files and file groups the name of the database believe it or not I'm sorry the name of the backup believe it or not this is important backup files can get moved around they can get rearranged they can get changed they're going to be here they're going to be there you're going to have just they're going to be all over the place if you name them you know my special backup for video now you know what it is I've created this backup just for you for the video of backups and restores never type during demos what I've got now is a name in a description so I know when I find this backup at some later date I know what what it is what it's for if there's anything interesting about it if there was some reason I did it separate from my regular backup process or if it was part of my regular backup process I can name it you know regular daily backup and then I know that this is part of my daily backup routine there's no surprises because I've got information stored with the backup we'll go back to typing during demos is bad special backup typing and talking is bad anyway you can set up expiration dates and stuff on video on backups I'm not going to get into that that's getting into some more advanced topics we don't want to do that but we do want to remove this existing file we're going to create a new file just for you guys you can use the GUI you click on the ellipsis you can pick and choose where you want to put stuff it's just going to be easier for me to provide this mm underscore video dot back it's just as quick just as easy to type depending on what you're typing so now we're set up for doing a full backup or are we there's a few other bits and pieces over here first up backup it to the existing media set now you can backup to an to mediaset every time give mediaset a name and description or you can back up to an existing media set if you back up to an existing media set i wrote i recommend overwriting the existing backup sets you can stack your backups inside of a single file I know people who do it there are good reasons why but there are also good reasons against my personal preferences for individual files but I'm not going to you know that like I said it's my personal preference you can make this go either way you like reliability this is one of your more important areas this is where you need to focus you need to verify your backups backups are important backups are extremely important backups are one of the most important things you do but your backups are only as good as your last restore Thank You Kimberly trip great saying you must verify that your backups are good that you can restore them otherwise it's simply a file that you're maintaining so one of the first steps you're going to do is verify the backup when it's finished this will run a verify only restore it's not actually a restore it's just a validation of the backup file itself it's a good thing to make that even better we're going to perform check sums the check sums are validate that the information read off the disk is the same as the information written to the backup media and those checks numbers are stored and used by the backup verification so all of this stuff is part of validating that the backup is good the backup is going to work and so you need to do this next compression I heartily heartily recommend you compress your backups because it makes them faster makes them faster to backup and faster restore because it's moving less information to the disk also by the way it saves you disk space and management loves that but don't worry about that the main thing is you're saving speed you're not saving speed you're gaining speed anyway now I've got back up set up and ready to go I'm going to overwrite my existing media I'm going to verify the backup I'm going to perform checksums it's going to a file that I want I've given it a name and a description and it's a full backup I'm ready to go click the ok button backup is completed now my database is protected I'm ready for any kind of an emergency let's create an emergency let's say that we know we've done something bad and we need to restore this database I'm going to right click on it again go back to tasks restore database restore is a little bit different than your regular backups first off you can pick and choose where you're going to restore it to and you can type into this and create a new database from a restore most recent possible we'll be talking about that more in a little bit now you can restore from from the database or from a device if you're restoring from a database what it is is the information that's been stored about the most recent backups for that database are all kept available in ms DB so you can take a look at them and understand what's going on with the backup and you can run things that way there's a lot of good information in here this is stuff more or less from the backup header includes the log sequence numbers if you're using the stacked backup approach where there's multiple backup sets in a single piece of media you'll show you the position of the backup all that fun stuff the start date and the finish date so you know how long your backups took this one took all of one second that's a tough long backup anyway all this information is available to you from the backup file from the from the backup but let's go to the media directly let's assume that we are not restoring from some other location we're restoring from some other server and we go in here and we'll find our mm a video back ok now the interesting thing is is the name the name of the backup is there I can see the names I supply the special name I supplied so I know what this backup is again the ls ends and all this other stuff because all this information is coming from the back up header and so now I'm almost ready to run a backup but not quite first off am i overriding the existing database well yes I am I want to overwrite the existing database if I didn't want to overwrite the existing database I could create a new database in which case I would have to move the physical files which are located here I would have to give them a new location because the existing database is using them and so we'd have to change that if I'm doing multiple files I can prompt if I want to restrict access I can but the really important part of the back of the restore operations down here restore with recovery versus restore with no recovery if you're doing differential restores after a full restore if you're doing log restores after a full restore you cannot leave your database in a recovered State you need to leave it in a non recovered State ready for more backups to be applied a lot of people will run their backups in a no recovery state until they're sure that they've got everything restored the way they want it and then they'll recover frequently people forget to recover and then they wonder why the database is still in a restoring State make sure you recover your databases all the way through I'm going to go and leave it in place for this one and we're going to run the restore operation you can see the execution shows how fast things are going by and the restore is done and now we've got our database back in place now let's talk about log backups and log restores first off I need to change my database my database is currently in simple recovery mode because it's in simple recovery mode I can't even take log backups so I'm going to switch it over to the full recovery mode now from this point forward any transactions are recorded into log and until I run a log backup those things are kept around forever so I probably should run a log backup soon but first because I switched the recovery from simple to full I must take an initial full backup I've changed the basic structure of the database in such a way that it's no longer in a state where I can restore it except restoring it to simple I couldn't simply run log backups because I need a full backup with transaction markers such that the log backups can work against it so I have to do a new backup so I'm going to call this my new full backup and done because I switched from simple to full recovery all right now I'm going to remove this I'm going to add a new one I'm going to call it D : backup I'm going to call it let's call it a mmm new full not back let's go in and check our options I still want to overwrite in case there's already one there called that I definitely want to verify my backups I've changed something in my production database I'm going to run a backup of it and I'm going to verify that backup I definitely still want to run checksums I definitely still want compression click OK and now I get it back up completed now then I can start working with logs log backups and transactions to see a transaction occur let's go and add some data now we're in the movie management database so we're going to insert into insert into agent let's give the agent a name we'll call him Herman Munster and he is agent to the strange and I had a I was running tests earlier so I've got this test column here I've been messing with and so now we've got a good back good insert statements ready to go so now we've added Herman he's in there he's now in our system and we can play around with it and do a select star from agent Porter by agent ID descending just to see Herman and there is Herman Munster agent to the strange so now we've got this in there it's a transaction that has occurred so let's do a log back up again we go to backup for the movie management database we're going to make sure we do a transaction log this time it's going to be a transaction log backup and this one's going to be called after Herman that's the name of our backup and we will need to remove this file we will need to add a new file we'll call it B back up and we'll call it Herman log that back ok so we know which one it is so that's our Harmon log it's after Herman and we should still perform check sums we absolutely should and we can use compression we absolutely can there's nothing wrong with this so click OK now we've backed up our log we've now got that transaction has been saved it's good to go if we need to do a recovery we can now let's add some more data let's add Frank N Furter probably didn't spell that right and he is agent to the terribly strange we'll do an insert and we'll just validate that the data is there and there it is Frank and further agent to the terribly strange and it's good to go that's another law another log entry another transaction so we can do another log backup let's go to the backups again we want to do a transaction log backup we're going to call this one after Frankfurter we don't want to use the Herman log unless you want to do an append which you can we're not going to in this case and we'll call it after Frank stop back again we go to the options again we make sure we do an overwrite again we want to perform a checksum on the log backups and again we want to compress our backups here okay now the log backup is done we've got log backup checksums we've got log backup compression we're all happy we're all good to go you know what I'm wondering if maybe having Frank and further in here is not inappropriate so maybe let's roll that back let's do a restore to a point in time prior to Frank and further being in there so let's run oops hang on first before we do that let's make sure nothing but nothing is in the movie management database because that causes all kinds of problems you can't restore database when there's people logged into it and working on it so here's our restore notice using from database it knows which backups we have because it's all distorted MS DB and it's tracked all this stuff and you can see the first LS n for each of these the last LS n for each of these and the way things are set up and so you can see the Ellison of course is log sequence number and so you can see the progression of the logs as they occur and and the things that have happened on the database over time and you can see how what stuff is all set up so if we wanted to remove her Franken furniture we could simply do this take that log back up off continue doing point in time most recent possible it'll be most recent possible after Herman though not the most recent possible after frankfurter let's take a look at the options here we do want to overwrite we are going to replace because we're letting sequel server manage all the files we're going to go ahead and finish up with recovery okay so let's click OK you see it progress that restored two things we click OK and let's take a look at our database let's run the Select statement oops my bad supposed to be in the right place let's run the Select statement and we see Herman is in there but Frankenberger is gone now let's put Franken further back in and then let's run another log backup after frankfurter again and we'll add a file de colon backslash backup x / after frank that log ok let's check the options let's overwrite the existing media let's perform the check sums and let's compress and click ok so there's the log backup now let's make sure that we're not in the database let's make sure we're not in the database now let's do a task restore database now this time you'll notice now we've got after Herman we've got after Frank Furter again let's take a look at the times now we've got it up to 9 37 and 38 seconds let's restore to a point in time let's restore to 9 36 9 36 and 10 seconds ok doesn't matter you know I mean but the idea is that we're restoring to a point in time between these two values ok the finished date is 31 and 9 31 finish dates 938 sequel server will figure out which of these it's needed and it will use them appropriately because you've got a point in time that you're going to stop the recovery at now let's make sure we overwrite we're going to replace our database we are going to finish with recovery sequel server will manage whether or not the recovery is going on during these different things and with that we'll click ok so now we've recovered to a point in time now let's just see if that point in time is prior to or after Frank Furter getting inserted ups again gotta go to the right database don't you execute and in fact that time was prior to when Frank and further was inserted so even though Frank refers that final log when we ran this restore operation it did not include that transaction because that transaction took place post the time that we were asking for so that is a point in time recovery so let me just tell you real quick what we went over again was full backups and full restores log backups and log restores we talked about the importance of checksum in order to validate your backups we talked about the importance of verify only as part of the backup process the full backup process as a way to validate your backups and we talked about the most important mechanism for validating your backups is what we just did which is a restore finally if you follow it along with any of this at all or if you use this to practice later practicing your restores is one of the most important things you can do that's all I've got thank you very much this is grant Fritchie I work for red gate software I am the scary DBA

Channel: Grant Fritchey

Views: 215,468

Keywords: Microsoft SQL Server, Database, Backup, Restore, Accidental DBA, Red Gate Software, data platform, microsoft data platform, introduction sqlserver database backup restore, grant fritchey, grantfritchey, recovery, gui

Id: pA242aMvz6E

Length: 22min 5sec (1325 seconds)

Published: Tue Feb 21 2012