Post reply

restore to multiple datafiles

  • 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

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

  • 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

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

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply