What are the challenges you face when working across database platforms? Take the survey

Log shipping Question

NewDBAGirlNewDBAGirl Posts: 17
edited September 30, 2009 3:30PM in SQL Backup Previous Versions
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.


  • Options
    Eddie DEddie D Posts: 1,791 Rose Gold 5
    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
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.