Carl Federl (11/11/2010)
Regarding your statements:"In the case of backups, this allows you to specify a backup with just a file name (from T-SQL), and the backup will go to this directory." this does work.
"For data/log files, new databases will default to this location unless specified otherwise.", this does not appear to work and the database file location are the same as the master database. You can test this by setting the locations for DefaultData and DefaultLog and then run:
Also the registry read statements returns nulls because the statment:
set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) , 250)causes the "\" to be included as part of the @servername and needs to change to:
set @ServerName = SUBSTRING(@ServerName,CharIndex('\', @Servername) + 1 , 250)
Carl, thanks for the feedback. However, when I run your code, the database is created in the proper location. Here are my results:
DBName filename
------ ------------------------------------------------------
master C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
master C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
DBName filename
---------------- ------------------------------------------------------
TestFileLocation C:\SqlData\TestFileLocation.mdf
TestFileLocation C:\SqlData\TestFileLocation_log.LDF
Processed 160 pages for database 'TestFileLocation', file 'TestFileLocation' on file 2.
Processed 2 pages for database 'TestFileLocation', file 'TestFileLocation_log' on file 2.
BACKUP DATABASE successfully processed 162 pages in 0.310 seconds (4.076 MB/sec).
And thanks for finding and fixing that bug - the +1 definitely needs to be there. I guess I forgot to test this on a server with named instances on it. :blush:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes