SQL Server Automated Backups

"C:Program FilesMicrosoft SQL Server100ToolsBinnSQLCMD.EXE" -S
(local)SQLExpress -i D:dbbackupsSQLExpressBackups.sql

BACKUP DATABASE MyDataBase1 TO  DISK = N'D:DBbackupsMyDataBase1.bak'
WITH NOFORMAT, INIT,  NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE MyDataBase2 TO  DISK = N'D:DBbackupsMyDataBase2.bak'
WITH NOFORMAT, INIT,  NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

:: Daily Backup of SQLSERVER databases

:: AKC 30 Apr 2011

::

:: Set environment variables

SET SQLCMDPASSWORD=xxxxxx
SET BACKUPDIR=C:backupsdb

SET SCRIPTDIR=D:PublicDBbatch_scripts

:: Issue backup commands from a sql script

SQLCMD -U a_backup -S SERVERSQLEXPRESS -i %SCRIPTDIR%daily_backup.sql

:: Tidy Up Old Backup Files (keep for 5 days)

FORFILES /P %BACKUPDIR% /S /M "*.bak" /D -5 /C "cmd /c del @path"

DECLARE @thistime nvarchar(25);

DECLARE @filename nvarchar(255);

SET @thistime = CONVERT(nvarchar,GETDATE(),126);

SET @filename = "$(BACKUPDIR)" + N'PASL' + SUBSTRING(@thistime,1,10) + N'_DB.bak';

BACKUP DATABASE DB_live

    TO DISK = @FILENAME

    WITH INIT;

GO

:: Transaction Log Backups of SQLSERVER databases

:: AKC 30 Apr 2011

:: Run at reasonably spread out times of the day

:: Set environment variables

SET SQLCMDPASSWORD=xxxxxx
SET BACKUPDIR=C:backupsdb
SET SCRIPTDIR=D:PublicDBbatch_scripts

:: Issue backup commands from a sql script

SQLCMD  -U a_backup -S SERVERSQLEXPRESS -i %SCRIPTDIR%tlog_backup.sql

DECLARE @thistime nvarchar(25);

DECLARE @filename nvarchar(255);

SET @thistime = CONVERT(nvarchar,GETDATE(),126);

SET @filename = "$(BACKUPDIR)" + N'PASL' + SUBSTRING(@thistime,1,10) + SUBSTRING(@thistime,11,3) + N'_LOG.bak';

BACKUP LOG DB_live

    TO DISK = @FILENAME

    WITH INIT;

GO