What are the challenges you face when working across database platforms? Take the survey

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

bdillbdill Posts: 42 Bronze 2
edited February 22, 2011 12:14PM in SQL Backup Previous Versions
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
    1) My Server Properties specify the default location to be D:\SqlData and E:\SqlLogs 2) The Database being restored is currently in D:\SqlData and E:\SqlLogs 3) The backup was backed up from D:\SqlData and E:\SqlLogs
.. so every time I do a restore I have to (at least twice - 1 for log file and 1 per data file)
    1) Click the elipses button and wait (an eternal 5-10 seconds) for the "Folder Browser" window to finish loading. 2) Click to collapse the 5-level deep MS-SQL default location (or scroll, scroll, scroll to get down to the D:\ drive) 3) Click to expand D:\ drive 4) Click my "SqlData" folder 5) Click OK
Did I mention that I really hate the MS-SQL default data file location? :D


  • Options
    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


    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • Options
    bdillbdill Posts: 42 Bronze 2
    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?
  • Options
    peteypetey Posts: 2,358 New member
    If you were to run the following against the instance where SQL Backup is installed, are the right values returned?
    DECLARE @datapath nvarchar(256)
    DECLARE @logpath nvarchar(256)
    EXEC master..sqbutility 1012, @datapath OUT, @logpath OUT
    SELECT @datapath, @logpath
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    bdillbdill Posts: 42 Bronze 2
    Yes! I get
    "D:\SqlData" and "E:\SqlLogs"

    What is 1012? A hidden/undocumented feature?
  • Options
    peteypetey Posts: 2,358 New member
    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.

    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.