Backup and Restore Database in SQL Server

hello friends welcome back to see Colette Munoz in my previous videos we saw how to download sequel server and how to install sequel server and in the next videos we saw how to create a new database and add new tables and to some changes on those tables in today's video we will see how to create a backup of a database and how to restore the backup of that database okay so let me take you to SSMS where we can see we have a database over here testament DB right and if I expand it I can we can see that you know there is a table DB o dot employ okay so let us take the backup of this database for taking the backup what you have to do is you have to right-click on the database and click on task and then select backup a pop-up window will open here so here you can reselect the database if you want the recovery model is full so the recovery model you can change from here itself okay so right now it is full don't worry about that so backup type there are three types of backups you can take one is full it will take the full backup of the database second is differential so if you have already taken a full backup of a database you can take differential backup so it will only take the backup of the objects changed after the full backup is taken previously similar is the transaction box so it will take only the backup of those transaction logs which have changed after the previous transaction log backup okay so backup component is our database and and the destination is disk ok and this is the location where the database backup is kept by default this is under C program files Microsoft sequel server and these files so you can even remove this location can just click on remove and add a new location of your choice right so what I will do is I will move away from the C Drive and and place the backup in D Drive sequel folder and let me type the backup name bak so the database name is Tess command a B so I have kept the similar name with bak extinction you can click on OK again ok and the new name is reflected here ok so one option is to you know just click on OK it will take the database other option is to you know if you script out this thing it will give you a sequel query to take the backup okay so let me show you how does this query look like so backup database Testament DB - where - this location okay with no format no in it the name of this skip they these are some of the options okay and so this is the way you know you can take backup either from the SSMS or from the sequel query okay so let's just now take backup from SSMS over here the database backup is taken successfully okay let me go to the location right so here the database backup is taken okay and if I let let me drop this delete this file and take the backup from the sequel file right I will just execute it okay and it shows that you know the process completed within these seconds microseconds and the same backup is taken here again okay now this same query you can use to backup other databases like Adam she works 2014 right so what I will do is I will replace the Testament DB with this adventure box 2014 so you can directly drag it here and drop it here and I will remove the Testament DB name okay change the backup file name so that it won't overwrite you are previously back taken backup okay and here also right so let me execute it okay and see within one second it has taken backup of adventureworks also right so these two backup files are created over here in this folder okay so this is the way you can take backup from the GUI tool or the sequel script right okay now let's restore the database right so we have created these two databases backups here now let's try to restore Testament DB backup file right so what you can do is you can right click on databases and just click on this option restore database okay so a new pop-up window comes up over here okay here you have to click on device right and click on this button and click on add it will take you to the default backup location of C Drive Program Files MS sequel server we have to go to the that is this drive okay so I will select SN we back up click OK again click ok ok so we already have a database here so let's try to create the database with some different name because it will otherwise conflict ok and just you just check the these things backup sets to restore and just see you know the file name that are changed so now the file name will be Testament tb2 so it has effects the two here right on the embryo file amplifies the master data file file where your data is stored and this is the LD of file so DB 2 so it is fixed over here also LD file is the log data file ok and within options you can just ignore this tail log log backup right so I'll just unselect it ok now again here you there are two options either you can click on OK or you can script out the whole task right so let me script out the thing before I click OK ok so this will put the query over here use master then go restore database from disk here this location right where the database was taken then with file equal to 1 then move Testament DB to this location and move log to this location no unload stats equal to 5 right these are some other options and this is the query right to restore the database backup right now it's your choice to click here or to click here right so if I just click here there is that database Testament DB to restored successfully ok and you can see here right so the same employee table you will be seeing over here right so whatever the changes were done here in this database and you will be seeing the same changes over here in this database that is restore just now okay let me drop this database ok and let me restore this database not while the GUI but via the restore script right just execute it again if you see here and you will see here and you will see here the process and it is completed right and if i refresh it you will see the same data is coming over here okay so this is it and you know as you see it is very simple to take a backup of a database and to restore a backup of a database I in the next videos will try to see some similar things and please stay tuned for that please let me know your comments and suggestions and please subscribe by subscribing you will get to know about the latest videos that I put in here okay thanks a lot for watching have a good day

Channel: SQL with Manoj

Views: 222,202

Rating: 4.864172 out of 5

Keywords: SQL Server, SQL, Microsoft SQL Server, Backup Database, Restore Database

Id: mr8Lpkx5yag

Length: 7min 15sec (435 seconds)

Published: Tue Mar 29 2016