Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the reasons I like to get into the habit of backing up regularly even when I'm just working with sample databases on a development server is that it's way too easy to run a little bit a code that maybe does a little bit more than you expected. So I'm pasting in a few delete statements here. This is not the kind of code you want to execute a lot. Well this is going to do with the no WHERE statements is it's going to go through and delete everything in all of those tables. So say this accidentally happened yesterday, a few hours ago, a few seconds ago.
And I'm now in the problem that when I go and look at my Customer table, for example, I have nothing whatsoever and this is not what I want. Well luckily I was doing regular backups so I can restore these. I'm going to do this from SQL Server Management Studio, though you can do it from the SQL command line. I'll right-click one of the databases again it doesn't matter. Even if the database wasn't showing up, you can still pick any of them. Go into Tasks and then Restore. We're going to restore the database.
And again it gives you the drop-down. Is this the one that you want? Yes it is. So a point in time most recent possibly? Yeah absolutely. If you had a specific date and times in a long chain of backups, you can pick it from a particular place. We don't need to here. And because I've been using SQL Server management studio to do my backups, it's taken a look and says okay I have the available files. I know the backup set. I'm ready to go. What else would you like to do? I do have a few options. I'm going to leave everything as is. There are some options you might want to look at if your situation is a bit more unusual, because there's what's called a recovery state.
What state do we want the database to be in when it brings up? Perhaps you're trying to recover a state of the database where you're just interested in getting it as accurate as possible, but as far as you're concerned it could be in read only mode. Well that's not usual. I want to have this database ready to go and ready to use. I'm going to leave all the options there. I'm going to click OK. It's throwing me up an error that exclusive access could not be obtained because the database is in use. Well we've got a couple of things we can try there.
It's probably just a little bit more concerned with the fact that I only just did those DELETEs and it's hanging on to some locks here, so I'm just going to reconnect to the instance. Go back in and try to select to restore the database again, leaving all the default options, and then click OK. And this time we're looking good. Of course, the proof is can we go back into that database and find to say the customer information? And yes, we can.
Now similar to working with your incremental and your full backups, if you are working with a restore, that can be scripted if you need to. Hopefully you are not having to do a restore of your database often enough that you actually need to have a permanent script file for doing it. And certainly if your database is in a more complex situation, if you're doing not just the regular full and differential backups, but you have split your backups into the more granular pieces of file groups and transaction logs, you are going to want to take a little bit more care than just the quick way that we've done it.
But certainly when working with a development server, like we're doing right now, this should be a perfectly and acceptable way to do both your backups and your restores.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91133 Viewers
80 Video lessons · 138168 Viewers
59 Video lessons · 56986 Viewers
52 Video lessons · 70622 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.