Log Shipping Wizard Restores Log Files to Wrong Location

Eddy!

I am setting up Log Shipping between two servers using the SQL Backup wizard with the Overwrite existing database option.

The problem I am having is that the wizard is restoring the files to the wrong locations.

For example, my files on the primary (Live) server are located in the following locations:
F:\MSSQL\Data\<Database>.mdf
G:\MSSQL\Log\<Database>.ldf
(Setup for OLTP so on separate spindles as best practise).

The files on the standby server live in the following location:
\MSSQL\Data\<Database>.mdf
\MSSQL\Log\<Database>.ldf
(All on a RAID 5 for OLAP type operations)

When restoring to the standby server I would assume this would restore to the same directories as the existing but it defaults to:
\MSSQL\Data\<Database>.mdf
\MSSQL\Data\<Database>.ldf

This therefore means it is dropping and re-creating the log file and not overwriting the existing one. Is there a setting to get round this or a way to generate a script for the whole log shipping process because by detaching and re-attaching the database after moving the log file puts it into recovered mode. I don't want to be restoring the databases twice with a move option when i come to doing our DR site which has a lot more complex disk subsystem. i.e the same as Live.

There is also the option to create a new database but when were talking 100's GB of data this takes sometime to write out the size of the files to disk which i don't want to do.

Eddy

Jason Cook

Currently SQL Backup 5's Log Shipping Wizard will use the SQL Server data directory by default when overwriting the database, we realise this isn't always the right solution so we are looking into improvements for a future release.

The following document may help with setting up log shipping manually, if you want to use an existing standby database. The actual log shipping process simplifies down to two jobs, which are discussed at this link:

http://www.yohz.com/logship.html

If you need any more help, please let us know.

Thanks,
Jason

Eddy!

Jason,

Thanks for the reply.

Something else i have just noticed whilst restoring through this wizard is the physical name has been changed of the database on the standby server for a particular database being log shipped with more than one datafile associated with it.

i.e. i have a database on my primary server with 2 data files:

<DatabaseName>_Live_Data.MDF
<DatabaseName>_Live_1_Data.NDF

This has been created on the stand by server as:

<DatabaseName>_Live_1.MDF
<DatabaseName>_Live_3.NDF

Is this another known bug that exists within using this option?

Eddy

Jason Cook

Hi,
While the renaming is not a bug as such, the log shipping wizard doesn't provide the same opportunity to rename the files as the restore wizard does (since they both use the same logic). The lack of ability to change the file names is the problem, and would be handy to have available in more advanced cases.

The idea of the log shipping wizard is to provide the ability to set up the log shipping simply. If you wish to use more advanced logic (such as an existing database or want to rename the database files), then currently you will need to use the document I mentioned previously. However we are considering making changes to this in a future release.

Hope that helps,
Jason