Automated SQL Server Backups

Question

What are the best practices for creating automated backups of SQL Server 2008 databases?

Backup should take place without using offline / debug database.

Answer 1

I would recommend just creating a maintenance plan in SQL Server for processing backups, you can configure it to backup to a specified location at a specified time without shutting down the databases, and handle incremental backup cleanup.

http://msdn.microsoft.com/en-us/library/ms189715.aspx

Answer 2

If you use SQL Server Express , you will not find a user interface for running periodic backups.
In this case, you need to run the package using scheduled Windows tasks or something similar.

Remember to use a user with sufficient privileges to access SQL Server

In 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 = 10 BACKUP DATABASE MyDataBase2 TO DISK = N'D:\DBbackups\MyDataBase2.bak' WITH NOFORMAT, INIT, NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO 

Answer 3

I struggled with this for a while, because it was not obvious how to work with a mode that created files with different names, so that one launch would not overwrite another. In the end, he created the following Windows batch file

 :: Daily Backup of SQLSERVER databases :: AKC 30 Apr 2011 :: :: Set environment variables SET SQLCMDPASSWORD=xxxxxx SET BACKUPDIR=C:\backups\db\ SET SCRIPTDIR=D:\Public\DB\batch_scripts\ :: Issue backup commands from a sql script SQLCMD -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. Corresponding sql

 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 

Opening the FORFILES command to clean old files was key to me.

Transaction Log Equivalents

 :: 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:\backups\db\ SET SCRIPTDIR=D:\Public\DB\batch_scripts\ :: Issue backup commands from a sql script SQLCMD -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, so I took backups to C :.

A daily backup is entered as a task in the Windows Task Scheduler to run daily at 4 a.m. The transaction log backup is started daily at 8:00 a.m. and repeated every 4 hours after 13:00 (making it work at 8:00 p.m., from 4:00 p.m. to 8:00 p.m. every day).