Restore Database with any number of Ndf files

  • Comments posted to this topic are about the item Restore Database with any number of Ndf files

  • 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.

  • What query have you executed?

  • I have created the stored procedure in Master, then ran the following command:

    exec sp_restoredb N'mydatabase', N'D:\mydatabase.bak'

  • 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.

  • 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.

  • It will restore on existing database only.

  • 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

  • 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