Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Syntax


Backup Syntax

Author
Message
Mohan Kumar
Mohan  Kumar
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 Visits: 596
Comments posted to this topic are about the item Backup Syntax

--www.sqlvillage.com
Ol'SureHand
Ol'SureHand
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 720
Sorry guys, the answer is only partially correct.
Backup may happen happily IF you've got the backup folder in the C: drive.
Which I haven't as I move the entire structure to separate drives.

In which case you get exactly the answer I have ticked as correct:

.Net SqlClient Data Provider: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBA.bak'. Operating system error 3(error not found).
.Net SqlClient Data Provider: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
How do I get my 2 points back - and my correctness rating reestablished ????????
majorbloodnock
majorbloodnock
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1113 Visits: 3062
With respect, if the default backup directory for your instance doesn't exist, I'd suggest that's a misconfiguration issue rather than a limitation of the "backup database" command. I can think of a whole world of other things I could do with an instance that'd get perfectly valid SQL commands that'd work on a properly configured instance throwing up errors instead.

In the specific situation you've described, I'd personally amend your instance's backup path so it points to the new location you prefer to use.

Semper in excretia, sumus solum profundum variat
Ol'SureHand
Ol'SureHand
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 720
Also with respect, I was not criticising the SQL command but, rather, pointing to the question having more than one correct answer.

It is debatable if moving your database files (including the Backup folder) away from the C: system drive is "misconfiguration".

Not changing the inane Microsoft default backup directory is more debatable - for the ones not brave enough to change the registry by hand, Phil Phactor has a 2 Kms DMO script and Vince Jacoboni one in Perl - see http://www.simple-talk.com/community/blogs/philfactor/archive/2007/03/22/21152.aspx.

I prefer to NEVER rely on default configured letters... but, then again, that was just a QOTD which IMHO it has more than one correct answer or requires more qualifying conditions.
majorbloodnock
majorbloodnock
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1113 Visits: 3062
Hmm. Despite efforts to the contrary, it seems I've managed to explain myself badly, and come across rather arrogantly at the same time. My apologies.

I don't think that changing storage locations, whether default or actual, around is in itself misconfiguration at all; my point was that making those changes but not telling SQL Server about the amendment may well qualify, despite the scenario you highlighted having very little impact. In this, I made an (unsafe) assumption that this is what you had done, since when I've edited the registry to point to another drive as the default backup location, it's worked fine, and the backup command has picked up the new default location.

Not the first time today that what I've said and what I've meant have been different, either. I think I might head back off to bed as soon as possible....

Semper in excretia, sumus solum profundum variat
KevinC.
KevinC.
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 504
Good question. It's always good to be reminded what the defaults are.

--
Kevin C.
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Interesting question. It succeeded on one server but failed on other. 'Cause the MSSQL\Backup directory is on F:\ drive instead of C:\.

Not sure if that was the cause of failure.

SQL DBA.
Rick Romack
Rick Romack
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 297
Well I did get the correct answer, and when I try it on a SQL 2005 server where the SQL excecutables are installed on O: and the databases are on D: the backup actually ran to the installation created backup folder on D:. :-)
JediSQL
JediSQL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 187
This batch will tell you where backups will go by default. It works on SQL 2000 and SQL 2008 for me:

declare @data nvarchar(512);

exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@data OUTPUT;

print (@data);


- Dan

Sincerely,
Daniel
Ottavio
Ottavio
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 179
BACKUP DATABASE <dbName> TO DISK = '<path>\<dbName>';
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search