Restore to default directory

tkdennis

I frequently restore databases to different servers. The Restore Wizard always puts the files in the MSSQL nested Data Directory instead of using the server's configured Database Default Locations specified in the Server Properties. Is there a way to have SQL Backup use the server's Database Default Locations so I don't have to change the directories every time?

Example:

Server Root Directory:
C:\MSSQL\MSSQL10.TEST\MSSQL

Server Default Database Location:
Data: \Database
Log: \Database\Log

SQL Backup default file locations:
Data: C:\MSSQL\MSSQL10.TEST\MSSQL\DATA
Log: C:\MSSQL\MSSQL10.TEST\MSSQL\DATA

Thanks,
Traci

Brian Donahue

Hi Traci,

SQL Backup's user interface tries to prevent you from restoring to invalid data file locations by changing the file locations to the SQL Server default locations.

I think that SQL Backup was designed under the assumption that when restoring a backup from one server to another, you'll more than likely be moving the files due to an incompatible directory structure between the two servers.

tkdennis

Thanks for the response. I just wish it would use the default directories configured for the server instead of the SQL Server installation root so I wouldn't have to keep manually moving them off the C: drive.

Brian Donahue

That's strange, because SQL Backup is supposed to be using the default SQL Server location to restore to. SQL Backup is getting the location from the registry of the SQL Server computer. These are the locations I got from the SQL Backup code a few versions ago -- I hope it's still looking at these keys:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\DefaultData 
/* if this isn't set, then... */ 
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Setup\SQLDataRoot + '\Data' 
For SQL Server 2005:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\Setup\DefaultData 
/* if this isn't set, then...*/ 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\SQLDataRoot + '\Data' 

jerryhung

I just did a test restore, and by default SQL Backup 6.2 picked the nested "C:\Program Files\.........." directory instead of the E:\MSSQL\DATA and E:\MSSQL\LOG directories specified in the "Database Settings"

Brian Donahue

What is contained in the registry keys mentioned in my previous post? The default data locations may be different, either because Microsoft has moved the information somewhere else or there was a misunderstanding in the first place... but I had reverse-engineered backup about 18 months ago and it was looking at these registry keys.

fatherjack2

Brian,

On my SQL 2005 64bit servers I have the data locations stored as values in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\DefaultData
and
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\DefaultLog

where X is an integer relating to DB instance/SSRS instance/SSIS instance installed. the keys that you mentioned are blank

i have the same problem that restores try to default to the installation folder rather than my preferred location.

Brian Donahue

Maybe SQL Backup is looking in the Wow6432Node? I'll have to go through the source code again.

rdeason

That may be the case. As a side note, you have to change the Reg Securities on Wow6432Node to ensure your service account can save templates.

I have the same results as Tracy and Dennis. Inconvenient. My two instances default to:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

My SQL Data location is defined as I:\MSSQL\DATA. It would be great if the default restore location could point here.

I'm running 64bit W2K8 w/ SQL2008STD.

qcjims

I have this same issue. would love a fix.

bboyle

Hi all,

Any update on a solution to this problem? I have a DB with partitions and a large number of files so manually remapping these folders in the UI isn't really an option for me.

Thanks,
Brian

bboyle

I received the following from Redgate:

SQL Backup will default to the default data directory of the server that created the backup file.

We have has a number of users (including us) wondering why it works the way it does, so the developers are looking into this.

The tracking code for this is SB-3204 and it is planned to be fixed in future version of SQL Backup - however, I can't give you a timeframe at the moment of when to expect it.

Brian

TSchwab

I'm having the same issue.

Is there any new info from redgate about this problem?
It is really annoying... especially when you have a database with a lot of files and filesgroups...

:?