June 2, 2010 at 12:32 pm
I am the SQL DBA, and I've been given a few SMS and SCCM servers to "do my thing". I don't know anything about these products.
First thing I noticed: no Maintenance Plans and massive Transaction Log files.
So I dutifully created my standard weekly Full, daily Differential and hourly Log backups, let 'em rip one ofter the other, then shrank the massive logs, etc.
Reported back to the SMS manager, he said Wait... SMS does its own backups! So I asked if it backs up the SQL databases, and he said he didn't know. (and I searched on here but it is very hard to find a relevant article, if there are any, since both terms are so popular.)
By the size of the SMS/SCCM backup files it looks like it may be backing up the SQL databases, but I'm not sure. And I don't want to have TWO systems backing up SQL, truncating logs, etc -- leading to difficult? impossible?? restores if/when needed. Although even if SMS is doing SQL backups it does not seem to be truncating logs.
Any experience with these? Any guidance much appreciated -- SQL backups are now running!!
Thanx!
June 2, 2010 at 12:48 pm
I'm not an SMS, SCCM, MOM, or SCOM expert, but I have always backed up the databases. And when one of the drives failed it was invaluable when it came to restoring corrupt pages.
I'm not by any means saying a backup couldn't be taken on these, but the guys I work with that do know these products were very happy I was backing up the DB's.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
June 3, 2010 at 6:33 am
It only backups up the database if it's configured to and it doesn't take into account logs at all. If you want a full point in time recovery system, you need to do the backups yourself. But, that means that you need to coordinatre with the SMS manager so that he doesn't do database backups. Otherwise, his full backups are going to mess with yours and a point in time recovery could be seriously compromised.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2010 at 8:34 am
Run something like this to see all the backups, and see if there are any running other than yours.
SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, user_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -30, (getdate())) -- Last 30 days
AND sysdb.name = 'My_DB_Name'
ORDER BY sysdb.name, bkup.backup_finish_date desc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply