How to backup SQL Express 2005 database - Script

Environment

• Microsoft SQL Express 2005

Content
To backup SQL Express 2005 database:
1 - Set the BackupDB.sql.

2 - Set the BackupDB.bat to run BackupDB.sql.

3 - Run a powershell to auto delete older database backup.

BackupDB.sql

DECLARE @filename varchar(400)
DECLARE @zippedFile varchar(400)
DECLARE @date varchar(20)
DECLARE @time varchar(20)

SET @date = Convert(varchar(12),getdate(),112)
SET @time = Convert(varchar,getdate(),108)

SET @time = substring(@time,1,2) + substring(@time,4,2)
SET @filename ='Y:\Backup_DB_' + @date + '.dat'
SET @zippedFile = @filename + ".zip"


PRINT '<< Start to backup database(s) ... >>'

/*
* Only can run on MSSQL 2005 or previous version
*
* http://dba.stackexchange.com/questions/22116/mapped-drive-for-microsoft-sql-server-2008
*/

/*
* If not run net use in transact SQL:
* Cannot open backup device 'Y:\Backup_DB_20140109.dat'. Device error or device off-line. See the SQL Server error log for more details.
*/
EXEC XP_CMDSHELL 'net use Y: "\\ServerA\DB_BACKUP$" "Password" /user:Domain\DB_BACKUP /persistent:no'

DECLARE @fileExist int
DECLARE @command varchar(400)

/*
* @ref EXECUTE permission denied on object 'xp_cmdshell'
* http://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell
*/
SET @command = 'dir ' + @zippedFile + ' /B'
EXEC @fileExist = XP_CMDSHELL @command, NO_OUTPUT
IF @fileExist = 1
BEGIN
BACKUP DATABASE databaseA
TO DISK= @filename

SET @command = '"C:\Program Files\7-zip\7z.exe" a ' + @zippedFile + ' ' + @filename
EXEC XP_CMDSHELL @command

SET @command = 'del /q ' + @filename
EXEC XP_CMDSHELL @command

SET @command = 'Backup done. File: ' + @zippedFile
PRINT @command
END
ELSE
BEGIN
SET @command = 'File, ' + @zippedFile + ', exits. Backup stopped'
PRINT @command
END



EXEC XP_CMDSHELL 'net use Y: /delete /yes'

PRINT '<< Backup database(s) finished >>'

BackupDB.bat

@echo off

osql -E -i BackupDB.sql

Delete older SQL daabase backup media.

C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe & 'C:\bin\del older SQLDB backup.ps1'

$limit = (Get-Date).AddDays(-30)

$sqlBackupPath = 'D:\Backup\DatabaseA\'

Get-ChildItem $sqlBackupPath | ? {
-not $_.PSIsContainer -and $_.LastWriteTime -lt $limit -and $_.Name -like '*.zip'
} | Remove-Item

Update