When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations.
Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right click on the server and select "Server Properties", then go to the "Database Settings" tab and near the bottom you will see a section with "Database Default locations")
For example, suppose I execute the following statement:
CREATE DATABASE DefaultLocationDB
On my machine this means 2 files will be created:
- C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.mdf
- C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.ldf
This is nice, you don't have to remember a path to pass, but when you want to restore a database from another server (where a different path is used) it can be a pain.
To restore a database with a specific location for the files we use the following syntax:
RESTORE DATABASE DefaultLocationDB
FROM DISK = N'c:\backups\DemoDB.bak' WITH FILE = 1,
MOVE N'demo_data_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',
MOVE N'demo_log_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',
NOUNLOAD, REPLACE
To make it easy to restore a database using a script I designed a function to figure out the location for the default Data path (and another one for the default log path).
The locations of these paths I got from
http://msdn.microsoft.com/en-us/library/ms143547.aspx
The output can be stored in a 260 character variable (260 as specified MAX_PATH in
http://msdn.microsoft.com/en-us/library/aa365247.aspx).
Edit: Rudi Larno posted a very nice refactored version of the code I posted. (especially note the nice recursion in it.)
View my old code
IF object_id('dbo.fn_get_default_data_path') is not null
DROP FUNCTION dbo.fn_get_default_data_path
GO
CREATE FUNCTION dbo.fn_get_default_data_path()
returns nvarchar(260)
AS
BEGIN
DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @data_path nvarchar(260), @log_path nvarchar(260);
SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER')
-- sql 2005/2008 with instance
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
IF @data_path is null -- sql 2005/2008 default instance
BEGIN
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @data_path output;
SET @data_path = @data_path + '\Data';
END
if @data_path is null -- sql 2000 with instance
begin
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
end
if @data_path is null -- sql 2000 default instance
begin
SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
end
return @data_path
END
GO
As you can see it gets very ugly very fast.
To extract the log path use this function:
IF object_id('dbo.fn_get_default_log_path') is not null
DROP FUNCTION dbo.fn_get_default_log_path
GO
CREATE FUNCTION dbo.fn_get_default_log_path()
RETURNS nvarchar(260)
AS
BEGIN
DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @log_path nvarchar(260);
SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');
-- sql 2005/2008 instance
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
if @log_path is null -- sql 2000 with instance
begin
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
end
if @log_path is null -- sql 2000 default instance
begin
SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
end
IF @log_path is null
BEGIN
SELECT @log_path = dbo.fn_get_default_data_path()
END
return @log_path
END
GO
IF object_id('dbo.fn_get_default_path') is not null
DROP FUNCTION dbo.fn_get_default_path
GO
CREATE FUNCTION dbo.fn_get_default_path(@log bit)
RETURNS nvarchar(260)
AS
BEGIN
DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path nvarchar(260), @value_name nvarchar(20);
SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');
-- sql 2005/2008 with instance
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
SET @value_name = N'DefaultData'
IF @log = 1
BEGIN
SET @value_name = N'DefaultLog';
END
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
IF @log = 0 AND @path is null -- sql 2005/2008 default instance
BEGIN
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path output;
SET @path = @path + '\Data';
END
IF @path IS NULL -- sql 2000 with instance
BEGIN
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
END
IF @path IS NULL -- sql 2000 default instance
BEGIN
SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
END
IF @log = 1 AND @path is null -- fetch the default data path instead.
BEGIN
SELECT @path = dbo.fn_get_default_path(0)
END
RETURN @path;
END
GO
The functions can then be used as follows (or you can just copy paste the output):
declare @sql nvarchar(2000),
@data varchar(260),
@log varchar(260);
select @data = dbo.fn_get_default_data_path(),
@log = dbo.fn_get_default_log_path()
select @sql= 'RESTORE DATABASE DefaultLocationDB
FROM DISK = N''c:\backups\DemoDB.bak'' WITH FILE = 1,
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',
MOVE N''demo_log_device'' TO N''' + @log + '\DemoDb.ldf'',
NOUNLOAD, REPLACE'
exec (@sql)
edit: I updated the script for dbo.fn_get_default_log_path() to avoid calling dbo.fn_get_default_data_path() when not needed.