Backup SQL Server
Backing up your SQL Server database is essential for protecting your data. The topic introduces SQL Server backup devices, backup media, covers backup types and backup restrictions.
The following are the steps of SQL Server Backup
Step 1 - Select server name and expand server tree in Object Explorer.
Figure 1
After expanding server name.
Figure 2
Step 2 - Expand databases. Your Databases list should look like figure 3. It contains System Databases or user databases. You can get backup from any database either its system database or database created by user.
Figure 3
Step 3 - Expand System Databases. Your System Databases list must look like figure 4.
Figure 4
Step 4 - Select master database. Right click then select Tasks option after that click on Back Up option. You should follow step like figure 5.
Figure 5
y
You will get Back Up Database - master dialog box that should look like figure 6.
Figure 6
There are many options in Back Up database dialog box.
- Database - You can select any database from database list.
Figure 7
- Backup type - This option shows two types, Full and Differential. When you select master table only full type is shown but if you select user defined then you can select either full or differential type from drop down list. Suppose I select Main user database in Database option, then you will see differential option that looks like figure 8.
Figure 8
- Copy Only Backup - Copy Only Backup check box is optional.
- Backup component - Click on Database.
- Name - This option can either take default name or you can enter any name.
- Description - You can write any description about back up.
- Backup set will expire - Using two options After or On. In After option you can set any day from current day, when you want erase database backup automatically. You can use 0 To 99999 days. Zero means it will not expire. In On option, you can select specific date to expire your database backup.
- Destination - This option specify where you want to save your backup database.
- Add - You can add your database backup any specific location.
Step 5 - Click on Add button to save backup database on D drive in backup folder (see figure 9). Click on browser button, select backup folder from D drive.
Figure 9
Enter file name Backup Master in locate database files dialog box that should look like figure 10, then click OK button.
Figure 10
- Remove - This option removes the backup database location.
- Contents - This option shows database content. You can check the content of backup database.
Step 6 - Click on Content button. Device content dialog box appears, it will show you the content of backup database as in figure 11.
Figure 11
Step 7 - Click on OK button.
You will get a message box as in figure 12.
Figure 12
Step 8 - Check backup folder in D drive that data is saved or not.
Recovery in SQL Server
To recover a SQL Server database from a failure, a database administrator has to restore a set of SQL Server backups in a logically correct and meaningful restore sequence.
Step 1 - Expand SQL Server in Object Explorer. Your Object Explorer should look like figure 1.
Figure 1
Step 2 - Expand Databases option. You will see content like figure 2.
Figure 2
Step 3 - Right click on Databases option then click Restore Database option (see figure 3 ) and Restore Database dialog box should appear that should look like figure 4.
Figure 3
Figure 4
Step 4 - Type the name of a new database or you can select existing database from drop down list. I type Master 2 as name for new database.
Step 5 - Select From device option to get data from drive.
Step 6 - Click on browser button (see figure 5). Specify Backup dialog box appear that should look like figure 6.
Figure 5
Figure 6
Step 7 - Click on Add button. locate backup file dialog box appear (see figure 7). Select backup file from D drive and then click OK button.
Figure 7
Step 8 - Click OK button on Specify Backup dialog box.
Step 9 - Check in Restore option and then click OK button on Restore Database - Master 2 dialog box that should look like figure 8. Clicking OK button, Microsoft SQL Server Management Studio dialog box appear and then click on OK button (see figure 9).
Figure 8
Figure 9
you can easily see Master 2 database that is showing in figure 10
Figure 10
Step 10 - Check all the content of Master 2 database add properly or not, know that expanding Master 2 database should look like figure 11.