October 20, 2010 at 1:00 pm
SkyBox (10/20/2010)
Thank you Eights...
You mean Wayne?
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
October 20, 2010 at 1:08 pm
GilaMonster (10/20/2010)
SkyBox (10/20/2010)
Thank you Eights...You mean Wayne?
Yes, I meant thank you Wayne.
October 20, 2010 at 2:24 pm
pavan_srirangam (10/19/2010)
you are in aright track.But it would be better if you take differential every one hour and and set up clean up task to delete old differential backups so that in case of system failure
you need restore only one full backup --> one diffrential --> four tran log backups.
Thats it ...other wise you need to restore plenty tran backups since last differential backups.
Would like some other opinions on hourly intervals for the differentials. I agree with Pavan in the sense that this is the cleanest and quickest approach, but considering my db is 500gb could this hinder performance?
There is plenty of activity/changes that would be occuring between the differentials.
October 20, 2010 at 2:35 pm
SkyBox (10/20/2010)
Would like some other opinions on hourly intervals for the differentials. I agree with Pavan in the sense that this is the cleanest and quickest approach, but considering my db is 500gb could this hinder performance?There is plenty of activity/changes that would be occuring between the differentials.
I personally wouldn't be doing differentials every hour unless I had an insane SLA. 1-2 full/week, maybe 2 diffs a day, and t-log backups every 15-30 minutes. Depends on the amount of data being modified.
If you're looking for speedier recovery times, I'd recommend doing diff backups before and after maintenance loads (I assume your system also does overnight bulk loading). Do a diff backup before that starts, so if bad loads/whatnot comes in you can recover. Do another immediately after so when you restore because of problems during the day all your bulk work is already done, and you just need to cover the T-logs since the beginning of the workday.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 21, 2010 at 8:12 am
Just a word of warning about Diff backups. They incorporate all the changes since the last Full, they append each time and will get bigger and bigger until the next Full backup. Depending upon the database activity your diff files could be huge.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2010 at 9:23 am
I put together scripts for Transaction log and diff db backups. They run fine when I execute them manually in SQL. However, the SQL agent jobs that I created fail on the "datepart" parameter for the creating the filename. If I pull in getdate (no time) the job runs successfully through the agent. Does anyone know a better way to add a dateTime stamp to my file names?
from log: Invalid parameter 1 specified for datepart.
SQL:
-- Declares a string
declare @sql nvarchar(255)
-- Declares the filename.
declare @bkdbName varchar(50)
set @bkdbName = 'myDB_LOG_BKUP_'
+ Convert(varchar(10),getdate(),110)
+ '_' + convert(varchar(2),DATEPART("HH",getdate())) + convert(varchar(2),DATEPART("MI",getdate()))
-- declares and set the path.
declare @bkName varchar(255)
set @bkName = '\\vsqlax\e$\SQLBackups\Logs\'
-- set path and db name
set @bkName = @bkName + @bkdbName
-- Sets the backup running by executing the @sql string.
set @sql = 'BACKUP LOG myDB TO DISK = '''+@bkName+'.TRN'''
print @sql
exec sp_executesql @sql
October 21, 2010 at 10:04 am
No quotes for datepart.
eg
SELECT convert(varchar(2),DATEPART(hh,getdate())) + convert(varchar(2),DATEPART(mi,getdate()))
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
October 21, 2010 at 10:18 am
Gail - you ROCK!
October 21, 2010 at 10:21 am
A sortable name is better. Makes it easier to look through the contents of a directory.
declare @bkdbName varchar(50)
set @bkdbName =
'myDB_LOG_BKUP__'+
replace(replace(replace(convert(varchar(19),getdate(),121),'-','_'),':','_'),' ','__')
select bkdbName = @bkdbName
Results:
bkdbName
-----------------------------------
myDB_LOG_BKUP__2010_10_21__12_19_56
October 21, 2010 at 3:22 pm
Gail definitely rocks, I met her at PASS a couple of years ago and attended one of her panels.
And doing file names with embedded date is so massively beneficial that I use it all the time. Personally I wouldn't use embedded delimiters, I'd just use YYYMMDD_HHMMSS to make the file name shorter.
One thing about manually maintaining backup files like this is you have to be careful they're deleted correctly and promptly or you can fill up your backup volume. I maintain my old DBCC logs with a batch file like this: (my most recent DBCC log would be [server]_DBCCLog.txt)
del [server]_dbcclog.10
ren [server]_dbcclog.09 *.10
ren [server]_dbcclog.08 *.09
ren [server]_dbcclog.07 *.08
ren [server]_dbcclog.06 *.07
ren [server]_dbcclog.05 *.06
ren [server]_dbcclog.04 *.05
ren [server]_dbcclog.03 *.04
ren [server]_dbcclog.02 *.03
ren [server]_dbcclog.01 *.02
ren [server]_dbcclog.txt *.01
This way, if I have a DBCC problem over the weekend and don't see the log until Monday, I still have the full log that I can examine. You can use this technique to maintain backups easily enough.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 21, 2010 at 3:43 pm
Wayne West (10/21/2010)
Gail definitely rocks, I met her at PASS a couple of years ago and attended one of her panels.And doing file names with embedded date is so massively beneficial that I use it all the time. Personally I wouldn't use embedded delimiters, I'd just use YYYMMDD_HHMMSS to make the file name shorter.
One thing about manually maintaining backup files like this is you have to be careful they're deleted correctly and promptly or you can fill up your backup volume. I maintain my old DBCC logs with a batch file like this: (my most recent DBCC log would be [server]_DBCCLog.txt)
del [server]_dbcclog.10
ren [server]_dbcclog.09 *.10
ren [server]_dbcclog.08 *.09
ren [server]_dbcclog.07 *.08
ren [server]_dbcclog.06 *.07
ren [server]_dbcclog.05 *.06
ren [server]_dbcclog.04 *.05
ren [server]_dbcclog.03 *.04
ren [server]_dbcclog.02 *.03
ren [server]_dbcclog.01 *.02
ren [server]_dbcclog.txt *.01
This way, if I have a DBCC problem over the weekend and don't see the log until Monday, I still have the full log that I can examine. You can use this technique to maintain backups easily enough.
I like it!
Just for something quick and dirty I set up a mainentance clean up task from the GUI that deletes the prior day's files - based on the directory and file extension. Going to keep a close eye on the files and space usage for a bit before I take things to the next step.
This forum has been a tremendous help! I'm hooked. Just wish I could spend more than 10% of my time on db responsilities...
October 21, 2010 at 3:45 pm
use the extended stored procedure XP_Delete_File to remove old backup files, it deletes based on the file date.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 22, 2010 at 6:46 am
Perry Whittle (10/21/2010)
use the extended stored procedure XP_Delete_File to remove old backup files, it deletes based on the file date.
Yeah - I noticed that the maint. plan cleanup uses the XP_Delete_File proc.
Thanks Perry
October 22, 2010 at 9:12 am
SkyBox (10/22/2010)
Yeah - I noticed that the maint. plan cleanup uses the XP_Delete_File proc.Thanks Perry
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply