Backup DB as a bak file type?

Question

Backup DB as a bak file type?

VegasL

Hall of Fame

Points: 3006
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004558 More actions · Answer 1

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

VegasL Hall of Fame Points: 3006 More actions · Answer 3

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.

VegasL Hall of Fame Points: 3006 More actions · Answer 4

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Backup DB as a bak file type?

Cookies on SQLServerCentral