June 25, 2012 at 7:24 am
I've recently been given the task of documenting our SQL Servers (around 30 in total) and specifically focusing on the backup aspect.
The issue I'm faced with is that the company seems to have used at least 2 third-party products to backup "some" databases in the past, and also SQL backups are thrown into the mix. The 2 third-party products I've discovered (so far) are redgate and Microsoft DPM ... and I know these have been used because they are present on the first server I'm looking at (DPMRA service exists and red-gate has a start-menu item).
My question is ... does anyone know how I can find out which piece of software is backing up these databases? I've done the usual checks and found that daily backups are being taken. After running : select * from msdb.dbo.backupset I can see the software_vendor_id column has 4608 and nothing else (which I believe is SQL backup ... but I'm not sure if DPM or Redgate utilize SQL Backup which would make this a red-herring).
SQL version is 2008 running on Server 2008 (but I don't think the question is version specific).
To my knowledge there's no powershell commands I can run to check the current DPM activity from the SQL server (I don't have any information for DPM in this company) and red-gate GUI has some schedules listed but I can't determine if they're active as the logs seem corrupted.
Lastly ... the previous DBA left no documentation so I literally am flying blind with this :hehe:
June 25, 2012 at 8:33 am
I'm not sure what to do about the Red Gate backup logs, but that software works directly with the SQL Agent on the servers, so you should be able to look at the agent to see what jobs are scheduled, what their status is, and what commands they are using to run backups. I think that will go a long way towards answering the questions.
"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 25, 2012 at 8:49 am
Thanks for the reply Grant.
I was hoping to do all the investigation within SQL if possible (possibly with some type of DMV which I've overlooked) rather than sifting through the 3rd party logs.
In the past hour I've found that some of the 3rd liner's seem to think DPM is the tool used for backup but due to the lack of documentation and the amount of servers in the business ... finding which server would be a case of logging into almost any server which alludes to backup in the server name :crazy:
If there's definitely no easy way within SQL to figure this out then I best get my best mole hat on and go diggin ... which is very daunting indeed, lol.
June 25, 2012 at 10:01 am
You can query directly against the msdb tables for SQL Agent, if Agent is running the backups. But if the schedule is external... you're probably out of luck.
"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 25, 2012 at 2:18 pm
I would think that DPM has a console that lists all of the servers and schedules that it uses. At least, that was my understanding...
For RedGate and others like it, you cannot just look at the agent jobs. If someone has decided to use standard maintenance plans and built in Execute SQL Tasks that call out to the extended procedures you will not see those. In that case, you will have to open every single maintenance plan to identify what is actually being used.
You also need to be aware that Litespeed can be installed in a native mode - which essentially means it overwrites the standard native backup. So, normal SQL Backups would actually be using the Litespeed engine. Not sure if RedGate has the same feature available - but I do know they have Hyperbac which could be used to compress the backups with native SQL.
And finally, all of these utilities have command line capabilities and it is always possible that someone implemented backups outside of SQL Server using the command line.
Oops, also forgot about SAN backup utilities (e.g. Netapp SnapManager) - which show up as normal SQL backups but are really just snapshots of the LUN where the databases are located.
Recommendation: be the mole 🙂
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
June 26, 2012 at 7:39 am
For what it's worth, here is what I use. It shows all the backups, the server, database, user name, start date, type (database, tranlog, etc.), size, physical file name, and the name of the backup (program being used, if 3rd party). Using this script, I noticed that someone was doing Backup Exec backups when I was doing my normal SQL Server native backups. Messing up my backup chain.
I also use it to see if anyone is doing unauthorized backups (db_owner lets you do backups to the default backup area, vendors were doing backups before upgrades, without my knowledge. No more db_owner rights for them....), and if any are going to directories that I am not clearing out periodically.
Comes in handy. Hope it helps.
--List all backups for each database
SELECT S.server_name,S.database_name, S.recovery_model, S.name, S.user_name,
S.backup_start_date, S.type, CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) AS backup_size_MB, M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
--WHERE S.database_name = ''
ORDER by backup_start_date DESC
--ORDER by S.database_name, backup_start_date DESC
--To see if any other programs are doing the backups
SELECT DISTINCT name FROM msdb..backupset ORDER BY name
June 27, 2012 at 2:23 am
Apologies for the late reply,
All responses have been very informative and I'm in a better position now to start digging. I will try those scripts out this morning and see what we find but I've come equipped with my mole hat as I think it's inevitable I'll end up getting my hands dirty with this task.
If I find anything along my travels I'll update this thread to ensure knowledge is shared.
Thanks again, and if I get to china, I'll stop digging!
Cheers,
Carl
June 27, 2012 at 3:25 am
DPM takes backups in a completely different fashion to Redgate SQLBackup. SQLBackup will most likely, if standard config, be generating .SQB files and these are the SQL Server backups.
As Jeffrey said DPM has its own console, the way it takes backups is to snapshot files and this is done using the volume shadow copy service, they are PIT snapshots only and IMHO not very flexible. If you look in the database directory for DPM enabled servers you'll generally see subfolders with the snapshotted files.
Do yourself a favour and throw DPM in the round file and stick with SQLBackup 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 9, 2012 at 2:11 am
lmaosol (6/25/2012)
I've recently been given the task of documenting our SQL Servers (around 30 in total) and specifically focusing on the backup aspect....
SQL version is 2008 running on Server 2008 (but I don't think the question is version specific).
Guess, I am being late with the following piece of advice, but nevertheless.
Honestly, I didn't read the thread, but I want to share my experience of SQL backup. I use Handy Backup Small Server 7 with SQL plugin for the daily backup of my stuff. So far, everything is perfect and fast, but I'm not sure how the things would be in your case because I have only ONE server.
You should, probably, have a look at Handy Backup 7 Network Server - a bigger version of the solution I use. I guess, it will divide your servers into several groups and backup them in several sessions (in order for balance between performance and time).
Hope, this Helps!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply