Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have several database backups and I want to restore all of them to a server.

Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and not the ones carried over with the database??

Restore FILELISTONLY FROM DISK='E:\New folder\Thursday - DB_Order.bak'

FROM DISK='E:\New folder\Thursday - DB_Order.bak'
MOVE 'DBOrder_Data' TO 'E:\New folder\Business_Data.mdf',
MOVE 'DBOrder_Log' TO 'E:\New folder\Business_Data_log.ldf'

Using query 1 I get the logical name then those names need to manually be set for query 2, but it’s really difficult when there is more than a single database.

Please help me to write a script where I just pass the physical path of backup file and new database name, then a restore of that database occurs.

If have any questions, please ask. Thanks in advance.

share|improve this question

migrated from Dec 21 '11 at 21:16

This question came from our site for professional and enthusiast programmers.

Take a look @… – benni_mac_b Dec 21 '11 at 20:48
I highly doubt you'll be able to do this totally automated in T-SQL. One possible alternative would be to use SMO (Server Mgmt Objects) from e.g. C# or VB.NET, which could be used to handle all these steps in application code – marc_s Dec 21 '11 at 20:50
+1 on @marc_s comment. I would agree that is probably the best way, though application code. – Thomas Stringer Dec 21 '11 at 20:58
up vote 3 down vote accepted

Here is something that you can use. It is not fully automated but this is definitely very useful piece of script.


    @DBName NVarchar(100),
    @BackupFile NVarchar(1000),
    @DeviceFrom NVarchar(1000),
    @DeviceTo NVarchar(1000),
    @LogicalName NVarchar(1000),
    @PhysicalName NVarchar(1000),
    @SQL NVarchar(MAX),
    @RowsToProcess integer,
    @CurrentRow integer,
    @Comma NVarchar(25);

SET @DBName = 'DB_Order'; --  Change this for each database
SET @BackupFile = 'E:\New folder\Thursday - DB_Order.bak'; --  Change this for each database

SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@DBName + '.mdf',
physical_name) - 1) 
FROM master.sys.master_files
WHERE name = @DBName AND FILE_ID = 1;

SET @DeviceTo = 'E:\New folder\'; -- Change this if you are changing your restore location
SET @SQL = 'RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @BackupFile + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';

    RowID int not null primary key identity(1,1)
    ,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 BIGINT 
    ,FilegroupId BIGINT 
    ,LogGroupGUID uniqueidentifier 
    ,DifferentialBaseLSN numeric(25) 
    ,DifferentialBaseGUID uniqueidentifier 
    ,IsReadOnly BIGINT 
    ,IsPresent BIGINT
    ,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005

SET @RowsToProcess = @@RowCount;

WHILE @CurrentRow < @RowsToProcess
    SET @CurrentRow= @CurrentRow + 1;
    IF @CurrentRow = @RowsToProcess
        SET @Comma = ',STATS=1';
    SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
    SET @PhysicalName = Replace(@PhysicalName,@DeviceFrom,@DeviceTo);
    SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';

--EXEC(@SQL); -- Execute when you are ready.

You can use SQLCMD / Batch file to pass in the parameters of your DB name and physical file name and try to automate it.

share|improve this answer

I have used a PowerShell script like this to restore backups to a new target, resetting paths and filenames:

param ($bakpath, $dbname, $instance, [switch]$force, [switch]$test)

# Restore latest backup from wildcard path, optionally forcing out open connections

# Set local SQL Server file directories here:
$mdfpath = "D:\sqldata"
$ldfpath = $mdfpath

if ((get-pssnapin sqlserverprovidersnapin100 -ErrorAction "SilentlyContinue") -eq $NULL) { add-pssnapin sqlserverprovidersnapin100 }
if ((get-pssnapin sqlservercmdletsnapin100 -ErrorAction "SilentlyContinue") -eq $NULL) { add-pssnapin sqlservercmdletsnapin100 }

if (($bakpath -eq $null) -or ($dbname -eq $null)) {
    Write-Error "Parameters required: -bakpath(file/wildcard) -dbname [-instance] [-force] [-test]"

if ($instance -eq $null) { $instance = "." }

$bak = dir $bakpath | sort -prop LastWriteTime | select -last 1

$go = [Environment]::NewLine + "GO" + [Environment]::NewLine


$files = Invoke-Sqlcmd -Query $sql -ServerInstance $instance

$sql = ""
if ($force) { $sql += "ALTER DATABASE [$dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" + $go }
$sql += "RESTORE DATABASE [$dbname] FROM DISK = '$bak' WITH "

foreach ($file in $files) {
    $lname = $file.LogicalName
    $pname = $file.PhysicalName.Split("\")[-1]
    $ext = $pname.Split(".")[-1]
    $ftype = $file.Type
    " -> {0} ({1}: {2})" -f $lname, $ftype, $pname

    if ($ftype -eq "D") { $sql += "MOVE N'$lname' TO N'$mdfpath\$dbname.$ext', " }
    elseif ($ftype -eq "L") { $sql += "MOVE N'$lname' TO N'$ldfpath\$dbname.$ext', " }

$sql += "REPLACE" + $go
if ($force) { $sql += "ALTER DATABASE [$dbname] SET MULTI_USER" + $go }

if (!$test) { Invoke-Sqlcmd -Query $sql -Verbose -ServerInstance $instance }
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.