Create a Full Database Backup (SQL Server) Using SQL Server Management Studio:
———————————————————————————————————-

Recommendations

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. For more information, see Differential Backups (SQL Server).

You can estimate the size of a full database backup by using the sp_spaceused system stored procedure.

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).

Permissions:

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

To back up a database:

1.After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2.Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.

3.Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4.In the Database list box, verify the database name. You can optionally select a different database from the list.

5.You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).

6.In the Backup type list box, select Full.

7.Note that after creating a full database backup, you can create a differential database backup; for more information, see Create a Differential Database Backup (SQL Server).

8.Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.

Note:
When the Differential option is selected, you cannot create a copy-only backup.

9.For Backup component, click Database.

10.Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

11.Optionally, in the Description text box, enter a description of the backup set.

12.Specify when the backup set will expire and can be overwritten without explicitly skipping verification of the expiration data:
To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings Page). To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

13.Choose the type of backup destination by clicking Disk or Tape. 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.

14.To view or select the advanced options, click Options in the Select a page pane.

15.Select an Overwrite Media option, by clicking one of the following:

Back up to the existing media set :
For this option, click either Append to the existing backup set or Overwrite all existing backup sets. For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.

Back up to a new media set, and erase all existing backup sets

For this option, 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.

16.In the Reliability section, optionally check:

Verify backup when finished.

Perform checksum before writing to media, and, optionally, Continue on checksum error.
17.If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

18.SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

Using Transact-SQL to create a full database backup:

1.Execute the BACKUP DATABASE statement to create the full database backup, specifying:

The name of the database to back up.

The backup device where the full database backup is written.

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE database

TO backup_device [ ,…n ]

[ WITH with_options [ ,…o ] ] ;

Examples:

A. Backing up to a disk device
The following example backs up the complete AdventureWorks2012 database to disk, by using FORMAT to create a new media set.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.Bak’
WITH FORMAT,
MEDIANAME = ‘Z_SQLServerBackups’,
NAME = ‘Full Backup of AdventureWorks2012’;
GO

B. Backing up to a tape device
The following example backs up the complete AdventureWorks2012 database to tape, appending the backup to the previous backups.

USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO TAPE = ‘\\.\Tape0’
WITH NOINIT,
NAME = ‘Full Backup of AdventureWorks2012’;
GO

C. Backing up to a logical tape device
The following example creates a logical backup device for a tape drive. The example then backs up the complete AdventureWorks2012 database to that device.

— Create a logical backup device,
— AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice ‘tape’, ‘AdventureWorks2012_Bak_Tape’, ‘\\.\tape0’;
USE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO AdventureWorks2012_Bak_Tape
WITH FORMAT,
MEDIANAME = ‘AdventureWorks2012_Bak_Tape’,
MEDIADESCRIPTION = ‘\\.\tape0’,
NAME = ‘Full Backup of AdventureWorks2012’;
GO

Follow these steps to create the job and put your backup statement inside of it:

1.Expand the ‘SQL Server Agent’ tree and right-click on ‘Jobs’. Then choose the top item, ‘New Job…’

2.Now you’ve got the new job dialog box. Filling in the info is pretty easy. You need to give your job a name, and everything else is optional. Here I’m going to fill in the name of the job as ‘Backup user database’.

3.Next click on the ‘Steps’ pane on the left and you’ll be presented with this screen. It’s blank because you haven’t created any steps yet. So go ahead and click on the ‘New’ button at the bottom.

4. This is where the real magic happens. Again, you have to fill in a name so you know what the step is called. Make it something descriptive. There are several step types to choose from, but the default is T-SQL and since we’re running a T-SQL command that’s clearly the one we want to go with. The database defaults to ‘master’ and that’s just fine with us. Here’s what we have so far. You see the only thing we’ve had to do is fill in the step name.

5. The only thing left to do is to copy your backup statement into the query window. Of course it’s always a good idea to make sure your code will parse before you try to run it. Just click the ‘Parse’ button I’ve circled. Here’s what that looks like.

6.Now click OK and it’ll take you back to your new job window and now you’ll see your job step listed. And you can stack as many as you like inside there.

7. To schedule your job, just click on ‘Schedules’ on the left and then choose the schedule that’s right for you. It works similar to the the way it does in Windows so there’s really not much need for me to rehash it here.

8.Once you click OK all the way out until the new job box is closed, your job will be added to the job tree. You may have to right-click on ‘Jobs’ and refresh the display for it to show up.

Now that you’ve created the job to backup your database I’d like to go back to the command again and add one more part. See, when you backup a database to a file it won’t overwrite the file by default. By default it will append to the file, so your backup file will just get bigger and bigger. So what you want to do is add a special flag to the command that tells it to initialize (or overwrite) the file every time. That makes our backup command look like this now:

BACKUP Database Model
To disk = ‘c:\ModelBackup.bak’

With INIT

This is a good thing to keep in mind while you’re creating your job.