18

I need to programmatically (T-SQL) restore a database backup in SQL Server 2008. This backup comes from a different server and the original database name may have been different as well. (In effect, I am copying a database from some server to a new database on another server.)

Apparently, I have to use RESTORE DATABASE ... WITH MOVE, which works, but I would need to know where the files should be restored to and how they should be named. Despite searching, I haven't found what would seem to be the most basic task: just use the default path and filenames, i.e., do whatever the SQL Server does when you issue a CREATE DATABASE - you don't have to specify the path for that command, so why is it needed for a RESTORE?

Have I overlooked some trivial solution, or will I really have to list the files, somehow find the database directory from the SQL Server configuration, and use that information to construct the RESTORE command? Thanks.

(Comparing with PostgreSQL, where when using pg_restore, you specify a destination database that is already created; and when you were creating it you had the option - not mandatory - of specifying a non-default tablespace, but if you didn't, you just don't care where the files are physically stored.)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vashekcz
  • 561
  • 1
  • 4
  • 16

7 Answers7

20

For future Googlers; from SQL Server 2012 onwards you can use:

SELECT ServerProperty(N'InstanceDefaultDataPath') AS default_file
     , ServerProperty(N'InstanceDefaultLogPath') AS default_log
;

This reads the folder paths that appear under the Server Properties > Database Settings > Database Default Locations

Server Properties > Database Settings

gvee
  • 16,732
  • 35
  • 50
7

I wrote a function a while ago which does this for you. It supports all versions of SQL Server including installations with default instance and named instances. See here for the code and an example

To do the restore you will call the function like shown below.

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_path(0),
       @log = dbo.fn_get_default_path(1)

SELECT @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.mdf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)
Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
4

The CREATE DATABASE command will create the database with the default paths, so you can create a database then restore over it using the replace syntax (sqlcmd mode):

:setvar DatabaseName MyDatabase
declare @fileName varchar(255)
set @fileName = 'c:\backup\mybackup.bak'

if db_id('$(DatabaseName)') is null
            CREATE DATABASE [$(DatabaseName)]

RESTORE DATABASE [$(DatabaseName)]
FROM DISK = @fileName
WITH REPLACE
David Martin
  • 11,764
  • 1
  • 61
  • 74
  • 5
    then I try this in sqlcmd or SQL Query I get: `File 'MyDBnameHere' cannot be restored to 'M:\myOLDserverPath\MyDBnameHere.mdf'. Use WITH MOVE to identify a valid location for the file.` – Tilo Aug 05 '16 at 21:49
3

I had to do this myself and I ended up combining a few answers to create a single script that restores a database (any database) and remaps the files. All you have to know is the backup file's path and the new databases name. Unfortunately it doesn't look like there are any succinct solutions to this problem.

DECLARE @Source nvarchar(max) = 'C:\backup.bak'
DECLARE @RestoreDestination nvarchar(max) = 'NewDatabaseName'

--https://stackoverflow.com/a/27893494/5734516
--https://stackoverflow.com/a/4018782/5734516
DECLARE @fileListTable TABLE (
    [LogicalName]           NVARCHAR(128),
    [PhysicalName]          NVARCHAR(260),
    [Type]                  CHAR(1),
    [FileGroupName]         NVARCHAR(128),
    [Size]                  NUMERIC(20,0),
    [MaxSize]               NUMERIC(20,0),
    [FileID]                BIGINT,
    [CreateLSN]             NUMERIC(25,0),
    [DropLSN]               NUMERIC(25,0),
    [UniqueID]              UNIQUEIDENTIFIER,
    [ReadOnlyLSN]           NUMERIC(25,0),
    [ReadWriteLSN]          NUMERIC(25,0),
    [BackupSizeInBytes]     BIGINT,
    [SourceBlockSize]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32) -- remove this column if using SQL 2005
)

DECLARE @QueryCommand nvarchar(max) =
 'RESTORE FILELISTONLY FROM DISK = N''' + REPLACE(@Source, '''', '''''') + ''' WITH NOUNLOAD'

INSERT INTO @fileListTable 
EXEC(@QueryCommand)


--https://stackoverflow.com/a/2014673/5734516
DECLARE @filepath NVARCHAR(260)
EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
        N'DefaultData', 
        @filepath output, 'no_output'


DECLARE @Command nvarchar(max) 
SET @Command = 'RESTORE DATABASE ' + QUOTENAME(@RestoreDestination)  + ' '
                + ' FROM DISK=N''' + REPLACE(@Source, '''', '''''') + ''' '
                + 'WITH FILE=1 '
SELECT 
    @Command = @Command + ',MOVE N''' + REPLACE(LogicalName, '''', '''''') + '''' 
    + ' TO N'''  
        + REPLACE(@filepath + '\' + @RestoreDestination + case when FileID = 1 then '.mdf' when Type = 'L' then '_' + CAST(FileID as varchar(max)) + '.ldf' else '_' + CAST(FileID as varchar(max)) + '.ndf' end, '''', '''''')
    + '''' 
FROM @fileListTable 

SELECT @Command
EXEC(@Command)

Note: I know this is an old post but though it could be useful for someone going forward.

webwake
  • 1,154
  • 1
  • 13
  • 26
2

As mentioned, CREATE DATABASE creates files in default location. Then you can find paths of those files and use those in the RESTORE part

-- Database will be created in default location
CREATE DATABASE [MyRestoredDatabase] 

DECLARE @mdfFile VARCHAR(MAX)
DECLARE @ldfFile VARCHAR(MAX)

SELECT @mdfFile = physical_name
FROM sys.master_files
WHERE database_id = DB_ID('MyRestoredDatabase')
AND file_id = 1

SELECT @ldfFile = physical_name
FROM sys.master_files
WHERE database_id = DB_ID('MyRestoredDatabase')
AND file_id = 2

-- Overwrite known files of the new database from restore 
RESTORE DATABASE [MyRestoredDatabase] 
FROM DISK='C:\Path\To\OriginalDatabase.bak'
WITH REPLACE,
    MOVE 'OriginalDatabase' TO @mdfFile,
    MOVE 'OriginalDatabase_Log' TO @ldfFile

If you need find out logical names, use following SQL

RESTORE FILELISTONLY
FROM DISK='C:\Path\To\MyRestoredDatabase.bak'

Fully automated script can be found here

Community
  • 1
  • 1
Ondrej
  • 1,209
  • 1
  • 11
  • 21
2

You have to read register, like

DECLARE @Result NVARCHAR(4000) 
EXECUTE [master].[dbo].xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', 
@Result OUTPUT,  
'NO_OUTPUT' 
SELECT @Result
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Artineer
  • 11
  • 1
  • Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 22 '12 at 06:01
0

If you mean using the New Database option of the Management Studio, then it does default some values. But the T-SQL for CREATE DATABASE still requires the paths. You can see this by going to the management studio, select New Database, enter the new name, and click the Script button. This button will provide you the T-SQL that the SQL Server will run. This process does not actually run, so you can cancel out of the New Database wizard.

There is also a similar button when you restore. So if you have all the information already and just need the T-SQL, go through the management studio to do a restore but instead of clicking OK to run the process, click the Script button which will generate you the T-SQL with the default values.

Hope this helps.

Douglas
  • 11
  • 1