File locations during a restore

Giggles220Giggles220 Posts: 154
edited April 30, 2008 5:59AM in SQL Backup Previous Versions
When I do a restore and get to the screen to select the Database to restore to I always have to change the location for the files. If I pick an existing database is does not show that databases location and if I pick a new one it does not pick up the default for the instance of SQL. Am I missing something?

Comments

  • peteypetey Posts: 2,358 New member
    When you run the following, are the right values returned?
    DECLARE @data nvarchar(256)
    DECLARE @log nvarchar(256)
    EXEC master..sqbutility 1012, @data OUTPUT, @log OUTPUT
    SELECT @data, @log
    
    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • When I run the query I get my default path, but when I try to do a restore from a file I get a different path. I have screenshots if it helps.

    [/img]
  • peteypetey Posts: 2,358 New member
    Thanks for bringing this to our attention. I have raised an internal report for this (ref. SB-3429).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I am also seeing this problem. I am trying to restore a 4.6 backup using 5.2. When I get to the Choose Database dialog box, I do not see any of the data files, nor does the ... box function.
  • Is there an update on this as I am havingthe same issue - version 5.3
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    If you are restoring through the user interface, SQL Backup tries to work out the default data location for the instance of SQL Server you're restoring to. This is always going to be the same unless you change the registry on the SQL Server computer. Here are the relevant entries:
    For SQL Server 2000:
    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'
    

    So setting the DefaultData registry key to the location where you want the database files created will change the location selected by the SQL Backup console. If the log location is different than the default data location, I can't see how you have any other choice than to manually pick the log file directory when you restore.
Sign In or Register to comment.