Restore Wizard - Step 2 of 4 - Files/Restore As issues

bdill · February 17, 2011 6:42PM

I am using SQLBackup 6.4

Desired behavior: Have the "Resore As" path default to the SQL server instance configured "default location" for data and log files rather than the horrible MS-SQL default location.

The "Restore As" path is not right on "Step 2 of 4" in the Restore Wizard. (See screenshot )

The dialog always uses the horrid MS-SQL default location:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

but I have configured SQL to use different default locations.

Server Properties -> Database Settings

Database default locations

Data: [D:\SqlData ]
Log: [E:\SqlLogs ]

SQL Backup defaults to the crappy MS-SQL default location even though

.. so every time I do a restore I have to (at least twice - 1 for log file and 1 per data file)

Did I mention that I really hate the MS-SQL default data file location?

peter.peart · February 21, 2011 1:59PM

Hi there,

Thanks for your post. I believe the below knowledgebase article on our website will tell you how to do this, however if you have any trouble let me know:

http://www.red-gate.com/supportcenter/C ... wledgebase\SQL_Backup\KB200805000255.htm

HTH!

Pete

bdill · February 21, 2011 4:06PM

Thank you for for reference to the KB article, however it is insufficient for two reasons.

1) The Restore wizard (or something) appends a hard coded "\Data" to the end of my specified location. Ex: I put "D:\SqlData" in the registry, but the Restore Wizard shows "D:\SqlData\Data". 2) No separate entry for default log location. My data files and log files are on different logical (and physical) drives - as recommended by everybody who knows anything about SQL internals and hardware. #2 is a bummer. If #1 worked, this would solve at least 1/2 of my pain-point, but because #1 the registry hack is totally useless to me.

From a philosophical standpoint, why would the tool simply ignore any meaningful configuration set by the user? I argue that the tool should pull the default Data and Log locations from the server instance configuration. After all, SQL Server uses it when creating new databases. If it's good enough for SQL, why not Red-Gate?

petey · February 22, 2011 1:10AM

If you were to run the following against the instance where SQL Backup is installed, are the right values returned?

DECLARE @datapath nvarchar&#40;256&#41;
DECLARE @logpath nvarchar&#40;256&#41;
EXEC master..sqbutility 1012, @datapath OUT, @logpath OUT

SELECT @datapath, @logpath

Thanks.

bdill · February 22, 2011 12:01PM

Yes! I get
"D:\SqlData" and "E:\SqlLogs"

What is 1012? A hidden/undocumented feature?

petey · February 22, 2011 12:14PM

1012 is one of many utility functions available to the GUI to retrieve details about the SQL Server instance. 1012 will retrieve the data and log locations based on the following order:

- user defined values as per DefaultData and DefaultLog registry values
- SQLDataRoot registry value
- SQLDataPath registry value

I've raised a bug report (SB-4609) for the GUI to use this utility function so as to offer more meaningful locations for the data and transaction log files.

Thanks.

Restore Wizard - Step 2 of 4 - Files/Restore As issues

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk