Log shipping Question

Forum for users of Redgate's SQL Backup tool

Log shipping Question

Postby NewDBAGirl » Tue Sep 29, 2009 6:07 pm

I need to put some objects on the destination server, but are not yet currently on the source server.

They will be added to the source server later. I'm worried that when they are added to the source server it will cause a problem when that transaction log is restored to the destination server since the objects are already there.

Will that cause a problem?

Also, my databases on the destination server are Read Only, I got an error when I was using the database and it tried to restore a log, it failed. Does this mean that the databases can not be used when it tries to do a restore? My boss came up with an idea to use the database on the stand by server to serve a Web site, but it dosen't look like this is going to work.
Posts: 12
Joined: Thu May 03, 2007 2:06 pm

Postby eddie davis » Wed Sep 30, 2009 7:30 pm

Thank you for your into the forum.

I am little puzzled by your question:
I need to put some objects on the destination server, but are not yet currently on the source server.

I am assuming you mean destination database and source database.

SQL Server will not allow you to create new objects on the log shipped destination database.

In a Log Shipping setup, the transaction log backups are restored to the destination database using one of the two recovery states:

    WITH NORECOVERY - Database is non-operational. When the log restore is complete, the database is not returned to ready operational status and it cannot be used for normal operations. You can continue to restore transaction log backup files. Therefore you will be unable to create new objects on the database.

    WITH STANDBY - Database is in a Read Only condition. This is similar to the non-operational condition with some exceptions. When the restore process ends, the database is placed nto Read-Only mode. It is available to accept additional transaction log backups. In Read-only mode you can query the database but you cannot modify the database or add new objects to the database.

If you create new objects in the source database, these actions are recorded in the Transaction Log which is log shipped for restore to the destination database. When you recover the target database and make it operational these new objects will be available in the destination database.

To summarise the above, you cannot create new objects and modify or delete existing objects in the destination database in a Log Shipping set-up.

Many Thanks
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
Posts: 1148
Joined: Wed Jun 14, 2006 2:47 pm
Location: Red Gate Software

Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests