2012-11-16

I'm new to Recorder and most folk will have sorted their backup arrangements, but I thought I'd share what I found...

The usual vehicle for scheduling operations such as backups in Microsoft SQL Server is SQL Server Agent, but Microsoft have not made this available in SQL Express, at least not in the 2008 R2 edition that I'm using. SQL Server Agent does appear in SQl Server Configuration Manager, but will not start.

This Microsoft article explains how to get around the problem http://support.microsoft.com/kb/2019698.

What I've done is adapt Microsoft's solution to my set-up and I explain it here in case it's of any use to others...

Recorder version: 6.17.2.251
Installation type: Network
SQL Server version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) Express Edition with Advanced Services
Database Server O/S: Microsoft Windows Server 2008 Enterprise version 6.0.6002 SP2
Local setup: Microsoft Windows Domain network with single domain controller running Windows Server 2003 SBS
Backup device: Buffalo LinkStation NAS

WARNING 1: THIS METHOD IS APPROPRIATE FOR SIMPLE RECOVERY MODEL ONLY
WARNING 2: USE AT YOUR OWN RISK

1) Setup SQl admin Windows domain user who owns NBNData database or has similar privileges
2) Create a new database DBAdmin for admin purposes only
3) In SQL Server Management Studio, set the recovery model of the NBNData database to Simple
4) In SQL Server Management Studio, create 2 backup devices for NBNData: One for Sun-Fri and one for Sat
5) Write sql script (loosely based on Microsoft's one) to create a stored procedure which will use a T-SQL command to  backup a database. Run script to create the stored procedure.
6) Try out the stored procedure by running it from SQL Server Management Studio
7) Test stored procedure will create both full and differential backups and will overwrite or append as required
8) Write batch file that uses sqlcmd command to backup the NBNDatabase and try it
9) Write batch file that uses sqlcmd command to do a full backup of the NBNDatabase and overwrites the backup file. This will be run each Sunday
10) Write batch files (a) to do differential backup to be appended to above file to be run Mon-Fri (b) to do a full backup to be written to a separate backup file (overwritten) to be run each Saturday
11) Test each batch file
12) In Windows Task Scheduler on the database server, setup tasks to run each batch file as appropriate.
13) Check the backup files - i.e. perform a restore on a test system (I haven't done this yet)

PS I wrote out the backup plan and schedule before implementing it (because I get confused easily)

I will attempt to put the SQL script in the Downloads forum now...

Show more