Monday, 6 January 2014

Admin Script: Backup SQL Server Databases with Week Of Month Timestamp

This is my collection of generic database backup script for SQL Server using SQLCMD.exe.

The purpose of the script is to backup SQL Server database in the Week Of Month format. The script is invoked via task scheduler to perform database backup (System and User database) weekly

This script consists of two files:
1. SqlServerBackup_WeekOfMonth.cmd
    Script to setup configuration and the script invocation from Task Scheduler

2. SqlServerBackup_WeekOfMonth.sql
    T-SQL Script to perform the database backup

This script works for SQL Server Database (Express or Full Version) from version 2008 and above.

If the setup is done properly, the final result is as follows:

Step 1: Save Two Backup Scripts (see Backup Scripts)

1. Save Two Backup Scripts:
    - SqlServerBackup_WeekOfMonth.cmd
    - SqlServerBackup_WeekOfMonth.sql

2. Edit batch command SqlServerBackup_WeekOfMonth.cmd for configuration

3. The following entry requires input:
    - DB_INSTALL_HOME (If the installed location is not default location)
    - DB_INSTANCE_NAME (SQL Server Instance Name)

4. The Batch command should be renamed by adding prefix such as DB Instance Name
    to avoid confusion. Note: you can have multiple batch file to perform either all, system, or user 
    database backup.

    For Example, SS2012D01__SqlServerBackup_WeekOfMonth.cmd

Step 2: Backup Operator for SQL Server Database

It is advisable to create a user account (Local/Domain) to perform database backup as backup operator. This backup operator is for database only and it should not mix with Windows Backup Operator.

To simplify the setup, the backup operator user will set with 'sysadmin' database role to perform system and user database backup. If you wish to set the least permission for database backup operator, you can use the following script from this link Create Minimum permission backup restore role User / Operator DB for SQL 2008.

In this tutorial, the local account where SQL Server resides will be setup as follows:

1. Create new user to act as database backup operator and set 'Password never expires' option.

2. Open 'Local Security Policy' (Administrative Tools -> Local Security Policy)
    and navigate to Security Settings -> Local Policies -> User Rights Assignment

3. Add the new created user into the following policy:
    - Deny log on locally
    - Log on as batch job

4. Create Folder for backup location and make sure the database backup operator
    has NTFS 'Modify' permission

Step 3: Create a Task Scheduler to perform Database Backup

1. Open 'Task Scheduler' (Administrative Tools -> Task Scheduler)

2. Follow the wizard:

Step 4: Setup SQL Server Security Account

1. Open 'SQL Server Management Studio'

2. Connect to SQL Server Instance

3. Navigate to Security --> Logins
    On Object Explorer, Right click on Logins and select 'New Login...'   

4. On Login Name, select the database backup operator created in step 1
    and set the Server Roles 'sysadmin'

Admin Scripts

No comments :

Post a Comment