Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role….. can take a full backup of Database by using SQL Server Management Studio.
Following steps you need to follow to take a full backup of SQL Server Database. These steps are applicable for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.
1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.
2. Expand Databases node and then right click the database which you want to take a full backup and point to Tasks, and then click Back Up… option as shown in the below snippet to open up Back Up Database dialog box.
3. In Back Up Database Dialog box, in Database List box, verify the database name. Optionally you can even select a different database to backup. You will be able to perform a Full Backup of a database which is FULL, BULK_LOGGED or Simple recovery model.
4. In Backup type list box select Full.Once you have taken a full backup of a database then you can perform Differential backup or Transactional log backups. However, if you database is in Simple recovery model you will not be able to take a Transactional log backup. This is by design from Microsoft.
5. If you want to perform a Copy-Only backup of a database then choose Copy-Only Backup option in Back Up Database Dialog box. Using copy-only backup option you can take a full, differential or transactional log backup which is independent of the sequence of conventional SQL Server backups. The copy-only backup option was introduced in SQL Server 2005.
6. In Backup Component select Databases and in Backup set leave the default Name and optionally enter Description. Leave the default value as 0 days for Backup set will expire after option.
7. Click the Add button to select the file or backup device for the backup destination as shown in the below snippet and click OK to return to Backup Database Dialog box.
8. Once you have completed the General settings you click on Option Tab.
9. Select Options in the Select a page pane to view the advanced options. In Overwrite Media choose Back up to the existing media set and Append to the existing backup set option.
10. Under Reliability section, you can select the check box Verify backup when finished option. By selecting this option you can make sure the database backup is good.
11. Under Compression section, you can select the Compress backup from the drop down list as shown in the below snippet. Microsoft introduced Database Backup Compression Feature in SQL Server 2008 for Enterprise Edition.
12. To generate TSQL Script for the database backup click Scripts and choose Script Action to your choice from the different options.
13. Once the database is successfully backed up you will get a popup message
” The backup of database ” ————— ” completed successfully. ”
I hope this useful to all.
Badrinath's Personal Blog 