March 18, 2010 at 10:30 am
Steve Jones - Editor (3/18/2010)
Don't be sorry. We appreciate the acknowledgement, but it's not necessary. we very much are here to help, and we enjoy it. Our intention is not to do work for you, but to help you learn and figure out things yourself. Or give you our thoughts /advice on issues.
Thanks Steve, great site, I am a lone SQL DBA in a shop with over a dozen Oracle DBAs who think Oracle is God's gift to computerkind :w00t: and SQL is a toy... gets lonely here sometimes... good to have somebody to talk to!
March 18, 2010 at 10:35 am
Be very careful about using compression & encryption on your database backups at the same time. These two tools don't play play well together and can hose your backup files.
To make sure they haven't, do a few test restores to make sure that your script is keeping good backup copies.
March 18, 2010 at 11:23 am
jpSQLDude (3/18/2010)
Do you think it makes any difference if you switch to Simple, then take a Full backup, then switch to Full Recovery Model; or switch from Full to Simple back to Full, then take a Full backup?
Yes.
If you take a full backup before switching to full recovery then, once you have switched to full recovery, your log backups will fail until a full backup is run. A full backup is required to start the log chain, you cannot have a log chain in simple recovery because the log is truncated on a regular basis.
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
March 18, 2010 at 11:27 am
It's funny. This subject came up last night at our local users group. I'd never heard of it before then. Now you guys are talking about it on this thread....
Bulk-logged doesn't have the same problem, does it? I've never had a problem switching between FULL & BL and my translog backups. But then I've never double-checked.
March 18, 2010 at 11:30 am
No, bulk-logged retains tran log chain. That's why you can switch from full to bulk and back and the log's intact.
Full recovery = log retained, all operations fully logged
Bulk logged recovery = log retained, some operations minimally logged
Simple recovery = log not retained, some operations minimally logged
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
March 18, 2010 at 11:36 am
I don't see the point of breaking the chain to get a "fresh" full backup. Just take full backups in full recovery as you have been doing. Delete the old backups if you don't need them. If you need to do a restore, you will choose which full backup to use as a starting point (most likely the most recent), so you can ignore all the older ones.
You can run something like this to check when your databases were last backed up. Don't forget to backup the system databases daily (master, model, msdb)
-- Most Recent Backups and # of days since ANY type of backup
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
case
when status > 16 then 'Check DB Status' -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name , a.type, status
ORDER BY B.name , LastBackupDate desc,a.type, status
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply