May 25, 2011 at 10:07 am
I have a single user database that will be coming onboard shortly - I have a plan in mind on how I want to schedule the backups, but I'm trying to figure out the best way to do it.
I'd like to do a Full backup on Friday PM and Sunday PM (I'd like to do general DB maintenance on Sat), Differentials Tuesday - Thursday and Transaction Log Backups every hour 7am-7pm Mon-Fri.
I'm no expert on this, but in theory I'd like to keep the last FULL/DIFF/TRN on disk each with their own file with timestamp. Any/all files get backed up to tape daily. I have plenty of space to keep the backups on disk locally longer than that, so we'll see how often I'll clear them.
FULL
declare @backupfile nvarchar(2000)
set @backupfile = N'Z:\Backupewdb_FULL' + replace(convert(nvarchar(50), getdate(), 112), ' ',':') + N'.BAK'
BACKUP DATABASE [newdb] TO DISK = @backupfile WITH INIT , NOUNLOAD , NAME = N'newdb backup', NOSKIP , STATS = 10, NOFORMAT
DIFF
declare @backupfile nvarchar(2000)
set @backupfile = N'Z:\Backupewdb_DIFF' + replace(convert(nvarchar(50), getdate(), 112), ' ',':') + N'.DIFF'
BACKUP DATABASE [newdb] TO DISK = @backupfile WITH DIFFERENTIAL, NAME = N'newdb backup'
TRN
declare @backupfile nvarchar(2000)
set @backupfile = N'Z:\Backupewdb_TRN' + replace(convert(nvarchar(50), getdate(), 112), ' ',':') + N'.TRN'
BACKUP LOG[newdb] TO DISK = @backupfile WITH NOINIT, NAME = N'newdb backup', STATS =10
Question is, are these stamements correct to get what I'm looking for? Or is my idea wrong to begin with 😎
May 25, 2011 at 10:07 am
Hmm...sorry...not that it matters but the code is taking out the '\ n' for the backup dir.
May 25, 2011 at 2:22 pm
you could achieve this via a maintenance plan.
---------------------------------------------------------------------
May 25, 2011 at 2:26 pm
george sibbald (5/25/2011)
you could achieve this via a maintenance plan.
I don't see an option to time stamp a file with a maintenance plan.
May 25, 2011 at 2:50 pm
its not made obvious but it will timestamp them, you can also use the maintenance cleanup task to delete them by date, which your code does not cater for.
dont use the backupset option - backup to file
---------------------------------------------------------------------
May 26, 2011 at 7:55 am
Is there anything wrong with what I'm doing - specifically with the transaction log backups?
With Maintenance Plan transcational backups, yes it does a timestamp, but I'd rather have a single file with the daily transcational backups appended to it for each day. If I do append with the Maintenance Plan, won't it just continue to append to the same file all week?
May 26, 2011 at 8:33 am
Maintenance plans will create a single file for each backup.
I avoid multi file backupsets like the plague. I see no advantages to using them, it means having to work out which file in the backupset you want when doing restores.
---------------------------------------------------------------------
May 26, 2011 at 6:40 pm
I also avoid them - all it takes is a misplaced INIT command and you have just lost all of your transaction log backups.
Having a single file is no different than having a single folder - each just becomes a container for the individual files/backups. And, it is much easier to manage files in a folder than backups in a file.
The maintenance plans can be setup so they create a folder for each database - and all backup files will go to that folder. The cleanup utility can be configured to cleanup files by databases in each folder.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply