June 19, 2018 at 7:26 am
Hi,
i´m struggling with a script to restore a database from disk. I would like to write a SP with two parameters: name of the target database and path to the backupfile.
This script should run as a part of migration scripts on different SQL Server Versions. And that´s where i am struggling...
My approach is to get the logical file names from the backupfile first to build a restore-command with move-clause.
So i tried this:
--some other code before
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),
[SnapshotURL] nvarchar(360)
)
SET @sqlCommand = 'RESTORE FILELISTONLY FROM DISK = ''' + @backupfile + ''''
INSERT INTO @fileListTable EXEC(@sqlCommand)
--some other code below
This code works fine, but not with all backupfiles / all SQL Server Versions.
The script is aborting with the message "The column name or the number of values provided does not match the table definition."
I found out there is another column in filelist called "SnapshotURL" - but this column isn´t allways there.
MS is providing different information about RESTORE FILELISTONLY. In MS Docs there is this column as a part of the resultset starting with SQL Server 2008; in MSDN there it isn´t.
Next try was to add another column to @fileListTable with a default value. Same outcome.
So i started a new approach and modified my exception handling. Now i try INSERT INTO @fileListTable EXEC(@sqlCommand)
and if it fails i declare another table variable with the SnapshotURL column and do it again.
Now i got struck by the next impediment. The script are executed with ant. And the error message resulting from first try causes ant to abbort the whole script. Unfortunaltely i found no way to catch and delete an error (like in the quick 'n dirty days in C++ ;-))
Does anyone have a hint how to handle this or at least a reliable information about wheter this column depends on SQL Server version or not?
Thank you in advance!
June 19, 2018 at 9:59 am
If your objective is to be "version insensitive" to SQL Server versions, then you either have to know the version values and code for them, or you need to eliminate any and all version specific code from the RESTORE functionality. There's no good alternative that I'm aware of. One thing to note is that it probably is long past the time frame where you should even be attempting to support SQL Server versions as old as SQL 2008 (10 or 10.5). It's also quickly approaching the time when SQL 2012 will be in the same category, and given that getting to 2008 is usually necessary before migrating further, given a starting version of 2005 or less, those things probably need to occur fairly soon before it may become a severe challenge to do them at all.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2018 at 10:21 pm
Well, a wise man once said: if you´re feeling lost in complexity, step back, take a look at the bigger picture and simplify your stuff...
Sometimes this really works.
I changed my code like this:--some code before
BEGIN TRY
INSERT INTO @fileListTable EXEC(@sqlCommand)
END TRY
BEGIN CATCH
INSERT INTO @fileListTable2 EXEC(@sqlCommand)
END CATCH
--some code below
@fileTableList is declared without SnapshotURL column, @fileTableList2 is with this column.
If first insert fails second insert is done and works. And, however, no Exception is thrown so ant wont abort the execution of the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply