This blog is in continuation to using the default values. Earlier this week I wrote a blog which talked about the failure of Create Database Command.
While working on the reproduction of the issue, I changed the parameters and later realized that the backup database command was also failing. If you rely on the default values for folders that don’t exist, this will raise an error message as shown below. Here is the simple command which I have run:
BACKUP DATABASE MASTER TO DISK = 'master.bak'
The command was failing with below error.
Here is the text of the message shown in image:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘E:\InvalidPath\master.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
If you notice the error message is pretty clear. The backup is going to path “E:\InvalidPath” which is an invalid folder on my system. Important point to note here is that I have NOT given any file path in the command, I have just given file name master.bak.
The question is, from where the path is picked? If you have read my previous blog, it’s picked from “BackupDirectory” value in registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer
The highlighted portion might change based on SQL Version and Instance ID. For me it’s MSSQL12 because this is SQL Server 2014 instance. And my instance name is SQL2014:
|SQL Version||Folder Name|
|SQL Server 2008||MSSQL10|
|SQL Server 2008 R2||MSSQL10_50|
|SQL Server 2012||MSSQL11|
|SQL Server 2014||MSSQL12|
For example, if you have SQL Server 2012 default instance, it would be MSSQL11.MSSQLServer.
Here is the registry value
We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.
If you want to use T-SQL then here is the script.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory', REG_SZ, N'E:\InvalidPath'
NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.
Now that we have seen a couple of examples where using the default values have got us into trouble. As readers, I would like to know if you have got into any errors because of using default values in your coding stints. Let me know via your comments.
Reference: Pinal Dave (http://blog.sqlauthority.com)