XML FeedsThe following question was asked
I've got a sql server that's set up to have database files to default to e drive, but the model's files are on c. will new database pick up the setting from the server and put them on e, or will they pick it up from the model and put them on c?
What SQL Server does is that it will pick up the settings from the server settings. We can easily verify that by running some scripts
To check where model has its path, you can run the following query
- select * from master..sysaltfiles
- where db_name(dbid) ='model'
- go
On my laptop, the location for the files is as follows
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf
If I now create a new database like this
- create database TestMeNow
- GO
And if I now check for the location
- select * from master..sysaltfiles
- where db_name(dbid) ='TestMeNow'
- go
I can see that it is the same as for model
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestMeNow.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestMeNow_log.ldf
Now, let's change it at the server level, this code below will make the default for data files on D:\Data and log files on D:\Log
- USE [master]
- GO
- EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\Data'
- GO
- EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\Log'
- GO
Or you can do it by right clicking on the server --> properties --> Database Settings if you are not as comfortable running SQL. Here is what it looks like
Now restart the SQL Server instance for the changes to take effect
Create a new database
- create database TestMeNow3
- GO
Now if you check, you will see that it placed the files in the location we have specified in the server settings
- select * from master..sysaltfiles
- where db_name(dbid) ='TestMeNow3'
- go
Here is where the files are located
D:\Data\TestMeNow3.mdf
D:\Log\TestMeNow3_log.ldf
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.