none
How can i restore the db? RRS feed

  • Question

  • How can i restore the database into multiple drives?

    Let say,i have the db which is having 8 mdf files going to different drives(E,F,G,H,I,J...).Is there script is avilable to do that?

     

    Thanks,

    Ron.

    Thursday, May 5, 2011 7:50 PM

Answers

  • This is the script, to use for your environment, you have to make some of the changes.

     

    Step 1: Retrive the Logical file name of the database from backup.
    RESTORE FILELISTONLY
    FROM DISK = 'D:BackUpYourBaackUpFile.bak'
    GO

    Step 2: Use the values in the LogicalName Column in following Step.
    ----Make Database to single user Mode
    ALTER DATABASE YourDB
    SET SINGLE_USER WITH
    ROLLBACK 
    IMMEDIATE

    ----Restore Database
    RESTORE DATABASE YourDB
    FROM DISK = 'D:BackUpYourBaackUpFile.bak'
    WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
    MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

    /*If there is no error in statement before database will be in multiuser
    mode.
    If error occurs please execute following command it will convert
    database in multi user.*/
    ALTER DATABASE YourDB SET MULTI_USER
    GO

     

     

     

     

    http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/


    Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.
    • Marked as answer by KJian_ Thursday, May 12, 2011 8:59 AM
    Thursday, May 5, 2011 7:59 PM