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.

  • 1
    You're talking of limited space, but you also expect to have an operator remove the old backups to another location. Why not automate this task? Apr 2, 2013 at 13:29

4 Answers 4


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 !!
  • Thank you for your kind response. I have considered the Maintenance solution, but have not invested enough time to explore all the options, yet. With regards to the script you have provided, it looks great, but there are several points I will bring to your attention: 1) I do not intend to do Full backup. The backup is meant to capture say, the last two weeks (a configuratble parameter), whereas the database may contain 3 months' worth of data. 2) How reliable is the clean up method? Have you used it extensively? By the way, I am not using R2, just 2008. I look forward to your response.
    – user22040
    Apr 5, 2013 at 1:47
  • For 1 ==> You can do a differental backup as well, to just get the changes from the last full backup. But if the database size is small, then you can go for FULL backup. If you intend to clean up the files, make sure that the full backup is not deleted. You can develop a strategy to take FULL backup every week and then differential backups for rest of the week (depends on what you need). For 2 ==> Yes, I have been using it for years and it works flawless.
    – Kin Shah
    Apr 5, 2013 at 15:11
  • Regarding point 1) Thank you for the information. I was after more flexibility. I will definitely utilize your script, and see what other keywords I can use. As for point 2) do you what the maximum value can be, instead of 24? For example can it be equivalent to several weeks or months' time?
    – user22040
    Apr 8, 2013 at 8:24
  • If you look at the documentation link that I mentioned, CleanupTime ==>Specify the time, in hours, after which the backup files are deleted. So if you want to cleanup your backups after 7 days, set the cleanup time = 168Hrs and so on.
    – Kin Shah
    Apr 8, 2013 at 13:21

You could back up the databases straight to a network drive.

backup database AdventureWorks2008R2 to disk = '\\fileserver\backups\AdventureWorks2008R2_full.bak' ;

Use a UNC path (\server\share). Using a a mapped network drive requires that the drive be mapped in the profile of the account running the backup (usually the MSSQLSERVER service account).

THe account running the backup will, of course, need write permission on the shared folder on the file server machine.

As an aside, you say this is running on Windows XP. I have two comments on this.

First, push back on your customer/employer as hard as you dare that if they try and run server services on a workstation then they will get grief. There is a reason Microsoft have server and workstation versions of their OS.

Second, it might pay you to check the edition of SQL Server (select serverproperty( 'edition' ) ;). If it shows "Developer Edition" then your company might be in violation of the license agreement.

  • I like the UNC option. Thank you. As for the O/S, I have tried really hard. The answer I've got has been "deal with it", I am afraid. As for the edition, it is not a developer edition, and as you suggested that would be in violation of licence agreement if it were the case. The edition I am using is only meant to be for the purpose of historian, and one must not use it for any other purpose. Thank you for the reminder, anyways. Please provide any other suggestions you may have.
    – user22040
    Apr 5, 2013 at 1:55

Try our SQLBackupAndFTP tool. You can run scheduled backups of SQL Server databases (full, differential or transaction log), zip and encrypt the backups, store them on a network, FTP server, Dropbox, Box, Amazon S3, Google Drive or SkyDrive, send yourself an e-mail confirmation on job success of failure. Also you can configure cleaning up old backups.

  • 2
    Thank you for the offer. I have already seen the product, and its screenshots, but am not sure as to what it can offer in comparison to what one can acheive using scripts, only. Please enlighten me!
    – user22040
    Apr 8, 2013 at 8:17
  • 2
    Sure, you can create scripts to automate backups, compress and encrypt backups, send them on a storage, cleanup old backups (and email notifications if backup procedure was failed) and other tasks... But how about efforts to create and support all these scripts? You'll just save time with SQLBackupAndFTP.
    – Alexey
    Apr 8, 2013 at 19:03

Try using backup programs which use Virtual Device Interface to compress the backup stream 'on the fly'. It saves the strorage space. I would recommend EMS SQL Backup which also has the ability to schedule cleanups as well as compressed backup and allows uploading backup files to network locations and clouds.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.