Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup Syntax Expand / Collapse
Author
Message
Posted Wednesday, November 4, 2009 9:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 5:36 PM
Points: 1,339, Visits: 596
Comments posted to this topic are about the item Backup Syntax

--www.sqlvillage.com
Post #813979
Posted Wednesday, November 4, 2009 9:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 7:52 PM
Points: 371, Visits: 717
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 ????????
Post #813980
Posted Thursday, November 5, 2009 4:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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
Post #814110
Posted Thursday, November 5, 2009 7:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 7:52 PM
Points: 371, Visits: 717
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.
Post #814220
Posted Thursday, November 5, 2009 7:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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
Post #814238
Posted Thursday, November 5, 2009 9:01 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
Good question. It's always good to be reminded what the defaults are.

--
Kevin C.
Post #814332
Posted Thursday, November 5, 2009 9:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #814348
Posted Thursday, November 5, 2009 1:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:38 AM
Points: 1,043, Visits: 288
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:.
Post #814518
Posted Thursday, November 5, 2009 10:58 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 10:43 PM
Points: 49, Visits: 145
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
Post #814709
Posted Wednesday, November 18, 2009 3:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 20, 2011 1:15 AM
Points: 9, Visits: 179
BACKUP DATABASE <dbName> TO DISK = '<path>\<dbName>';
Post #820657
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse