SQL Server DBA Tutorial 78-How to Restore a Database From Full Backup in SQL Server

welcome to tech brothers in this video we're going to learn how to restore a database from full backup in sequel server we'll be using two method to do that one is using sequence every management studio graphic user interface and other is using T sequel script so let's go ahead and do that we're gonna go ahead and use sequence of your management studio graphic user interface here's my sequel server and sequel prod is my instance and these are the databases and the database that I want to restore from my full backup keep in mind that you have to have a full backup to restore the database so that's probably obvious so I'm going to go ahead and restore sales orders database from its full backup so I'm going to go ahead and right click on the database go to tasks go to restore and click on option database because we're going to basically restore the database not files and file group or page you have an option to restore your file and file groups or page page is 2012 and 2014 a new addition to the restore operation in sequel server so we're gonna go ahead and click on database and up here we have a database name and we're going to go ahead and click on device and when you click on device that means that you need to provide the backup full backup file to this database so we're gonna go ahead and click on this square and find add the file with our full backup is right now right here is my full backup so I'm going to go ahead and select that click OK and as you can see that database up here is sales order you have an option to rename the database right here the destination database that if you wanted to do like restore as so if you click on restore s then that means that you're going to if you rename the database right here that means you're using s restore option as option so we're going to go ahead and keep it this way because this is the database we intend to restore from it's for backup something happened five minutes ago and we had the backup full backup prior to five minutes so we're gonna go ahead and restore the database from there so let's go through the quickly up here if you would like to keep these files move these files up here restore as option if you click up here if you go all the way right now is going to be sales orders MDF and sales order LDF and if you don't have a restore as and you would like to do just exactly the default values it's going to overwrite sales order MDF and LDF so if you are doing restore as I would recommend you to go ahead and do the sales order underscore whatever the name you gave underscore test or whatever so we're gonna go ahead and that do that if we're going to restore as a different name of the database so up here you can relocate the files to the folder to the new location if you wanted to do that if you don't want to put if you wanted to keep the same names of MDF and ldf but provide a different location otherwise it will give you an error saying that this file already exists if you use restore as option so we're gonna go ahead and click on the options right here we have an option that you wanted to go ahead and overwrite the existing database if you do that it is going to overwrite and all the database that you have up here is going to get lost and only data that you will have is from full backup and up here preserved application setting with the keep replication if this database is being replicated going to preserve that application settings for you and once the database is restored your replication status will remain will replication setting will remain same restrict access up after you restore if you wanted to restrict user for from accessing this database you have an option and up here is the recovery option restore with the recovery in this particular video we're going to restore from full backup and bring the database online however you do have options right here if you wanted to go ahead and restore your differential and your transactional log back up then you need to basically choose this option restore with no recovery that will keep the database in restore mode and you can go ahead and restore differential and transactional log backup but transaction log backup do does require you know no recovery mode but the differential you don't have to but anyhow you have options right here you can choose that and up here this is new that is going to take tale log backup if you don't know what a log backup is please watch my video so it's going to go ahead and take backups for you for the tale log just in case that you're wondering that if you're going to restore other transaction logs and after that on all the uncommitted transaction that wasn't part of the last transaction log backup that you had will be taken in ten log and you can go ahead and restore your tale log in the end and it'll come online and your data loss will be minimal so this option right here server connection this is again better new in 2012 and 2014 I don't remember seeing it in 2008 but close existing connection to the destination database if you don't have this option checked if your database is in use it is going to give you error that database is in use and I'm going to perform that demo real quick for that so here's my database I'm going to make this database in use so I'm going to do bring begin Tran select a straight from customers so I'm going to run these commands and this will leave our transaction open in this database alright so the next option is that we are not going to click this closed existing connection and try to restore a database and see if we get the click okay as you can see we got the error and the error is saying that database is in use right here so we're going to go ahead and click okay and and we're going to click on close existing connection any connection to the database I'm going to go ahead and commit this transaction this will still keep the database in use so let's look at that if our database is still in use sales osa sales order right here is runnable it's still in use so we're going to go ahead and click on this option close existing connection to the destination after this if you click that what it back behind the scene it's doing is bringing the database in single mode and then restore it and bring it back in sick a multi user single user animal two users back behind the scene so I'm going to go ahead and click on general and click OK to restore our full backup all right as you can see the database sales order restored successfully so what we learned that if we click on the close transaction all connections all the connection to the database if we close that it's going to kill these connections and bring that database actually in single user mode and restore and then go ahead and put the database in multi user which you will see in a second from transactional sequel server we're gonna go ahead and use T sequel now to do the same thing so I have already the transactional sequel right here so we're gonna go ahead restore full backup as you can see let's look at a couple options right here alter database sales order sets underscore single with rollback immediate that is going to set the sequel's this particular database into single user mode that'll kill all the connection and only connection that will be available is your connection and up here that tail log backup as I said that it will take the tail log backup just in case if you wanted to restore your transactions later right here is the main command restore database and this is the main up here the location where your backup file is and as you can see the stats up here is five it's gonna the completion status would be 5 percent 10 percent 15 percent so it will be incremental fire so after that it's done alter database sales set multi-user so what I was talking about earlier using graphic user interface this is the this is using T sequel script so we're going to go ahead and click on execute and this should restore our database using T sequel as you can see right here the restore database completed successfully so this is basically how you restore your database using sequel server management studio your full backup restore database using full backup and restore your database using full backup using T sequel script and I hope this video helps

Channel: TechBrothersIT

Views: 36,395

Rating: 4.7647057 out of 5

Keywords: Restore, Database, Full, Backup, Restore a database From Full Backup in SQL Server, SQL, Server, Developer, TSQL, DBA, Interview, Scripts, DBA Tips, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, SQL Server 2014, SQL SErver Adavance Tutorial, SQL Server Tutorial for beginners, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Adminstoration Tutorial Step by Step, TechBrothersIT

Id: 3MScho4vPkE

Length: 10min 37sec (637 seconds)

Published: Thu Mar 05 2015