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.
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.
I was able to find some scripts to automate the process.
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
Backup SQL Databases (7 downloads)
To run these files, use Windows Tasks Scheduler.
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.