Post reply

Method to inventory contents of multiple bak files without restoring the backup

  • January 6, 2016 at 11:49 am

    #303551

    If I want to get basic details about a DB Backup from a bak file I can, using SSMS, go to Restore DB, select Add file, browse to the bak file, select it and on the RESTORE DATABASE dialog , within the Backup set to restore section I can see several pieces of information such as DB Name, Type and username. Is there a way to either get this same info or preferably more for many bak files without having to restore the DB's and without having to select each database one at a time using SSMS as described above?

    Basically I'm looking for a way to inventory a large number of bak files without having to load each manually. We have a sizeable number of bak files (most of which are a few GB or less) that represent OLD databases, many of which are duplicate and or triplicate copies under different names. I was hoping there might be a faster/easier way to scan all these bak files to get enough info about them to identify the duplicates as well as get basic info about each beyond what is implied in the bak file name.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • January 6, 2016 at 12:15 pm

    #1849987

    You can start with RESTORE HEADERONLY to get the basic information.

    To have it cycle through all the BAK files in a location, you're best bet would be to whip up a Powershell script to get the list of files, and feed that list into a SQLCMD line, writing the output to a text file (or the screen, if you prefer.) You could also skip the Powershell, and use the DOS command FORFILES to do the same thing.

  • January 6, 2016 at 2:28 pm

    #1850040

    In powershell you can iterate through the files in a folder and use a SMO Restore object ReadBackupHeader method to get the information. I would have thought that the Restore-SqlDatabase cmdlet would have an option for Header Only but there are references that it does not expose that functionality.

    https://social.msdn.microsoft.com/forums/sqlserver/en-US/48554741-fe14-4302-b16b-a41c5f5f519e/restoresqldatabase-cmdlet

    http://sqlmag.com/powershell/powershell-sql-server-way

    https://www.simple-talk.com/sql/backup-and-recovery/backup-and-restore-sql-server-with-the-sql-server-2012-powershell-cmdlets/

  • February 16, 2016 at 10:47 am

    #1859039

    RESTORE LABELONLY FROM DISK = N'X:\MyDBBackupFolder\MyDBBackupFile.bak'

    Thanks to everyone that offered up a possible solution but RESTORE LABELONLY ended up being the best solution. This command restores basic data about the bak file but what is included is the MediaDate which is the date the backup was done and that's sufficient enough for what I needed.

    Thanks again.

    Kindest Regards,

    Just say No to Facebook!

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

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