Sql-server – Automated SQL backup on a timely fashion, & cleaup the database for the backed up data

backupsql serversql-server-2008-r2

I need to back up SQL database (historian), on a timely fashion, and then clean up the database by removing the backed up data.

I am using MS SQL 2008 (R2), on a Windows XP machine. The biggest issue is the very limited hard disk space. The database is limited to a maximum of 3GB! In terms of overall performance, the PC is really slow, and unfortunately I do not have the choice to change that. So, I could consider backing up overnight when the data flow is expected to be less.

The intention is to back up the data every two weeks, have it stored in a special directory (e.g. c:\ ). Then an operator can move the backup to another machine. Given the limited space, I could consider some 'house clean up', by removing the backed up data. What is more important is the ability to merge the regular backups to an external database. So perhaps a typical SQL backup routine and restore, could be an option.

I would appreciate your kind advice regarding this matter. Thank you.

Best Answer

Instead of rewriting your own solution, my suggestion is to use SQL Server Maintenance Solution - SQL Server Backup

Edit: Below script will help you once you deploy Ola's script and create required objects:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', -- put the databases you want to backup
@Directory = 'C:\Backup',      -- change the backup directory to suit your needs
@BackupType = 'FULL',          -- This will take FULL database backups
@Verify = 'Y',                 -- This will verify the backups
@Compress = 'Y',               -- Since you are using SQLServer2008R2, standard and enterprise both have backup compression available. 
@CheckSum = 'Y',
@CleanupTime = 24              -- This will delete the backup older than 24 hrs !!
Related Question