Tips, Tricks, and Insight into Process and Power Software

Creating Backups for SQL Express

This entry is part 3 of 5 in the series AutoCAD Plant 3D on SQL Express

Once you have your database server up and running and connected to user computers, you have to figure out a way to do backups.

 

While you can manage backup through Management Studio, I was able to find and modify some scripts that suit our purpose – maintaining 2 week backup of all of the projects.

image

Here is a screen shot of the backup options for a database.  You can set the backup type to full or database and indicate whether the backup should expire.

SNAGHTML15ca04b

I was able to find some scripts to automate the process.

http://www.codecapers.com/post/How-to-Backup-a-SQL-Express-Database.aspx

I got help from another link for the 7 day purge, but I can’t find where that was.

Anyhow let’s look at the relevant parts.

The BACKUP_DIR is the share folder where the backups should be stored.

SERVER is the server name.

The script gets the current day and will create files like YYYY-MM-DD – HHMM Databasename-Full.bak

@echo off
::SET BACKUP_DIR=C:\SQL\ServerBackups
SET BACKUP_DIR=\\WIN-SC-SRV1\Downloads\SQLData
SET SERVER=localhost
for /f "tokens=2" %%d in ('echo %date%') do (
for /f "tokens=1-3 delims=/" %%j in ('echo %%d') do (
set month=%%j
set day=%%k
set year=%%l
))
set /a day=%day%-1
if %day% lss 10 (
set day=0%day%
)
::set year=%year:~2,3%
::set year=20%year%
set mydate=%year%-%month%-%day%
for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a%%b
sqlcmd -S %SERVER% -d master -Q "exec sp_msforeachdb
  'BACKUP DATABASE [?] TO DISK=''%BACKUP_DIR%\%mydate% - %XTIME% ?-Full.bak'''"

The next script deletes the .bak files from the backup folder.  You set the hold length in days, the folder to cleanout (purgedir) and the extension to clear out (purgeext).

Because forfiles doesn’t work for share names, the script uses PushD to map the share to an open drive letter and then runs the purge via the command line.

::change holdlength to match hold long you want to keep your backups
set holdlength=7
set purgedir=\\WIN-SC-SRV1\Downloads\SQLData
set purgeext=bak

PushD %purgedir% &&(

forfiles /s /m *.%purgeext% /c "cmd /c del @path " /d -%holdlength%
) & PopD

 

[Download not found]

To run these files, use Windows Tasks Scheduler.

http://windows.microsoft.com/en-US/windows7/schedule-a-task

http://www.windowsnetworking.com/articles_tutorials/Working-Windows-Server-2008-Task-Scheduler-Part1.html

http://www.windowsnetworking.com/articles_tutorials/Working-Windows-Server-2008-Task-Scheduler-Part2.html

 

My recommendation is to set the trigger daily for 11:00 pm or so.  For Action, choose start a program and navigate to where you have saved the batch files. I created a folder C:\SQL\ and saved the .bat files in there.

Next we will look at migrating a project to SQL Server.

Series NavigationInstalling SQL Server ExpressConnecting to SQL Server