Backup Plan

Question

Backup Plan

johns10t

SSC Rookie

Points: 26
More actions
October 24, 2011 at 7:39 am

#259156

I'm trying to get an understanding of how my backup plan will work. I set one up last week to have it perform a full backup on Sunday, but the results were unexpected.
The previous administrator had a backup job that would create a backup file for every database and then had a job to clean up those files after 4 weeks. This was fine, but by selecting that option, the backups append, and the backup files kept getting larger each week and finally caused the disk to run out of space.
Here's the plan I created:
-Full backup
-Specific databases
-Backup set will expire after 30 days
-Backup to disk
-Back up databases across one or more files
-Scheduled every Sunday
My problem was that I set up multiple files thinking it would write the full backup to one file the first week, then the next file the second week and so on. Found today, it breaks the backup into however many files you list.
Anyway, my goal is to have the plan create a new file each week, but not grow the way that the prior files had. I'd also like to do the same for the log file backup. Any help is appreciated!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

cindyaz Ten Centuries Points: 1229 More actions · Answer 1

append a timestamp to the filename. example below adds only date. you can also add time to the filename. everytime the script runs, it'll generate new backup files.

strFile = 'backup database mydb to disk=mydb_full'+convert(varchar,getdate(),112)+'.bak'

sp_executesql strFile

johns10t SSC Rookie Points: 26 More actions · Answer 2

Thanks for the response. Not that it's difficult, but I was expecting less of a 'custom' option.

If I have it set to overwrite existing, and have the expiration set out 30 days, will the backup simply fail because there is no file it is allowed to create the backup to, or will the overwrite cancel out the expiration?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you want one file per backup, the maintenance plan will do that, though most people recommend not generally using the maint plan.

This set of options will create one file per backup in the folder specified, each file timestamped. If you check the 'sub directory per database', then the backups will be created in a folder for that DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

johns10t (10/24/2011)
If I have it set to overwrite existing, and have the expiration set out 30 days, will the backup simply fail because there is no file it is allowed to create the backup to, or will the overwrite cancel out the expiration?

Expiring and overwrite expiring are more for tape backups than disk. No need for either with backups to individual files. You can use the maintenance cleanup task to delete old backup files

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

johns10t SSC Rookie Points: 26 More actions · Answer 5

I had a job that was set up like your example, using a cleanup job as well, I'm not sure if that's what led to my problem or if it's something else. Somewhere online I read that using the other options in the maintenance plan would solve my growing backup files, and that there wouldn't be a need to deal with a cleanup plan.

Part of my initial problem seemed as though it was caused by the backups, but the other part seemed as though it was due to the log file (and associated backup of log file) being 8x larger than the actual database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

johns10t (10/24/2011)
Part of my initial problem seemed as though it was caused by the backups, but the other part seemed as though it was due to the log file (and associated backup of log file) being 8x larger than the actual database.

That's a whole different type of problem.

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

johns10t SSC Rookie Points: 26 More actions · Answer 7

GilaMonster (10/24/2011)
johns10t (10/24/2011)
Part of my initial problem seemed as though it was caused by the backups, but the other part seemed as though it was due to the log file (and associated backup of log file) being 8x larger than the actual database.
That's a whole different type of problem.
http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Thank you. I'll give this a read and change my backup plan back to how it was.

sindhigrl Default port Points: 1448 More actions · Answer 8

I think this is what you are trying to do. This is what I use in a SQL job, the first step takes a full backup and appends a date stamp to the name. Step two goes out onto the disk and deletes any backup file older than 2 days(you can set it to 30 days) I do the same with my log backups:

Step 1:

SET NOCOUNT ON

DECLARE@backupfile varchar(1000)

SELECT@backupfile = 'L:\Backups\DatabaseName_BACKUP_'+CONVERT(VARCHAR,GETDATE(),112)+'.BAK'

BACKUP DATABASE DatabaseName

TO DISK = @backupfile

WITHINIT,

STATS = 10,

NAME = 'FULL BACKUP OF DatabaseName',

DESCRIPTION = 'FULL BACKUP OF DatabaseName'

Step 2:

SET NOCOUNT ON

DECLARE@command VARCHAR(1000)

DECLARE@currentfile VARCHAR(500)

-- Create a temporary table

CREATE TABLE #output (

txtOutput VARCHAR(1000) )

-- Get those database backups that are present on disk

SELECT@command = 'dir L:\Backups\DatabaseName_BACKUP_*.BAK /B'

INSERT INTO #output (txtOutput)

EXECmaster.dbo.xp_cmdshell @command

-- Delete unusful information

DELETE#output

WHEREtxtOutput IS NULL OR

txtOutput LIKE 'file not found' OR

txtOutput LIKE 'DatabaseName_BACKUP_' + CONVERT(VARCHAR,GETDATE(),112) + '.BAK'

OR txtOutput LIKE 'DatabaseName_BACKUP_' + CONVERT(VARCHAR,DATEADD(dd,-1,GETDATE()),112) + '.BAK'

-- Delete old database backups

IF( SELECT COUNT(*) FROM #output ) > 0

BEGIN

DECLARE c_file CURSOR FOR

SELECTtxtOutput

FROM#output

OPEN c_file

FETCH NEXT FROM c_file INTO @currentfile

WHILE @@FETCH_STATUS >= 0

BEGIN

SELECT@command = 'DEL L:\Backups\' + @currentfile

EXECmaster.dbo.xp_cmdshell @command, no_output

-- Report to what logfile that has been deleted

PRINT'DELETED FILE: L:\Backups\' + @currentfile

FETCH NEXT FROM c_file INTO @currentfile

END

CLOSE c_file

DEALLOCATE c_file

END

ELSE

BEGIN

-- Report if no files were deleted

PRINT'NO FILES DELETED'

END

Thanks

Anita

Thanks!