Post reply

How to find the backup is happening by third party tool.

  • October 28, 2009 at 7:28 am

    #211470

    Hi Everyone,

    I want to know how to find out in Sql Server 2005 that the backup is happening through the third party tool(eg veritas), other than the sql server backup. Is there any script avaliable, or which table i have to query in msdb (backupfile,backupfilegroup,backupmediafamily,backupmediaset,backupset).

    I tried to query all these table in msdb but in vain 🙁

    Thanks in Advance.

    PT.

  • October 28, 2009 at 7:32 am

    #1071723

    well, you can check the status of the databases:

    select * from sys.databases where state_desc <> 'ONLINE'

    --possible values:

    /*Database state:

    0 = ONLINE

    1 = RESTORING

    2 = RECOVERING

    3 = RECOVERY_PENDING

    4 = SUSPECT

    5 = EMERGENCY

    6 = OFFLINE

    */

    but you cannot trigger/track the event that changes the state of the database, as far as i know...just discovere it with a job repeating every x period of time.

    you could also find when this occurs in the default trace, i think.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 28, 2009 at 8:46 am

    #1071804

    I'm not sure if you mean actively running processes, or backup history. I use this to get a variety of backup history information. it might help you to modify it to filter or sort on USER_NAME that's running the various backups.

    SELECT a.server_name as 'Server',

    a.database_name as 'Database',

    convert(varchar(25),a.backup_start_date,100) AS 'Start Date',

    convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',

    DATENAME(weekday, a.backup_finish_date) AS 'Day' ,

    datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,

    cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)

    as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,

    case

    when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0

    then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))

    else 0

    end as 'Meg/Min',

    ceiling(a.backup_size /1048576) as 'Size Meg' ,--cast((a.backup_size /1048576) as decimal (9,2)) as 'Size Meg' ,

    cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig

    a.user_name,a.backup_size as 'Raw Size'

    FROM msdb.dbo.backupset a

    join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name

    WHERE a.type = 'D' and b.type = 'D' /*D=Full*/ AND a.backup_start_date > '2009-09-01'

    --and a.database_name = 'Database_Name'

    group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name

    order by a.server_name, a.database_name, a.backup_start_date desc

  • October 29, 2009 at 3:08 am

    #1072296

    Hi homebrew01

    Well thnkx for this script.

    But my problem is that how can I found out the backup is happening on the SQL Server is happening through third party tool or by simple backup maintance plan.

    Thnkx

  • October 29, 2009 at 10:27 am

    #1072621

    Paritosh-412771 (10/29/2009)

    Hi homebrew01

    Well thnkx for this script.

    But my problem is that how can I found out the backup is happening on the SQL Server is happening through third party tool or by simple backup maintance plan.

    Thnkx

    In our case, a different account runs our Veritas backup compared to the account that runs native backup, so I can look at the user_name specifically. May not work for you if the accounts are the same. Get them changed ?

  • October 30, 2009 at 6:27 am

    #1073057

    If you have an idea as to when this might be running, you can always set up a server side trace. To reduce the trace, you can filter on the MSDB database only.

  • October 30, 2009 at 12:20 pm

    #1073316

    The software used to perform the backup is recorded in the msdb.dbo.backupmediaset table.

    In my case if I run:

    select software_name from msdb.dbo.backupmediaset

    For standard SQL backups I get "Microsoft SQL Server"

    When I use my 3rd party backup utility I get "Idera SQLsafe"

  • October 30, 2009 at 12:42 pm

    #1073329

    I'm using Red Gate SQL Backup a 3rd party tool and the entries written to my "backupmediaset" are "Microsoft SQL Server".

    Maybe because I'm running it as a SQL Server job calling procedures which the Red Gate software installs? Or when the 3rd party tool is written to the backupmedia table they're just putting "an application name" which could be anything?

  • October 30, 2009 at 1:04 pm

    #1073336

    Hmm..

    I also use Hyperbac on one of our servers. It works a little differently - its a windows service that "intercepts" standard SQL backup commands. If it detects a .hbc or .zip extension for the backup file it kicks into action and compresses the output. When I look at the software_name in this case it says Microsoft SQL Server - but I would expect that since the Hyperbac utility is working outside of the SQL Server process. Idera uses the VDI feature of SQL, so it is inside the SQL process.

    Not sure about Redgate.

  • November 4, 2009 at 12:25 am

    #1074702

    HI

    use d following sql command,it may help u.

    select software_name from msdb.dbo.backupmediaset

    its sowing softwarename as microsoft sql server

    😎

    thanks

  • November 5, 2009 at 7:33 pm

    #1075673

    you can track using sql profiler

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply