Post reply

Backup Query

  • May 23, 2005 at 2:43 am

    #110232

    When I look at the properties of a database (right click in Enterprise Manager and select Properties) it shows that the database was last backed up yesterday but there are no backup jobs in Management defined that I can see. How can I find out what process is scheduling this backup?

    Thanks

  • May 23, 2005 at 4:59 am

    #560461

    in queryanalyser run :

     restore headeronly FROM DISK = 'path\yourbackupfile.BAK'

    I hope this helps.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • May 23, 2005 at 5:31 am

    #560465

    I searched all local drives on the database server for *.bak files created or modified on the date that the database had apparently last backed up and found nothing so I can't use the syntax provided. Is it possible to backup the database from another server i.e. for another server to contain the backup job, schedule and backup files?

    Thanks

  • May 23, 2005 at 5:55 am

    #560468

    yep

    Check sqlagent master-server

    I cannot test it right now, but if i remember correct, it would for sure leave a trace at the server.

    Check your server's sqlserver-log file(s)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • May 24, 2005 at 9:33 am

    #560788

    You can also query the msdb directly. Backup information is stored in 4 tables here. The following query will pull most of the physical information out of them for a backup that occurred at a specific time.

    Select

     name

    , description

    , user_name

    , backup_start_date

    , backup_finish_date

    , type

    , database_name

    , server_name

    , machine_name

    , filegroup_name

    , file_number

    , logical_name

    , physical_drive

    , physical_name

    , physical_device_name

    , device_type

    From msdb..backupset s

    Join msdb..backupfile f

     On s.backup_set_id = f.backup_set_id

    Join msdb..backupmediafamily m

     On s.media_set_id = m.media_set_id

    Where s.backup_start_date = '<the backup time>'

    You will have to modify the where clause to use the time from your system.

     

    Hope this helps.

    Richard L. Dawson
    Microsoft Sql Server DBA/Data Architect

    I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

Viewing 5 posts - 1 through 4 (of 4 total)

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