Jul
28th
Schedule Microsoft SQL Server Express R2 Databases Using SQLCMD with current date and time
Justin Jacob on July 28th, 2011
One of my server using Microsoft SQl Express R2 ,i need to schedule the backup of all databases,follow the step
1.Create a bat file mssqlbackup.bat
copy the following code
@ECHO OFF
SETLOCAL
set folder=%date:~-4%-%date:~4,2%-%date:~7,2%
echo %folder%
SQLCMD -E -S (servername) -Q “BACKUP DATABASE (database name) TO DISK=’D:\Daily_Backup\MSSQL_BACKUP\(database name)%folder%.bak’ WITH NOFORMAT, INIT, NAME =N’ full backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
echo delete more than 3 day old backup
SETLOCAL
set folder=%date:~-4%-%date:~4,2%-%date:~7,2%
echo %folder%
SQLCMD -E -S (servername) -Q “BACKUP DATABASE (database name) TO DISK=’D:\Daily_Backup\MSSQL_BACKUP\(database name)%folder%.bak’ WITH NOFORMAT, INIT, NAME =N’ full backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
echo delete more than 3 day old backup
forfiles /p D:\Daily_Backup\mssqlbackup /s /m *.bak /d -3 /c “cmd /c del @file”
my backup file is the flowing style
@ECHO OFF
SETLOCAL
set folder=%date:~-4%-%date:~4,2%-%date:~7,2%
echo %folder%
SQLCMD -E -S COMPUTECH -Q “BACKUP DATABASE computech_test TO DISK=’D:\Daily_Backup\MSSQL_BACKUP\computech_test%folder%.bak’ WITH NOFORMAT, INIT, NAME =N’computech full backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
echo delete more than 3 day old backup
SETLOCAL
set folder=%date:~-4%-%date:~4,2%-%date:~7,2%
echo %folder%
SQLCMD -E -S COMPUTECH -Q “BACKUP DATABASE computech_test TO DISK=’D:\Daily_Backup\MSSQL_BACKUP\computech_test%folder%.bak’ WITH NOFORMAT, INIT, NAME =N’computech full backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10″
echo delete more than 3 day old backup
forfiles /p D:\Daily_Backup\mssqlbackup /s /m *.bak /d -3 /c “cmd /c del @file”
Last line of code is to delete more than 3 days old file you can increase date or delete the line
Schedule the bat file