September 28, 2013 at 2:26 pm
Comments posted to this topic are about the item Restore Database with any number of Ndf files
September 30, 2013 at 5:03 pm
I get the following error when trying to run the restore command:
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
October 1, 2013 at 2:40 am
What query have you executed?
October 1, 2013 at 7:13 am
I have created the stored procedure in Master, then ran the following command:
exec sp_restoredb N'mydatabase', N'D:\mydatabase.bak'
October 2, 2013 at 10:04 pm
If you are executing
exec sp_restoredb N'mydatabase', N'D:\mydatabase.bak'
it means database on which you want to do restore is mydatabase with mdf,ldf and one or more ndf files and backup using which you want to do restore operation is mydatabase.bak(having same number of ndf files as that of my database on which restore will occur).Kindly check this, it its fine then no error may occur while execution.
October 3, 2013 at 4:58 am
Here are the steps I have followed:
1.Took a full back up of an existing database (abc.bak)
2.Executed your script, but using entirely new database name thinking that it will create a new database and restores the data from the backup.
So your script works only if the target database already exists. I was thinking even if it doesn't exist it will create a database with new name.
PLEASE CLARIFY MY UNDERSTANDING IS CORRECT.
October 3, 2013 at 10:18 pm
It will restore on existing database only.
March 6, 2015 at 12:30 am
And it won't restore a database backup that has more files than the database you're replacing.
I have been using the following code for generating the 'move' parameters, which handles this situation by putting new files in the default file path:
DECLARE @Move nvarchar(max);
SET @Move = NULL
SELECT @Move = COALESCE(@Move + ',
', ' ') + 'MOVE N''' + lfn.LogicalName + ''' TO N'''
+ COALESCE(mf.physical_name, -- Use filename and path from existing file
-- Or put new files in 'default paths':
CASE lfn.[Type]
WHEN 'D' THEN CONCAT(CONVERT(nvarchar(max), SERVERPROPERTY('instancedefaultdatapath')), LogicalName, '.mdf')
WHEN 'L' THEN CONCAT(CONVERT(nvarchar(max), SERVERPROPERTY('instancedefaultlogpath')), LogicalName, '.ldf')
END
) + ''''
FROM #logicalfilenames lfn
LEFT JOIN (
SELECT DatabaseName = d.name, mf.name, mf.physical_name
FROM sys.master_files mf
JOIN sys.databases d ON d.database_id = mf.database_id
) mf ON mf.name = lfn.LogicalName AND mf.DatabaseName = @dbname
April 27, 2016 at 4:57 pm
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply