[ACCEPTED]-SQL Server Automated Backups-backup

If you are using SQL Server Express, you won't find a UI to 5 run periodic backups.
In this case you have 4 to run a batch using Windows Scheduled Tasks 3 or something similar.

Don't forget to use 2 a user with enough privileges to access 1 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 = 10

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

GO

I would recommend just creating a maintenance 5 plan in SQL Server to handle the backups, it 4 can be configured to backup to a specified 3 location at specified times, without taking 2 the databases offline, and will handle your 1 incremental backup cleanup.

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

I struggled with this for a while because 18 it wasn't obvious how to work a regime that 17 produced files with different names so that 16 one run didn't over write the other. In 15 the end it created the following Windows 14 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 13 login with backup privileges. The corresponding 12 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 11 to purge older files was the key finding 10 for me.

The transaction logs equivalents 9 are

:: 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 8 files are on my D: drive, which is why I 7 took the backups onto the C: drive.

The Daily 6 backup is entered as a job in the Windows 5 Task Scheduler to run daily at 4:00am. The 4 transaction log backup is set to run daily 3 at 8:00am with a repeat every 4 hours finishing 2 after 13 hours (causing it to run at 8am 1 Midday, 4pm and 8pm every day)