restore to multiple datafiles

• Grumpy DBA

  SSChampion

  Points: 12597

  More actions

  November 12, 2007 at 9:08 am

  #200429

  Background: SQL 2005, old database has 1 filegroup (Primary) and .mdf and .ldf on same drive.

  New server: created database with Primary filegroup spanning 4 drives on SAN (transaction log on separate drive).

  Problem: I would like to restore the database to the new server and have the datafiles spread across the 4 files in the Primary filegroup. I cannot find a way to restore the database to multiple datafiles (maybe a result of a long weekend in Vegas...). It would be nice to be able to do the following:

  RESTORE DATABASE [ABCD] FROM DISK = N'K:\Temp\ABCD.bak' WITH FILE = 1,

  MOVE N'ABCD_Data' TO

  N'G:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCD_01.mdf,

  H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCD_02.ndf,

  I:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCD_03.ndf,

  J:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCD_04.ndf',

  MOVE N'ABCD_Log' TO N'K:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCD_log.ldf', NOUNLOAD, REPLACE, STATS = 10

  GO

  Thanks in advance - Grumpy

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737981

  More actions

  November 12, 2007 at 4:07 pm

  #750407

  I don't believe you can do this directly because of the mapping of pages to logical files. My guess is you need to:

  1. restore to db with 1 MDF/ 1 LDF

  2. Create 3 new NDFs

  3. Move data (clustered indexes, recreate objects, etc.) to new files.

• Grumpy DBA

  SSChampion

  Points: 12597

  More actions

  November 13, 2007 at 6:35 am

  #750640

  Thanks, Steve. After researching the topic for a while and not finding anything that said it could be done I figured I would have to restore the file to 1 mdf and 1 ldf, create the additional data files and start moving data.

  - Grumpy

• crcoracle

  Right there with Babe

  Points: 778

  More actions

  February 1, 2018 at 4:14 am

  #1978157

  11 years later... Is it still impossible to do this?
  In SQL Server 2012.... 2014... 2016... ???

  Up!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 1, 2018 at 5:00 am

  #1978163

  A restore recreates the DB exactly as it was at the time of backup. If you want multiple files, restore, then split.

  Please rather start a new thread for your question in future.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass