Backup and Restore Using SQL Enterprise Manager
One of the important features of Database administrator is to Backup and recovery of database. Yours company’s data is the most vital asset as it is treated as one of the confidential component of any organization. There are rare chances to recover the lost database. A backup device may be created using SQL Enterprise Manager or by using Transact-SQL commands.
How to create Backup Devices to Backup files
Follow the few steps to create and restore your backup using backup devices.
Open Enterprise Manager and connect to the server where you want to create your backup device.Explore the Management folder and point to the backup folder. Now right click on this and select New Backup Device nowan appletof Backup Device Properties – New Device willpop up. Suggest backup name and location for your backup device.
D:/backup/backupname.bak. Disable Tape drive name option if it is not installed on your system. This will create your backup device after selecting OK (refer Image 1).
Image 1: Backup Device Properties
After creation of the backup devices, it appears within backup tree of the SQL Enterprise Manager. Now right-click on the backup device and select Properties to view backup device contents, file name (properties) refer Image 2.
Image 2: Backup Device Properties
Using View Contents… will show you all the backups that have been performed to your backup device.
Other way to create a backup device is using Transact-SQL command.
Open SQL Query Analyzer and type Transact-SQL command to backup your device. Below figure 3 shows the use of a Transact-SQL command. This will create a backup device in a similar ways as shown in the earlier steps.
Image 3: Creating a backup device using Transact – SQL Command
How To restore your backup files from backup devices?
Now Open Enterprise Manager, connect to the server wherein you want to restore your backup history. Right-click on any database >> All Tasks >> Restore Database... The Restore Database dialog box will popup automatically. Choose From Device option to restore backup files. Click on the Read backup set information and add to backup history option in the Parameters option as shown in Image 3.
Image 4: Restore action
Click the Select Devices... to add the backup files to the database list. The Choose Restore Devices dialog box will appear shortly.
Image 5: Choose Restore Devices
Select the backup files to load the backup history by clicking Add. After the selection of the files press OK button to complete restoration process. Click the OK. This will initiate to restore the backup history to the MSDB database or tables.
Once the backup history is completely loaded, The SQL SE Manager will show the message – Sucessfully loaded backup set history. Now click OK to complete the entire history restoration process.