Restoring a Database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restoring a Database

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

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