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_VERSION
    - DB_INSTALL_HOME (If the installed location is not default location)
    - DB_INSTANCE_NAME (SQL Server Instance Name)
    - BACKUP_HOME

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

Script #1: SqlServerBackup_WeekOfMonth.cmd

@ECHO OFF ECHO ============================================================ ECHO YL Library : Backup SQL Server Database using SQLCMD.exe ECHO with Week of Month timestamp (eg. W1) ECHO Author : Zhang Yunlong ECHO Blog : ylnotes.blogspot.com ECHO Filename : 1. SqlServerBackup_WeekOfMonth.cmd ECHO 2. SqlServerBackup_WeekOfMonth.sql ECHO ============================================================ ECHO History: ECHO 1.00 Initial Release ECHO ============================================================ ECHO Execution Date: %DATE% %TIME% ECHO ============================================================ ECHO. ::================================================================= :: DB Server Info ::================================================================= ::Database Version :: Value Description Internal Version :: ------ ------------------ ---------------- :: SS2008 SQL Server 2008 90 :: SS2008R2 SQL Server 2008 R2 100 :: SS2012 SQL Server 2012 110 :: SS2014 SQL Server 2014 120 SET DB_VERSION=<db_version> IF "%DB_VERSION%" == "SS2008" SET DB_INTERNAL_VERSION=90 IF "%DB_VERSION%" == "SS2008R2" SET DB_INTERNAL_VERSION=100 IF "%DB_VERSION%" == "SS2012" SET DB_INTERNAL_VERSION=110 IF "%DB_VERSION%" == "SS2014" SET DB_INTERNAL_VERSION=120 SET DB_INSTALL_HOME=<db_install_home> ::Database Server (Computer Name\Instance Name) :: e.g. ".\SS2012" SET DB_SERVER=<db_server> ::================================================================= :: DB Backup Info ::================================================================= ::Backup Home Directory :: Note: do not put "\" at the end of the backup directory :: e.g. F:\__BACKUP__ SET BACKUP_HOME=<backup_home> ::Backup Mode :: Value Description Default :: ------ ------------------------ ------- :: ALL System and User Database Yes :: SYSTEM System Database :: USER User Database :: :: Note: TempDB will not be backed up SET BACKUP_MODE=ALL ::================================================================= :: SqlCmd ::================================================================= SET SQLCMD_EXE=%DB_INSTALL_HOME%\%DB_INTERNAL_VERSION%\Tools\Binn\sqlcmd.exe ::======================================== :: Execute Backup ::======================================== SET SQLCMD_PARAM=PARAM_BACKUP_MODE="%BACKUP_MODE%" PARAM_BACKUP_HOME="%BACKUP_HOME%" "%SQLCMD_EXE%" -E -S "%DB_SERVER%" -i "SqlServerBackup_WeekOfMonth.sql" -v %SQLCMD_PARAM%

Script #2: SqlServerBackup_WeekOfMonth.sql

--============================================================ -- YL Library : Backup SQL Server Database using SQLCMD.exe -- with Week of Month timestamp (eg. W1) -- Author : Zhang Yunlong -- Blog : ylnotes.blogspot.com -- Filename : 1. SqlServerBackup_WeekOfMonth.cmd -- 2. SqlServerBackup_WeekOfMonth.sql --============================================================ -- History: -- 1.00 Initial Release --============================================================ --========================================================== -- SqlCmd Parameters --========================================================== DECLARE @BACKUP_HOME varchar(255) SET @BACKUP_HOME = LTRIM(RTRIM('$(PARAM_BACKUP_HOME)')) --Backup Mode -- Value Description Default -- ------ ------------------------ ------- -- ALL System and User Database Yes -- SYSTEM System Database -- USER User Database DECLARE @BACKUP_MODE VARCHAR(10) SET @BACKUP_MODE = LTRIM(RTRIM('$(PARAM_BACKUP_MODE)')) IF @BACKUP_MODE = '' SET @BACKUP_MODE = 'ALL' --========================================================== -- Backup File Timestamp --========================================================== DECLARE @Backup_Timestamp CHAR(2) SET @Backup_Timestamp = 'W' + CAST(DATEPART(ww,GETDATE())+1 - DATEPART(ww,DATEADD(dd,-(DATEPART(dd,GETDATE())-1),GETDATE())) AS VARCHAR(1)) --========================================================== -- Database Server Info --========================================================== DECLARE @DbInstanceName VARCHAR(255) SET @DbInstanceName = CAST(SERVERPROPERTY('InstanceName') as VARCHAR(255)) --========================================================== -- Backup All Database --========================================================== DECLARE @DbName VARCHAR(100) DECLARE @BackupFile VARCHAR(255) DECLARE @SQL_DbBackupList VARCHAR(255) SELECT @SQL_DbBackupList = 'INSERT INTO #DatabaseLists SELECT name FROM sys.databases WHERE ' + CASE @BACKUP_MODE WHEN 'ALL' THEN 'name NOT IN (''TempDB'')' WHEN 'SYSTEM' THEN 'name NOT IN (''TempDB'') AND database_id <= 4' WHEN 'USER' THEN 'database_id > 4' END IF OBJECT_ID('TempDB..#DatabaseLists') IS NOT NULL DROP TABLE #DatabaseLists CREATE TABLE #DatabaseLists ( name VARCHAR(255) ) EXECUTE(@SQL_DbBackupList) DECLARE curDb CURSOR FOR SELECT name FROM #DatabaseLists OPEN curDb FETCH NEXT FROM curDb INTO @DbName WHILE @@FETCH_STATUS = 0 BEGIN SET @BackupFile = @BACKUP_HOME + '\' + @DbInstanceName + '_' + @DbName + '_' + @Backup_Timestamp + '.bak' BACKUP DATABASE @DbName TO DISK = @BackupFile WITH FORMAT FETCH NEXT FROM curDb INTO @DbName END CLOSE curDb DEALLOCATE curDb IF OBJECT_ID('TempDB..#DatabaseLists') IS NOT NULL DROP TABLE #DatabaseLists