locked
Backup completed or not RRS feed

  • Question

  • Hi Team,

     

    we have backup plan. All the backups are complted.

    How we check backups are completed are not 

     

    which commmand we have to use to check the backup are completed or not.

     

    Tx

     


    subu
    Thursday, October 21, 2010 2:01 AM

Answers

  • Hi Freemascot,

     

    Before answering question please understand the question  here i am asking about database backups completed or not  how can we check 

     

    the best solution is in "MSDB- BACKUP SET table "  

     

    Any way thank you for your effort. Really appriciate  to all the guys..

     

    Tx

     


    subu
    • Marked as answer by subu999 Thursday, October 21, 2010 9:34 AM
    Thursday, October 21, 2010 5:53 AM

All replies

  • Hi Subbu,

     

    There are many way is there to check backup is happen successfully or not.

     

    1)Right click the maintenance plan you created for taking backup and click view history and see the status  column if its show tick mark then it success if in case of cross mark failure.

     

    2)Run the below script which show the exact detail about backup

    Select

    distinct

    b.machine_name as 'ServerName',

    b.server_name as 'InstanceName',

    b.database_name as 'DatabaseName',

    DATABASEPROPERTYEX (b.database_name,'Recovery') as 'RecoveryModel',

    d.cmptlevel as 'CompatibilityLevel',

    d.dbid 'DBID',

    d.crdate 'DB_created_on',

    CASE b.[type]

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Transaction Log'

    END as 'BackupType',

    b.backup_finish_date 'last backup date',

    reverse(substring(reverse(bm.physical_device_name),

    charindex('\',reverse(bm.physical_device_name),1),1000)) as [Backup_path]

    --into ibmdb.dbo.backupdetails

    from master..sysdatabases d inner join msdb.dbo.backupset b

    On b.database_name =d.name

    inner join msdb.dbo.backupmediafamily bm

    on bm.media_set_id =b.media_set_id

    WHERE d.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

     

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = b.type)

     

     


    Regards, RM Thirunavukkarasu MCP, MCITP, ITIL V3 | http://thiruna.blog.com |Click "Propose As Answer" if solution is helpful to you.
    • Proposed as answer by Pradeep Adiga Thursday, October 21, 2010 4:55 AM
    Thursday, October 21, 2010 4:49 AM
  • You should also verify your backups either as part of the backup process or you can use RESTORE VERIFYONLY. See:

     

    http://msdn.microsoft.com/en-us/library/ms188902.aspx

     

     

    Thanks

    • Proposed as answer by moort Thursday, October 21, 2010 4:55 AM
    Thursday, October 21, 2010 4:54 AM
  • You can refer table sysjobhistory in the msdb database with the specific job_id.
    Thursday, October 21, 2010 5:22 AM
  • Hi Freemascot,

     

    Before answering question please understand the question  here i am asking about database backups completed or not  how can we check 

     

    the best solution is in "MSDB- BACKUP SET table "  

     

    Any way thank you for your effort. Really appriciate  to all the guys..

     

    Tx

     


    subu
    • Marked as answer by subu999 Thursday, October 21, 2010 9:34 AM
    Thursday, October 21, 2010 5:53 AM
  • It depends on what you actually need. If you use SQLBackupAndFTP - it can send you email notifications after backups are completed
    Tuesday, October 26, 2010 2:43 PM