SQL Server backup Using Management Studio
I will walk you through steps to create a full database backup in SQL Server 2008 R2 by using SQL Server Management Studio, keep in mind as a database increases in size full database backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement a full database backup with a series of differential database backups.
You can estimate the size of a full database backup by using the sp_spaceused system stored procedure.
When you specify a back up task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and selecting a script destination.
Connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Select a system database
Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
In the Database list box, verify the database name. You can optionally select a different database from the list.
You can perform a database backup for any recovery model (FULL, BULK_LOGGED, orSIMPLE).
In the Backup type list box, select Full. And for Backup component, click Database.
Note that after creating a full database backup, you can create a differential database Backup;
Choose the type of backup destination by clicking Disk, Tape or URL. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.
To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.
To view or select the media options, click Media Options in the Select a page pane.
Select Back up to a new media set, and erase all existing backup sets
enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.
And click OK to start back up, and it will show you the message below when the backup successfully.
SQL Server backup Using Transact-SQL
The following example backs up the complete MyFirstDb database to disk, by using FORMAT to create a new media set.
USE MyFirstDb;
GO
BACKUP DATABASE MyFirstDb
TO DISK = ‘C:\DbBackups\MyFirstDb_backup.Bak’
WITH FORMAT,
MEDIANAME = ‘Z_SQLServerBackups’,
NAME = ‘Full Backup of MyFirstDb’;
GO
IT mug 