Post reply

Database restore from disk with multiple datafiles

  • July 28, 2005 at 4:02 pm

    #94116

    Hey all,

    I have a question.  I am trying to restore 5 datafiles, 3 index files and 1 log file to a database.  I am getting a message in my restore procedure. Can someone take a look and give me some pointers?

    Thanks.

     

    RESTORE DATABASE Alexandria_Old 

       FROM Disk =

    'd:\mssql2000\backup\joeybackups\Alex1_bkup.bak',

    'd:\mssql2000\backup\joeybackups\Alex2_bkup.bak',

    'd:\mssql2000\backup\joeybackups\Alex3_bkup.bak',

    'l:\mssql2000\backup\Alex4_bkup.bak',

    's:\mssql2000\backup\Alex5_bkup.bak'

       WITH MOVE 

    'Alexandria_old_data_1'

    TO 'd:\mssql2000\Data\Alexandria_old_data_1_data.mdf', 

    MOVE 'Alexandria_old_data_2'

    TO 'd:\mssql2000\Data\Alexandria_old_data_2_data.mdf',

         

    MOVE 'Alexandria_old_data_3'

    TO 'd:\mssql2000\Data\Alexandria_old_data_3_data.mdf',

         

     MOVE 'Alexandria_old_data_4'

    TO 'd:\mssql2000\Data\Alexandria_old_data_4_data.mdf',

         

     MOVE 'Alexandria_old_data_5'

    TO 'd:\mssql2000\Data\Alexandria_old_data_5_data.mdf', 

         

     MOVE 'Alexandria_old_log'

    TO 'l:\mssql2000\log\Alexandria_old_log.ldf'

          RECOVERY, STATS=1

  • July 28, 2005 at 6:52 pm

    #578046

    What is the message you are getting?

  • July 28, 2005 at 7:14 pm

    #578047

    Sorry I didn't post before, it must be a syntax issue, or at least that is what the following message leads me to believe.

    Server: Msg 170, Level 15, State 1, Line 5

    Line 5: Incorrect syntax near 'd:\mssql2000\backup\joeybackups\Alex2_bkup.bak'.

  • July 28, 2005 at 8:26 pm

    #578059

    Hi,

    As per my knowledge, I don't think this is possible i.e. restoring multiple backups to a single database and also the thing here is, u cannot have multiple mdf files for one database. the primary file .mdf should be one and only one for a database, u can have n number of secondary data files called .ndf..

    Hope that helps..

    Regards,

    Dilip

  • July 28, 2005 at 9:22 pm

    #578070

    I tried to change all of them to .ndf except for the first and it still didn't work.  I know that you can restore more than one backup device, and in enterprise manager you can chose more than one file, I just don't know the syntax of it.

    Thanks.

  • July 29, 2005 at 1:28 am

    #578102

    I tried thru QA, but it didnt work, I think your's is full backup and from Ent Manager, i guess u can do multiple backups for diff backups..I even tried thru Ent- created 2 backups for northwind nwind1.bak and nwind2.bak, was able to attach multiple files, but when i click ok, it won't happen..I hope better u post this in Backups section where you can get more advanced help on this one..Best of luck

    Dilip

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

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