Setting up database backups using the scripts

Follow this procedure to set up database backup scripts.

Procedure

  1. Each backup script contains a section of variables at the top of the file. The values of these variables are the only things that should be edited in the file, as follows: 
    
/*****************************************************
    * Script Variables
    * @jobName         - The name of the backup job
    * @jobOwner        - The SQL Server Login that will own the backup job
    * @operator        - The name of the email operator who will be notified
    *                    if the job fails (added through sp_add_operator).
    * @scheduleName    - The name of the backup job schedule
    * @database        - The name of the database
    * @fullBackupFile   - The filename of the full backup of the   database.
    *                     By default this will be
    *                     overwritten with a new backup twice a week (Sunday,
    *                     Wednesday).
    * @logBackupFile    - The filename of the log bakup for the   database.
    *                     This will be appended to
    *                    daily and started fresh on Monday and Thursday by
    *                    default.
    ******************************************************/
   DECLARE @database nvarchar(256),
           @fullBackupFile nvarchar(256),
           @jobOwner nvarchar(128),
           @jobName nvarchar(128),
           @scheduleName nvarchar(128),
           @serverName nvarchar(30),
           @operator nvarchar(128),
           @backupCmd nvarchar(4000);

   SELECT  @jobName = N' Reporting Database Backup',
           @scheduleName = N' Reporting Backup Job',
           @jobOwner = N'sa',
           @database = N'DC_REPORTS',
           @fullBackupFile = N'c:\Discover\db_backups\tl_reports_full.bak',
           @serverName = N'(local)',
           @operator = NULL;

   /********* DO NOT CHANGE ANYTHING BELOW THIS LINE *********/
  2. The only required change is to assign valid file paths to the @fullBackupFile and @logBackupFile variables.
  3. To create and schedule a backup for a particular database:
    1. Open the file in SQL Server Management Studio. The scripts must be run as SA or an equivalent login.
    2. Set the files paths correctly.
    3. If the SQL Server Notification service is being used, set the @operator variable to a valid SSNS Operator. When run, the backup job sends email to the specified address.
    4. Run the script.