November 4, 2009 at 9:30 pm
Comments posted to this topic are about the item Backup Syntax
[font="Verdana"]--www.sqlvillage.com[/size][/font]
November 4, 2009 at 9:34 pm
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 ????????
November 5, 2009 at 4:12 am
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, suus solum profundum variat
November 5, 2009 at 7:24 am
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.
November 5, 2009 at 7:42 am
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, suus solum profundum variat
November 5, 2009 at 9:01 am
Good question. It's always good to be reminded what the defaults are.
--
Kevin C.
November 5, 2009 at 9:14 am
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.
November 5, 2009 at 1:06 pm
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:. 🙂
November 5, 2009 at 10:58 pm
This batch will tell you where backups will go by default. It works on SQL 2000 and SQL 2008 for me:
[font="Courier New"]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);
[/font]
- Dan
Sincerely,
Daniel
November 18, 2009 at 3:02 am
BACKUP DATABASE <dbName> TO DISK = '<path>\<dbName>';
December 1, 2009 at 1:59 pm
After having used SQL backup by RedGate for so long, I have forgotten the syntax for a simple backup. I usually just script it out from the GUI! 😀 How embarrassing considering I am a DBA !!
--
:hehe:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply