Restore going to the wrong location

tech_contact

we have a set of clustered windows 2003 servers with SQL 2000. The databases are on the R drive, the log files are on S.

We have a second instance of SQL running on the passive node of the cluster that we log ship to for reporting.

I have created a log shipping job and its shipping ok and been restoring ok. The C drive on the second server was getting full so we added some drives and created a D Drive. I detached the database and reattached on D. The problem is now they nightly restore job that runs after the log shipping keeps restoring the data on C.

Where would I change this

Jason Cook

Editing the restore job in management studio, you can modify the restore job to force the files to be in a specified location.

To do this, you need to add the "MOVE" keyword, and specify where each of the database files should move to. This way there is no "default assumption", which may not be what you intended.

For example assuming the following restore command:

master..sqlbackup '-SQL "RESTORE DATABASE [mydatabase] FROM DISK = ''c:\backups\mydatabase.sqb'' WITH RECOVERY "';

To ensure this always goes to the right location, rather than some guessed SQL Server default, you can add the WITH MOVE syntax (a database file, followed by a file location) as follows:

master..sqlbackup '-SQL "RESTORE DATABASE [mydatabase] FROM DISK = ''c:\backups\mydatabase.sqb'' WITH RECOVERY, MOVE ''file1'' TO ''d:\data\file1.mdf'', MOVE ''file_log'' TO ''d:\data\file_log.ldf'' "';

Hope that helps,
Jason

tech_contact

That did the trick

Thanks