Post reply

Backup DB as a bak file type?

  • July 30, 2017 at 12:59 pm

    #339139

    Hello,

    I am using SSMS and trying to do a db back.

    I select DB > Tasks > Backup.

    Type: Full
    Destination: Disk   (since its local pc)

    File of Type: Selected option: (*bak,*tm)

    Problem is when I go to view file, it is not a bak file.

    In windows explorer it says file type:  File, and in a previous back up using same stesps as above it is saved as file type: MS Dos Application.

    Question: How do i get it to save as a .bak file?

    Thanks

  • July 30, 2017 at 2:02 pm

    #1953306

    The "File of Type: Selected option: (*bak,*tm)" option isn't an "option".  It simply controls what to display.  If you want your file to have a ".bak" extension, YOU have to enter the extension in the file name.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 30, 2017 at 3:53 pm

    #1953312

    When you give the backup file a name, give it the .bak extension. SSMS doesn't do it automatically (or you can change it after the backup, will have the same effect)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 30, 2017 at 4:05 pm

    #1953315

    Ok when i give the filename a extension like, myfilename.bak, i get the error message below:

    System.Data.SqlClient.SqlError: The media loaded on "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\MyFilename.bak" is formatted to support 1 media families, but 3 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

    My goal is simply to have a backup (.bak) of db on local machine.

  • July 30, 2017 at 4:08 pm

    #1953316

    ok success, apparently the window was so small i couldn't see the other file names, i removed those, resaved with *.bak, and it worked

    Thanks guys. Appreciate you're help!

  • July 30, 2017 at 4:10 pm

    #1953317

    Make sure you only have one file name in that window, otherwise your backup is spread across the files and they are *all* necessary to restore the backup (they're slices, not copies)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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