February 28, 2011 at 2:31 pm
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??
February 28, 2011 at 4:17 pm
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
February 28, 2011 at 4:41 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2011 at 7:28 pm
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.
February 28, 2011 at 10:37 pm
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.
February 28, 2011 at 10:40 pm
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.
February 28, 2011 at 10:51 pm
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.
March 1, 2011 at 12:48 pm
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?
March 1, 2011 at 12:52 pm
TO DISK = '\\MyComputerName\C$\MyBackUps\TestBackup.BAK'
-or-
TO DISK = '\\MyComputerName\MyBackUpsShared\TestBackup.BAK'
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 1, 2011 at 1:02 pm
Great, thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply