Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Using SQL Server Management console for SQL Server 2005 we go to

Server Properties --> Database Settings --> 
change database default location from c:\blah\blah to E:\MSSQL\DATA

We restart the server and it should store all new databases in that directory at least in theory.

Everything so far is ok, but when we restore the first databasename.bak file, it stores the information on the C:\Program Files\blah\blah\data directory instead.

Any idea on how to make this permanent even to restored databases?

share|improve this question
add comment

4 Answers 4

up vote 4 down vote accepted

The database .bak file contains information about where the backed up database held its mdf and ldf files. My guess is that it was designed this way to make restores super simple. To restore a backup to different locations (i.e. the new locations you want), you need to use some slightly advanced features:

share|improve this answer
    
Meant to add that any new databases you create should end up in the new default locations you specified (which is what the setting is intended for). –  John Röthlisberger Jul 30 '09 at 23:56
    
Another vote for Transact-SQL method. I've used it personally in the past. –  Tatas Jul 31 '09 at 4:15
add comment

The server default you set only controls default location where new DBs will be located. You need to tell SQL new location of files in the RESTORE operation. If using the GUI, switch to the options page of the restore DB dialog, and give each file a new path. If using query window, use RESTORE FILELISTONLY to get list of the files, then use that info to RESTORE DATABASE ... WITH MOVE ... to restore the DB in the location of your choosing.

share|improve this answer
add comment

To restore a database called 'mine'

RESTORE DATABASE mine
    FROM DISK = 'c:\mine.bak'
    WITH MOVE 'mine' TO 'c:\vol\newlocation\mine.mdf',
         MOVE 'mine_log' TO 'c:\vol\newlocation\mine_log.ldf'
share|improve this answer
    
Thanks for your respond rorr. I got the following error Msg 3234, Level 16, State 2, Line 1 Logical file 'dbname' is not part of database 'dbname'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. –  Geo Jul 31 '09 at 16:08
    
restore filelistonly from disk = 'c:\mine.bak' will give you a list of the logical names. –  rorr Jul 31 '09 at 18:42
add comment

I believe backed up databases default to restore to where they were when they were backed up. So if you want to change this, you can just backup the databases once they are in their new locations.

Otherwise, you can look at:

HKLM\Software\Microsoft\Microsoft SQL Server\[instance name]\SQLDataRoot

and make sure that is the right path.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.