SQL Server backup

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.

SQL_03

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.

SQL_04

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.

SQL_05

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.

SQL_06

And click OK to start back up, and it will show you the message below when the backup successfully.

SQL_07

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

Advertisements