CJSmith dot me

I dump stuff I find useful here

Compress and move existing Windows 2012 and Microsoft SQL Server 2008 Express backups

Published / by Chris Smith / Leave a Comment

The following has been used to backup a server with Cisco Cubac on. The software runs on Windows 2012 machine and uses SQL Server 2008 Express.
As its not on a managed server I had to find a way to backup the server and the database, plus keep the backup as small as possible.
I get the Windows backup tool to backup once a day and save it to D:\WindowsImageBackup\, I then get the script below to run which then backs up SQL Server 2008 Express separately. I then get the backups compressed with 7zip and move them to a network storage device.
The destination NAS required a username and password, I had to put in a username and password (Not recommended although I believe SAMBA may not have been configured correctly).
If the server was on the same domain as the backup destination then get rid of “/user:username Password” from the net use line, normally having an account with the same username and password should work.

This batch requires RoboCopy (I believe is installed with the OS), 7Zip, a destination device and lots of disc space to fit the backups.

This has been used on a server with Windows 2012 and Microsoft SQL Server 2008 Express to backup a Cisco Cubac install.
The script will call SqlBackup.sql to tell SQL to backup the databases (As it installed with SQL Server 2008 Express).
Everything surrounded by <> needs to be changed, anything calling a directory needs to be checked.
%hostname% is not used for ServerHostName variable, its used for the SQL instance name (Which normally is the hostname).

echo Script runs a manual backup of SQL before moving it to the NAS.
echo Additional files such as backups by Windows are also copied across, but these must be generated by Windows Server Backup.
echo Setting up Variables
Set FileDate=%date:~-10,2%%date:~-7,2%%date:~-4,4%
rem change to SQL instance name
Set ServerHostName=
rem change backup destination
Set NasPath=\\BackupDestination\Cisco_Cubac\
Set Nasoldfiles=%NasPath%\OldFiles\
rem Set where the script runs from
Set BackupScript=C:\Wherethescriptrunsfrom\
Set Sqllocal=
rem Change username and password as required
net use %NasPath% /user:username Password /persistent:no > %BackupScript%logs\%filedate%-MapDrive.log
c:
mkdir %BackupScript%logs
mkdir %BackupScript%7ztemp
echo Run SQL Backup and put output into log file and append with date %FileDate%
sqlcmd -S %ServerHostName% -i %BackupScript%SqlBackup.sql > %BackupScript%logs\%filedate%-SqlBackup.log
echo Compress and move the latest SQL backup to the NAS
"C:\Program Files\7-Zip\7z.exe" a -t7z %BackupScript%7ztemp\%filedate%-FileSystem.7z %Sqllocal%*.bak > %BackupScript%logs\%filedate%-SqlBackupCompress.log
Robocopy %BackupScript%7ztemp\ %NasPath%Sqlbackup *.7z /E /MOVE /V /R:3 /W:5 > %BackupScript%logs\%filedate%-MoveSQLBackup.log
del %Sqllocal%*.* /Q > %BackupScript%logs\%filedate%-DeleteSQLBackup.log
echo Compress and move Windows backup from drive to NAS and remove old files
d:
mkdir D:\7ztemp
rem Change "D:\WindowsImageBackup\" to where the Windows backup is located
"C:\Program Files\7-Zip\7z.exe" a -t7z D:\7ztemp\%filedate%-ServerBackup.7z "D:\WindowsImageBackup\" > %BackupScript%logs\%filedate%-WinBackupCompress.log
Robocopy D:\7ztemp %NasPath%Winbackup\%filedate%\ *.* /E /MOVE /V /R:3 /W:5 > %BackupScript%logs\%filedate%-MoveWinBackup.log
rmdir "D:\WindowsImageBackup\" /S /Q >> %BackupScript%logs\%filedate%-DeleteWinBackup.log
mkdir "D:\WindowsImageBackup\" >> %BackupScript%logs\%filedate%-DeleteWinBackup.log
echo Remove old Files on NAS by using Robocopy to copy files over a certain age into a specific folder
mkdir %Nasoldfiles%
ROBOCOPY %NasPath%Sqlbackup %Nasoldfiles% /move /minage:7 >> %BackupScript%logs\%filedate%-NasRemoveOldFiles.log
ROBOCOPY %NasPath%Winbackup %Nasoldfiles% /E /move /minage:7 >> %BackupScript%logs\%filedate%-NasRemoveOldFiles.log
ROBOCOPY %NasPath%Backuplogs %Nasoldfiles% /move /minage:7 >> %BackupScript%logs\%filedate%-NasRemoveOldFiles.log
del %Nasoldfiles%*.* /Q > %BackupScript%logs\%filedate%-NasDeleteOldFiles.log
echo Move Backupscript logs to NAS to complete job
Robocopy %BackupScript%logs\ %NasPath%Backuplogs\ *.* /E /MOVE /V /R:3 /W:5

This second file (SQL Backup File for Microsoft SQL Server 2008 Express) is called separately, above I have called it SqlBackup.sql in the folder specified in variable BackupScript.

BACKUP DATABASE [ATTCFG] TO  [FileSystem] WITH NOFORMAT, NOINIT,  NAME = N'ATTCFG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [ATTLOG] TO  [FileSystem] WITH NOFORMAT, NOINIT,  NAME = N'ATTLOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [master] TO  [FileSystem] WITH NOFORMAT, NOINIT,  NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [model] TO  [FileSystem] WITH NOFORMAT, NOINIT,  NAME = N'model-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [msdb] TO  [FileSystem] WITH NOFORMAT, NOINIT,  NAME = N'msdb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Leave a Reply