How to permanently change where MSSQL stores database information?

Question

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?

score 4 · Accepted Answer · answered Jul 30 '09 at 22:40

4

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:

Using SQL Server Management Studio (see step 11)
Using Transact-SQL

answered Jul 30 '09 at 22:40

John Röthlisberger

422
1
3
9

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 04:15

score 1 · Answer 2 · answered Jul 30 '09 at 22:40

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.

score 1 · Answer 3 · answered Jul 30 '09 at 23:11

1

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'

answered Jul 30 '09 at 23:11

rorr

622
4
8

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

score 0 · Answer 4 · answered Jul 30 '09 at 22:33

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.

score 0 · Answer 5 · edited Jul 27 '16 at 16:18

0

Go to Object Explorer. Right Click on the database server and select "Properties". Go to "Database Settings" on the left hand side and change the Database Default Location.

Note: This is applicable only to the specific Database Server you making the changes to.

edited Jul 27 '16 at 16:18

BE77Y

2,577
3
17
23

answered Jul 27 '16 at 14:28

Gautham

1

How to permanently change where MSSQL stores database information?

5 Answers5