Sql-server – Restore Database with Move Failure

restoresql serversql server 2014

I am attempting to automate restoring .bak files on a secondary database in the case of a primary server failure. Both servers are SQL server 2014.

Here is a sample SQL script:

USE master
GO

DECLARE @DBName varchar(50)
DECLARE @BackupFile varchar(256)

Set @DBName = 'test-db'
Set @BackupFile = 'D:\MSSQL\Backups\test-db\test-db_backup_2016_03_18_001622_2611392.bak'



-- Find Logical and Physical file names
declare @LogicalDataFile      sysname
        , @LogicalLogFile       sysname
        , @PhysicalDataFile     nvarchar(260)
        , @PhysicalLogFile      nvarchar(260)

-- Data file
select  @LogicalDataFile = name
        , @PhysicalDataFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'ROWS'

-- Log file
select  @LogicalLogFile = name
        , @PhysicalLogFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'LOG'


-- Print variables
Print 'Logical Data File: ' + @LogicalDataFile 
Print 'Logical Log File: ' + @LogicalLogFile
Print 'Physical Data File: ' + @PhysicalDataFile
Print 'Physical Log File: ' + @PhysicalLogFile


-- Set as single user mode
EXEC('ALTER DATABASE [' + @DBName
+ '] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE')


--DB that will be over written
RESTORE DATABASE [@DBName] 
--Restore file with data
FROM DISK = @BackupFile
WITH MOVE @LogicalDataFile TO @PhysicalDataFile,
MOVE @LogicalLogFile TO @PhysicalLogFile,
REPLACE,
STATS=10

-- Return to multi-user mode
EXEC('ALTER DATABASE [' + @DBName 
+ '] SET MULTI_USER')

When I run the above script I get the following output:

Logical Data File: test-db
Logical Log File: test-db_log
Physical Data File: D:\MSSQL\Data\test-db.mdf
Physical Log File: D:\MSSQL\Data\test-db_log.ldf
Msg 1834, Level 16, State 1, Line 50
The file 'D:\MSSQL\Data\test-db.mdf' cannot be overwritten.  It is being used by database 'test-db'.
Msg 3156, Level 16, State 4, Line 50
File 'test-db' cannot be restored to 'D:\MSSQL\Data\test-db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 50
The file 'D:\MSSQL\Data\test-db_log.ldf' cannot be overwritten.  It is being used by database 'test-db'.
Msg 3156, Level 16, State 4, Line 50
File 'test-db_log' cannot be restored to 'D:\MSSQL\Data\test-db_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 50
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 50
RESTORE DATABASE is terminating abnormally.

I wanted to make sure that the MOVE statements weren't being ignored for some reason, so I changed the variable names for the logical and physical file names – as expected, I got an error stating that the variables must be declared.

Is there something else that needs to be done to allow the database to be overwritten? There are no other connections to this database.

Best Answer

You're trying to restore a database named [@DBName] (that's the literal name since it is surrounded by square brackets) onto files for the database named [test-db]. Remove the [ and ] from the [@DBName] piece of your code and you'll see you cannot use variable names in a RESTORE DATABASE command.

This needs to be performed with dynamic SQL. The list of files to move should be generated by looking at RESTORE FILELISTONLY FROM DISK = 'C:\some_db_backup.bak';

Here is a very basic example of how to generate a RESTORE DATABASE command using dynamic SQL, for SQL Server 2005 to SQL Server 2012 (i.e. this is not tested on 2014, and may or may not work without modification there):

SET NOCOUNT ON;
DECLARE @FileListCmd NVARCHAR(MAX);
DECLARE @RestoreCmd NVARCHAR(MAX);
DECLARE @cmd NVARCHAR(2000);
DECLARE @BackupFile NVARCHAR(MAX);
DECLARE @DBName SYSNAME;

DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT DECIMAL(10,2);

SET @BackupFile = 'C:\temp\backup.bak';
SET @DBName = 'SomeNewDatabase';

SET @cmd = '';
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(DECIMAL(10,2), 
    SUBSTRING(@Version,1 ,CHARINDEX('.',@Version, 4)-1));
IF @VersionINT >= 10.5
BEGIN
    -- WITH TDE OPTION
    DECLARE @FileList TABLE (
        LogicalName SYSNAME
        , PhysicalName VARCHAR(255)
        , Type CHAR(1)
        , FileGroupName SYSNAME NULL
        , Size NUMERIC(20,0)
        , MaxSize NUMERIC(20,0)
        , FileId BIGINT
        , CreateLSN NUMERIC(25,0)
        , DropLSN NUMERIC(25,0) NULL
        , UniqueId UNIQUEIDENTIFIER
        , ReadOnlyLSN NUMERIC(25,0) NULL
        , ReadWriteLSN NUMERIC(25,0) NULL
        , BackupSizeInBytes BIGINT
        , SourceBlockSize INT
        , FileGroupId INT
        , LogGroupGUID UNIQUEIDENTIFIER NULL
        , DifferentialBaseLSN NUMERIC(25,0) NULL
        , DifferentialBaseGUID UNIQUEIDENTIFIER
        , IsReadOnly BIT
        , IsPresent BIT 
        , TDEThumbprint VARBINARY(32)
    );
END
ELSE
BEGIN
    --NO TDE OPTION
    DECLARE @FileListNoTDE TABLE (
        LogicalName SYSNAME
        , PhysicalName VARCHAR(255)
        , Type CHAR(1)
        , FileGroupName SYSNAME NULL
        , Size NUMERIC(20,0)
        , MaxSize NUMERIC(20,0)
        , FileId BIGINT
        , CreateLSN NUMERIC(25,0)
        , DropLSN NUMERIC(25,0) NULL
        , UniqueId UNIQUEIDENTIFIER
        , ReadOnlyLSN NUMERIC(25,0) NULL
        , ReadWriteLSN NUMERIC(25,0) NULL
        , BackupSizeInBytes BIGINT
        , SourceBlockSize INT
        , FileGroupId INT
        , LogGroupGUID UNIQUEIDENTIFIER NULL
        , DifferentialBaseLSN NUMERIC(25,0) NULL
        , DifferentialBaseGUID UNIQUEIDENTIFIER
        , IsReadOnly BIT
        , IsPresent BIT 
    );
END

SET @FileListCmd = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + ''';';

IF @VersionINT >= 10.5
    INSERT INTO @FileList
    EXEC (@FileListCmd);
ELSE
BEGIN
    INSERT INTO @FileListNoTDE
    EXEC (@FileListCmd);
    INSERT INTO @FileList 
    SELECT *, NULL
    FROM @FileListNoTDE;
END

DECLARE @MoveFiles NVARCHAR(MAX);
SELECT @MoveFiles = (SELECT ', MOVE ''' + fl.LogicalName + ''' TO ''' 
    + 'C:\Database\Data\' + @DBName + '\' 
    + CASE WHEN fl.FileGroupName = 'PRIMARY' THEN 'system' 
      WHEN fl.FileGroupName IS NULL THEN 'Log' 
      ELSE fl.FileGroupName END + '\' + fl.LogicalName + '''
    '
FROM @FileList fl
FOR XML PATH(''));

SET @MoveFiles = REPLACE(@MoveFiles, '
', '');
SET @MoveFiles = REPLACE(@MoveFiles, CHAR(10), CHAR(13) + CHAR(10));
SET @MoveFiles = LEFT(@MoveFiles, LEN(@MoveFiles) - 2);

SET @RestoreCmd = 'RESTORE DATABASE ' + @dbName + '
FROM DISK = ''' + @BackupFile + ''' 
WITH REPLACE 
    , RECOVERY
    ' + @MoveFiles + ';
GO';

PRINT @RestoreCmd;
Related Question