How to backup to single .BAK file not a set.

Question

Post reply

How to backup to single .BAK file not a set.

loki1049

SSCrazy

Points: 2065
More actions
February 28, 2011 at 2:31 pm

#231944

I have a couple of developement databases that were created when I was kind of new to SQL server. They were set to use FULL model for backups and recoveries. I have since learned that I only need to use the SIMPLE model as I am only creating backups to store the database structure and a small amount of setup data.

The problem is that each time I go to make a backup it is creating and appending to a backup set. When I look at the extension of this file is has none, its only set to 'File' file type. Am I right to assume that this is different from the .BAK file type? I have got backups from another person I work with who uses databases in cloud space and gives me backups to work off of that are just .BAK files.

How can I create these type of files from my database??

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

It sounds like you are using SSMS to create the backup. If you do not want to append the backup to the previous file in a set, just switch to the Options tab in the Backup Database dialog and you will see the default option of 'Append to existing backup set'. Change this to 'Overwrite all existing backup sets' and the .BAK file will be replaced with a single backup, rather than several backups.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

And the TSQL syntax is to add REPLACE to the WITH clause of the backup.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

And the TSQL syntax is to add REPLACE to the WITH clause of the backup.

REPLACE is for restores. It's INIT or FORMAT for backups, depending on your previous backup settings (media name and retention policy). See BOL for details.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

loki1049 SSCrazy Points: 2065 More actions · Answer 4

Ray Mond (2/28/2011)

And the TSQL syntax is to add REPLACE to the WITH clause of the backup.

REPLACE is for restores. It's INIT or FORMAT for backups, depending on your previous backup settings (media name and retention policy). See BOL for details.

I can't stand BOL.

Looks like I am going to be moving to TSQL for all of my backup needs. I'm really beginning to hate SSMS and other GUI's. Seems code is the only way to go.

loki1049 SSCrazy Points: 2065 More actions · Answer 5

While I'm at it, I have one more quick question.

When creating a backup from TSQL, and using a rented database on a cloud server, is it possible to specify a local path (local in the sense of my client machine) to make the backup. Or if I run the script will it only create the file on the servers harddrive?

Reason being is we have a rented bunch of databases and it seems the only way to get backups is to send out emails and ask for them.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 6

When creating a backup from TSQL, and using a rented database on a cloud server, is it possible to specify a local path (local in the sense of my client machine) to make the backup.

In order to do that, the SQL Server service startup account on the server needs to be able to access your local path. If you can set that up with your service provider, then it's certainly possible.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

loki1049 SSCrazy Points: 2065 More actions · Answer 7

Ray Mond (2/28/2011)

When creating a backup from TSQL, and using a rented database on a cloud server, is it possible to specify a local path (local in the sense of my client machine) to make the backup.

In order to do that, the SQL Server service startup account on the server needs to be able to access your local path. If you can set that up with your service provider, then it's certainly possible.

So how exactly would the syntax look for this if my service provider could access my local path? I'm connecting to the Server from my computer using SSMS. Usually to create a backup I would use TSQL like below:



USE My_Database_Name

GO

BACKUP DATABASE My_Database_Name

TO DISK = 'C:\MyBackUps\TestBackup.BAK'

WITH INIT

But this would back it up to the C drive of the server wouldn't it? How would you specify it to my computer relative to the server path? Would I have to do something like

TO DISK = 'MyComputerName\\C:\MyBackUps\TestBackup.BAK'

I don't really understand how to create the path string. I really just know anything from C:\ onward, I don't know the syntax of selecting a computer then selecting its C:\ directory. Any suggestions or am I completely missing the point of this?