SQL Server Performance

Restoring a Database

Discussion in 'SQL Server 2005 General Developer Questions' started by rcatiggay, Oct 24, 2006.

  1. rcatiggay New Member

    Is there a way to restore a database backup file to the default data directory in a specific SQL 2005 instance using T-SQL?

    I know that you can use the command RESTORE DATABASE, but that will restore the database files back to it original location. I also know that I can use the WITH MOVE option also, but is there a keyword or some special notation that I can use to make sure that the data/log files are moved to the default data directory for a specific SQL 2005 instance?

    I am trying to avoid cases where the original file location does not exists in the current file system.

    Thanks in advance,
    Rodney
  2. satya Moderator

    No, you must use WITH MOVE option in order to write the data & log files to new location.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. DilliGrg Member


    This is what you are referring to?



    RESTORE DATABASE MyDatabase
    FROM DISK = 'C:Documents and SettingsMy DocumentsMyDatabaseMyDatabase_20060912.BAK'
    WITH NORECOVERY,
    MOVE 'MyDatabase_DATA' TO 'C:program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.mdf',
    MOVE 'MyDatabase_ix' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataMyDatabase_indexes.ndf',
    MOVE 'MyDatabase_LOG' TO 'C:program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.ldf'


    Thanks,
    DilliGrg
  4. satya Moderator

    Yes and as per the Books online it is only recommended method.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page