Backup and Restore SQL Server Databases Using SQL Server Management Studio

Published by admin on November 27, 2015

It is very good practice to back up SQL Server database against SQL server disasters, which can happen anytime due to various, reasons like media failures, hardware damages, user mistakes etc. It is possible to backup and restore SQL Server databases using SQL Server Management Studio. Simply follow the given steps to backup SQL Server database. Assume you are using SQL Server 2012.

Full Backup – Using SQL Server Management Studio

1. In Object Explorer, expand databases, choose user database (or simply expand system databases and choose system database).
2. Now right-click on the database, hit on Tasks > Backup.
3. Enter the details (as explained below) when Databasesdialogue box appears (in the Select a page General is selected by default).

Source Section

• Check Database list box and confirm database name (If required you can change the database name).
• Enter recovery model (FULL, BULK_LOGGED, or SIMPLE) in Recovery model text box.
• Choose backup type as Full in Backup type list box.

Remember – With Full option you have selected above optionally create the copy-only back up by simply checking the checkbox Copy Only Backup.

• Select database as Backup component.

Backup set Section

• Enter the Name for backup set.
• Enter the description in Description text box (this is optional).
• Enter backup set expiry detail: select After / On and enter required values.

Destination Section

• Select Disk or Tape as destination, and hit on Add; use Select Backup Destination dialogue box to choose the required destination. Now the added destination will appears in Back up list box.

1. Hit Options in Select a pagepane present on left of dialogue box. This page will display the advanced options. Now enter the information (as described below) as needed.

Overwrite media Section

• Choose Overwrite media option:
• Backup to existing media set

After  you select this choice, select Append to the existing backup set OR Overwrite all existing backup sets.
Remember : – Check media set name and backup set expiration is just the additional option. It simply verify the expiry details of backup set. You can electively provide the media set name in Media set name text box.

• Now back up to new media set, and delete the existing backup sets.

After choosing this option, enter media set name in New media set name text box (electively, you can add the description in New media set description text box).

Reliability Section

Choose required options from given three:

• Verify the backup when you are over
• Execute the checksum before writing to media
• Continue with the checksum error

Tape drive section (If the destination selected is Tape drive then only this section is enabled)

It got two options:

• After backup unload the tape
• Before unloading rewind the tape

Compression section

• SQL Server 2012 supports the backup compression. Choose the required options in Set backup compression list box. The options are given below.

1. Hit on Generalin Select a page pane present on the left of dialogue box. Hit on OK.
2. Hit on OKwhen message appears after completing the database backup.

Restore the SQL Server Databases Using the SQL Server Management Studio

In any organization, backing up SQL databases is the routine process because owing critical data isn’t enough you need to protect the data. After user face data loss, users can easily restore the lost data from backups. For restoring, the full backup users can use SQL Server Management Studio. The steps for SQL Server Recovery are mentioned below (given for SQL Server 2012):

1. In Object Explorer, expand the databases, choose user database (or expand the system databases and choose system database).
2. Now right-click on database, hit on Tasks > Restore > Database.
3. Enter the information (as given below) when RestoreDatabases dialogue box appears (in the Select a page pane by default General is selected); hit on OK.

Source Section

• Choose source database from Database in list box.
• Hit on browse button (…). Enter details when Select backup devices dialogue box appears. Choose device type from list in Backup media type box (you can even use Add to add more devices); hit on OK.
• Choose the database to restore in Database list box (This is available only after selecting Device in previous step).

Destination Section

• Now verity the name of database to restore in Database box (you can modify the name if needed).

Timeline Button

• Hit on the Timeline button.
• Enter the information (as given below) when Backup Timeline dialogue box appears.

• Hit on Specific date and time. Enter time and date using Date and Time boxes (you even use slider bar for it); hit on OK.

Restore plan Section

• Backup set to restore grid now shows the backups (required to perform restore action).

1. Hit on Optionsin Select a page pane present on left of dialogue box. The Options page displays the advanced options. Enter information (as given below) and hit on OK.

Restore options Section
Choose the suitable option from given three:

• Overwrite existing database (WITH REPLACE)
• Protect the duplication settings (WITH KEEP_REPLICATION)
• Confine access to restored database (WITH RESTRICTED_USER)

Choose the option in Recovery state box from given three:

• Restore With The Recovery
• Restore With The Norecovery
• Restore With The Standby

Tail-Log backup Section

No need to change the settings in this section. Though, you can

– Verify the checkboxes:

• Before restore take the tail-log backup
• In the restoring state Leave the source database

– Choose Backup file path

Server connections Section

• Verify checkbox Close existing connections to the database.

Prompt Section

• Optionally, you can verify the checkbox Prompt before restoring each backup.

1. Hit on OK when confirmation message displays after restoring the database.

If you face any sort of corruption issue while backuping the data. Like while backuping the data you face power failure. Virus attack, software conflict and other possible causes can make your data inaccessible. To fix the corruption issue use MS SQL File Repair Tool that guarantees you 100% recovery even after backup file corruption. It is a perfect solution that helps you to recover the corrupt and damaged MS SQL Server Database files accurately.

 Steps to fix corrupt SQL database:

Step 1: Stop running MS SQL server, the repair process is done on the copy of corrupt database so tap to ‘OK’ button and continue.

Step 2: now click on the ‘Select database’ button and select the path of corrupt MDF file. After that search for your corrupt database file by using ‘Look in’ and ‘File Type’ button. To start with the repairing process click on the ‘Scan file’ button.

Step 3: The complete listing of recoverable objects of database is in tree view on the left side of the window. Tapping to the object you can see the preview.

Step 4: Click on the ‘Start Repair’ icon to begin the process. To save the repaired file in the desired location click on the browse button and assign the path, otherwise the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘Ok’ button.

565 Total Views 1 Views Today