SQL Server Automated Backups [closed]
If you are using SQL Server Express, you won't find a UI to run periodic backups.
In this case you have to run a batch using Windows Scheduled Tasks or something similar.
Don't forget to use a user with enough privileges to access SQL Server.
In the batch file
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S (local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql
In SQLExpressBackups.sql
BACKUP DATABASE MyDataBase1 TO DISK = N'D:\DBbackups\MyDataBase1.bak' WITH NOFORMAT, INIT, NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10BACKUP DATABASE MyDataBase2 TO DISK = N'D:\DBbackups\MyDataBase2.bak' WITH NOFORMAT, INIT, NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO
I would recommend just creating a maintenance plan in SQL Server to handle the backups, it can be configured to backup to a specified location at specified times, without taking the databases offline, and will handle your incremental backup cleanup.
I struggled with this for a while because it wasn't obvious how to work a regime that produced files with different names so that one run didn't over write the other. In the end it created the following Windows batch file
:: Daily Backup of SQLSERVER databases:: AKC 30 Apr 2011:::: Set environment variablesSET SQLCMDPASSWORD=xxxxxxSET BACKUPDIR=C:\backups\db\SET SCRIPTDIR=D:\Public\DB\batch_scripts\:: Issue backup commands from a sql scriptSQLCMD -U a_backup -S SERVER\SQLEXPRESS -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"
where a_backup is my sqlserver login with backup privileges. The corresponding sql is
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
The discovery of "FORFILES" command to purge older files was the key finding for me.
The transaction logs equivalents are
:: Transaction Log Backups of SQLSERVER databases:: AKC 30 Apr 2011:: Run at reasonably spread out times of the day:: Set environment variablesSET SQLCMDPASSWORD=xxxxxxSET BACKUPDIR=C:\backups\db\SET SCRIPTDIR=D:\Public\DB\batch_scripts\:: Issue backup commands from a sql scriptSQLCMD -U a_backup -S SERVER\SQLEXPRESS -i %SCRIPTDIR%tlog_backup.sql
with sql file
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
I should note that the database files are on my D: drive, which is why I took the backups onto the C: drive.
The Daily backup is entered as a job in the Windows Task Scheduler to run daily at 4:00am. The transaction log backup is set to run daily at 8:00am with a repeat every 4 hours finishing after 13 hours (causing it to run at 8am Midday, 4pm and 8pm every day)