top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Create a Full Database Backup (SQL Server)

+1 vote
294 views

Using SQL Server Management Studio

When you specify a back up task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUPscript by clicking the Script button and selecting a script destination.

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 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.

General Page

  1. In the Database drop-down list, verify the database name. Optionally, you can select a different database from the list.

  2. The Recovery model text box is for reference only. You can perform a database backup for any recovery model (FULLBULK_LOGGED, orSIMPLE).

  3. In the Backup type drop-down list, select Full.

    Note that after creating a full database backup, you can create a differential database backup;

  4. Optionally, you can select the Copy-only backup checkbox 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. For more information, see Copy-Only Backups (SQL Server). A copy-only backup is not available for the Differential backup type.

  5. For Backup component, select the Database radio button.

  6. In the Destination section, use the Back up to drop-down list to select the backup destination. Click Add to add additional backup ojects and/or destinations.

    To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and clickContents.

Media Options Page

  1. To view or select the media options, click Media Options in the Select a page pane.

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

    System_CAPS_ICON_important.jpg Important


    The Overwrite media option is disabled if you selected URL as the backup destination in the General page

  • Back up to the existing media set

    System_CAPS_ICON_important.jpg Important


    If you plan to use encryption, do not select this option. If you select this option, the encryption options in the Backup Options page will be disabled. Encryption is not supported when appending to the existing backup set.

    For this option, click either Append to the existing backup set or Overwrite all existing backup sets

    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 descriptiontext box.

  1. In the Reliability section, optionally check:

    • Verify backup when finished.

    • Perform checksum before writing to media. For information on checksums,

    • Continue on error.

  2. The Transaction log section is inactive unless you are backing up a transaction log (as specified in the Backup type section of the Generalpage).

  3. In the Tape drive section, the Unload the tape after backup option is active if you are backing up to a tape drive (as specified in theDestination section of the General page). Clicking this option activates the Rewind the tape before unloading option.

Backup Options Page

  1. To view or select the backup options, click Backup Options in the Select a page pane.

  2. In the Name text box either accept the default backup set name, or enter a different name for the backup set.

  3. In the Description text box, you can optionally enter a description of the backup set.

  4. 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 Settingspage.

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

       

  5. In the Compression section, use the Set backup compression drop-down list to select the desired compression level. 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.

     

  6. In the Encryption section, use the Encrypt backup checkbox to decide whether to use encryption for the backup. Use the Algorithm drop-down list to select an encryption algorithm. Use the Certificate or Asymmetric key drop-down list, to select an existing Certificate or Asymmetric key. Encryption is supported in SQL Server 2014 or later. For more details on the Encryption options,

Examples

A. Full back up to disk to default location

In this example the Sales database will be backed up to disk at the default backup location. A back up of Sales has never been taken.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. Click OK.

B. Full back up to disk to non-default location

In this example the Sales database will be backed up to disk at E:\MSSQL\BAK. Previous back ups of Sales have been taken.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. On the General page in the Destination section select Disk from the Back up to: drop-down list.

  4. Click Remove until all existing backup files have been removed.

  5. Click Add and the Select Backup Destination dialog box will open.

  6. Enter E:\MSSQL\BAK\Sales_20160801.bak in the file name text box.

  7. Click OK.

  8. Click OK.

C. Create an encrypted backup

In this example the Sales database will be backed up with encryption to the default backup location. A database master key has already been created. A certificate has already been created called MyCertificate. A T-SQL example of creating a database master key and certificate can be seen at Create an Encrypted Backup.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. On the Media Options page in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.

  4. On the Backup Options page in the Encryption section select the Encrypt backup check box.

  5. From the Algorithm drop-down list select AES 256.

  6. From the Certificate or Asymmetric key drop-down list select MyCertificate.

  7. Click OK.

D. Backing up to the Microsoft Azure Blob storage service

Common Steps

The three examples below perform a full database backup of Sales to the Microsoft Azure Blob storage service. The storage Account name ismystorageaccount. The container is called myfirstcontainer. For brevity, the first four steps are listed here once and all examples will start onStep 5.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. On the General page in the Destination section select URL from the Back up to: drop-down list.

  4. Click Add and the Select Backup Destination dialog box will open.

    D1. Striped Backup to URL and a SQL Server credential already exists
    A stored access policy has been created with read, write, and list rights. The SQL Server credential,https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy.

    1. Select https://mystorageaccount.blob.core.windows.net/myfirstcontainer from the Azure storage container: text box

    2. In the Backup File: text box enter Sales_stripe1of2_20160601.bak.

    3. Click OK.

    4. Repeat Steps 4 and 5.

    5. In the Backup File: text box enter Sales_stripe2of2_20160601.bak.

    6. Click OK.

    7. Click OK.

    D2. A shared access signature exists and a SQL Server Credential does not exist

    1. Enter https://mystorageaccount.blob.core.windows.net/myfirstcontainer in the Azure storage container: text box

    2. Enter the shared access signature in the Shared Access Policy: text box.

    3. Click OK.

    4. Click OK.

    D3. A shared access signature does not exist

    1. Click the New container button and the Connect to a Microsoft Subscription dialog box will open.

    2. Complete the Connect to a Microsoft Subscription dialog box and then click OK to return the Select Backup Destination dialog box. See See Connect to a Microsoft Azure Subscription for additional information.

    3. Click OK at the Select Backup Destination dialog box.

    4. Click OK

 
 
Part 2 continuous 
posted Sep 2, 2016 by Jdk

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

Create a Full Database Backup (SQL Server)

Using SQL Server Management Studio

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.

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 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.

General Page

  1. In the Database drop-down list, verify the database name. Optionally, you can select a different database from the list.

  2. The Recovery model text box is for reference only. You can perform a database backup for any recovery model (FULLBULK_LOGGED, orSIMPLE).

  3. In the Backup type drop-down list, select Full.

    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).

  4. Optionally, you can select the Copy-only backup checkbox 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. For more information, see Copy-Only Backups (SQL Server). A copy-only backup is not available for the Differential backup type.

  5. For Backup component, select the Database radio button.

  6. In the Destination section, use the Back up to drop-down list to select the backup destination. Click Add to add additional backup ojects and/or destinations.

    To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and clickContents.

Media Options Page

  1. To view or select the media options, click Media Options in the Select a page pane.

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

    System_CAPS_ICON_important.jpg Important


    The Overwrite media option is disabled if you selected URL as the backup destination in the General page. For more information, see Back Up Database (Media Options Page)

  • Back up to the existing media set

    System_CAPS_ICON_important.jpg Important


    If you plan to use encryption, do not select this option. If you select this option, the encryption options in the Backup Options page will be disabled. Encryption is not supported when appending to the existing backup set.

    For this option, click either Append to the existing backup set or Overwrite all existing backup sets

    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 descriptiontext box.

  1. In the Reliability section, optionally check:

    • Verify backup when finished.

    • Perform checksum before writing to media. For information on checksums,

    • Continue on error.

  2. The Transaction log section is inactive unless you are backing up a transaction log (as specified in the Backup type section of the Generalpage).

  3. In the Tape drive section, the Unload the tape after backup option is active if you are backing up to a tape drive (as specified in theDestination section of the General page). Clicking this option activates the Rewind the tape before unloading option.

Backup Options Page

  1. To view or select the backup options, click Backup Options in the Select a page pane.

  2. In the Name text box either accept the default backup set name, or enter a different name for the backup set.

  3. In the Description text box, you can optionally enter a description of the backup set.

  4. 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 Settingspage.

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

       

  5. In the Compression section, use the Set backup compression drop-down list to select the desired compression level. 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.

     

  6. In the Encryption section, use the Encrypt backup checkbox to decide whether to use encryption for the backup. Use the Algorithm drop-down list to select an encryption algorithm. Use the Certificate or Asymmetric key drop-down list, to select an existing Certificate or Asymmetric key. Encryption is supported in SQL Server 2014 or later. For more details on the Encryption options,

You can use the Maintenance Plan Wizard to create database backups.

Examples

A. Full back up to disk to default location

In this example the Sales database will be backed up to disk at the default backup location. A back up of Sales has never been taken.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. Click OK.

B. Full back up to disk to non-default location

In this example the Sales database will be backed up to disk at E:\MSSQL\BAK. Previous back ups of Sales have been taken.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. On the General page in the Destination section select Disk from the Back up to: drop-down list.

  4. Click Remove until all existing backup files have been removed.

  5. Click Add and the Select Backup Destination dialog box will open.

  6. Enter E:\MSSQL\BAK\Sales_20160801.bak in the file name text box.

  7. Click OK.

  8. Click OK.

C. Create an encrypted backup

In this example the Sales database will be backed up with encryption to the default backup location. A database master key has already been created. A certificate has already been created called MyCertificate. A T-SQL example of creating a database master key and certificate can be seen at Create an Encrypted Backup.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. On the Media Options page in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.

  4. On the Backup Options page in the Encryption section select the Encrypt backup check box.

  5. From the Algorithm drop-down list select AES 256.

  6. From the Certificate or Asymmetric key drop-down list select MyCertificate.

  7. Click OK.

D. Backing up to the Microsoft Azure Blob storage service

Common Steps

The three examples below perform a full database backup of Sales to the Microsoft Azure Blob storage service. The storage Account name ismystorageaccount. The container is called myfirstcontainer. For brevity, the first four steps are listed here once and all examples will start onStep 5.

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click Sales, point to Tasks, and then click Back Up....

  3. On the General page in the Destination section select URL from the Back up to: drop-down list.

  4. Click Add and the Select Backup Destination dialog box will open.

    D1. Striped Backup to URL and a SQL Server credential already exists
    A stored access policy has been created with read, write, and list rights. The SQL Server credential,https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy.

    1. Select https://mystorageaccount.blob.core.windows.net/myfirstcontainer from the Azure storage container: text box

    2. In the Backup File: text box enter Sales_stripe1of2_20160601.bak.

    3. Click OK.

    4. Repeat Steps 4 and 5.

    5. In the Backup File: text box enter Sales_stripe2of2_20160601.bak.

    6. Click OK.

    7. Click OK.

    D2. A shared access signature exists and a SQL Server Credential does not exist

    1. Enter https://mystorageaccount.blob.core.windows.net/myfirstcontainer in the Azure storage container: text box

    2. Enter the shared access signature in the Shared Access Policy: text box.

    3. Click OK.

    4. Click OK.

    D3. A shared access signature does not exist

    1. Click the New container button and the Connect to a Microsoft Subscription dialog box will open.

    2. Complete the Connect to a Microsoft Subscription dialog box and then click OK to return the Select Backup Destination dialog box. See See Connect to a Microsoft Azure Subscription for additional information.

    3. Click OK at the Select Backup Destination dialog box.

    4. Click OK.

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 ] ] ;

    OptionDescription
    databaseIs the database that is to be backed up.
    backup_device [ ,...n ]Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option:

    { DISK | TAPE } =physical_backup_device_name

    For more information, see Backup Devices (SQL Server).
    WITHwith_options [,...o ]Optionally, specifies one or more additional options, o. For information about some of the basic with options, see step 2.
  2. Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options,

    • Basic backup set WITH options:

      { COMPRESSION | NO_COMPRESSION }
      In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.

      ENCRYPTION (ALGORITHM, SERVER CERTIFICATE |ASYMMETRIC KEY)
      In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.

      DESCRIPTION = { 'text' | @text_variable }
      Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.

      NAME = { backup_set_name | @backup_set_name_var }
      Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.

    • Basic backup set WITH options:

      By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. To explicitly specify this, use the NOINIT option. For information about appending to existing backup sets,

      Alternatively, to format the backup media, use the FORMAT option:

      FORMAT [ , MEDIANAME= { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
      Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.

      System_CAPS_ICON_important.jpg Important


      Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.

Examples (Transact-SQL)

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.

Transact-SQL

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.

Transact-SQL

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.

Transact-SQL

-- 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  

Using PowerShell

Use the Backup-SqlDatabase cmdlet. To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.

Examples

A. Full local backup

The following example creates a full database backup of the MyDB database to the default backup location of the server instanceComputer\Instance. Optionally, this example specifies -BackupAction Database.

Windows PowerShell

Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  

B. Full backup to Microsoft Azure

The following example creates a full backup of the database Sales on the MyServer instance to the Microsoft Azure Blob Storage service. A stored access policy has been created with read, write, and list rights. The SQL Server credential,https://mystorageaccount.blob.core.windows.net/myfirstcontainer, was created using a Shared Access Signature that is associated with the Stored Access Policy. The PowerShell command uses the BackupFile parameter to specify the location (URL) and the backup file name.

Windows PowerShell

import-module sqlps;
$container = 'https://mystorageaccount.blob.core.windows.net/myfirstcontainer';
$FileName = 'Sales.bak';
$database = 'Sales';
$BackupFile = $container + '/' + $FileName ;
  
Backup-SqlDatabase -ServerInstance "MyServer" –Database $database -BackupFile $BackupFile;

To set up and use the SQL Server PowerShell provider

  • SQL Server PowerShell Provider
READ MORE

Before starting, you will need to copy the SQL backup file (typically has a .BAK extension) to a local hard drive on the destination SQL Server machine.

image

Open SQL Server Management Studio and login to the SQL Server you want to restore the database to. It is best to either login as a Windows Administrator or as the SQL ‘sa’ user.

image

Once logged in, right click on the Databases folder and select ‘Restore Database’.

image

Click the ellipses button next to ‘From device’ under the ‘Source for restore’ section.

image

Set ‘File’ as the backup media and then click ‘Add’.

image

Browse to the SQL backup (BAK) file you want to restore.

image

image

In the Restore Database dialog, type or select the name of the database you want this backup restored to.

  • If you select an existing database, it will be replaced with the data from the backup.
  • If you type a database name which does not currently exist in your SQL Server installation, it will be created.

Next, select the restore point you want to use. Since a SQL backup file can hold multiple backups you may see more than one restore point listed.

image

At this point, enough information has been entered for the database to be restored. However, SQL backup files store information about where data files are copied so if there are any file system problems such as a the destination directory not existing or conflicting data file names an error will occur. These problems are common when restoring a backup created on a different SQL Server installation.

To review and change the file system settings, click the Options page on the left in the Restore Database dialog.

image

On the options page, you will want to make sure the ‘Restore As’ column points to valid folder locations (you can change them as needed). The the files do not have to exist, however the folder path must exist. If the respective files do exist, SQL Server follows a simple set of rules:

  • If the ‘To database’ (from the General page) matches the restore database backup (i.e. restoring to matching databases), the respective files will be overwritten as part of the restore.
  • If the ‘To database’ does not match the restore database backup (i.e. restoring to a different database), the ‘Overwrite the existing database’ will need to be checked for the restore process to complete. Use this function with caution as you can potentially restore database backup information on top of data files from a completely different database.

Generally, you can tell the databases differ based on the ‘Original File Name’ which is the internal name SQL Server uses to reference the respective files.

image

Once your restore options are set, click Ok.

image

Conclusion

SQL Server Management Studio makes the restore process simple and is ideal if you seldom perform database restores. This process works in every version of SQL Server from Express to Enterprise. If you are using the Express version, you can download SQL Server Management Studio Express to get access to this interface.

READ MORE

Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM_HHMMSS.bak

    DECLARE @name VARCHAR(50) -- database name  
    DECLARE @path VARCHAR(256) -- path for backup files  
    DECLARE @fileName VARCHAR(256) -- filename for backup  
    DECLARE @fileDate VARCHAR(20) -- used for file name     

    -- specify database backup directory
    SET @path = 'C:\Backup\'          

    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')        

    DECLARE db_cursor CURSOR FOR  
    SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases        

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name           

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           SET @fileName = @path + @name + '_' + @fileDate + '.bak'  
           BACKUP DATABASE @name TO DISK = @fileName          

           FETCH NEXT FROM db_cursor INTO @name   
    END           

    CLOSE db_cursor   
    DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.

READ MORE
...