How to restore multiple mdf files to a single mdf? (4234667)

How to restore multiple mdf files to a single mdf?

How to restore multiple mdf database files to a single mdf file.

If the backup file was created with 1 mdf and 1 ldf, additional data/log files cannot be created during the restore process (this is a limitation of SQL Server).

In the example below, the source database has 1 mdf, 1 ndf and 1 ldf -- the destination database has 1 mdf and 1 ldf file. After executing, the source has 3 files and the destination also has 3 files. Note: The REPLACE parameter is required.

exec master.dbo.xp_restore_database
@database = N'Two files'
, @filename = N'D:\Backup\Three files200701191128LiteSpeed_Full.BKP'
, @filenumber = 1
, @with = N'RECOVERY'
, @with = N'NOUNLOAD'
, @with = N'STATS = 10'
, @with = N'REPLACE'
, @with = N'MOVE N''Three files_Data'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL\data\Two files_Data.mdf'''
, @with = N'MOVE N''Three files_Data1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL\data\Two files_Data1.ndf'''
, @with = N'MOVE N''Three files_Log'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL\data\Two files_Log.ldf'''

Versions 2000 & 2005 of SQL Server
All versions of LiteSpeed