1

I'm (trying) managing a large number of sql server instances among a number of machinces running SQL Server 2005 and 2008. Almost all the DB are in Simple Recovery Model. I'm doing full backups every Sunday and differencial backups every day. Even though I am using compressed backups, there are databases which are nearly a TB. Therefore, I need to have a proper use of the backups regarding the space they are allocating.

My goal: I just need to have one full and one differencial backup for each database every single moment.

My question : What actions should I perform in order to achieve my goal? I guess something i need to do with the expiration of the backups and the "Clean up history", but I am not sure what exactly i need to do.

0

Method1: The Best method to achieve this would be using Olla hallengren Backup scripts.

You can find the scripts at https://ola.hallengren.com/downloads.html

First thing you need to do is execute CommandExecute.sql script and then run the databasebackup.sql script.

Once done you can execute below via SQL Agent job as per you're required schedule:

  EXECUTE dbo.DatabaseBackup
  @Databases = 'USER_DATABASES',
  @Directory = 'C:\Backup',
  @BackupType = 'FULL',
  @Verify = 'Y',
  @Compress = 'Y',
  @CheckSum = 'Y',
  @CleanupTime = 24

Here Backup type can be changed from FULL, DIFF to LOG(If any DB in full recovery model exist) as per the need. And fro @ cleanup time- Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

Refer to below link on further details:

https://ola.hallengren.com/sql-server-backup.html

Method 2: You can always refer to Maintenance plans for clean up within SQL server

Use below query as an example to delete the old backup files as per the need:.

 EXECUTE MASTER.dbo.xp_delete_file 0,N'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup',N'bak',N'2014-09-30T10:14:59'
GO

Refer to below link for using setting up cleanup task.

http://www.biztalkadminsblogging.com/index.php/blog-archive/item/20-automatically-remove-old-backup-files

  • Thanks for the method 1. I was not aware of it. However, I am not an experienced DBA and I'm filling more comfortable with the Method 2. Could you please give me more info about the Method 2? – Giannos Antoniou Feb 12 '15 at 10:23
  • @GiannosAntoniou; If you are comfortable with Maintenance plans, then i have edited my answer with script you can use via sql agent or refer to the link to create a maint'ce plan of you're own to purge old files. But my personal expnce says OLlas scripts are very easy to use and quite reliable. Rest you can choose. Hope it helps! Thanks – KASQLDBA Feb 12 '15 at 10:46
  • 1
    @GiannosAntoniou Do yourself a favor and read the documentation on Ola's scripts, they are well documented on his page. If you are managing a large environment, you'd be well served to acquaint yourself with these to make life easier. The setup is easy, at a minimum just change the path for the backups in the script and run it to create the jobs. Then it is just down to modifying the parameters of the jobs to meet your needs. Using a common standard that is well documented saves you a lot of time and makes sharing support easier in the future. – Your_comment_is_not_funny Feb 12 '15 at 15:11
  • @GiannosAntoniou: If question has been answered you can upvote as helpful and mark as answer. Thanks! – KASQLDBA Feb 13 '15 at 11:01
  • Is the Hallengren approach still the industry standard now? (2016-08-31) Or does SQL Server 2014 have new features that can replace it? – Wouter Aug 31 '16 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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