Poor mans Log shipping over the internet?

Posted on 2007-08-02
Last Modified: 2009-08-04
Ok here is the mess...

I have a production server on the web that handles no more than 1500 transactions per day.

I have a mirror of that server at the office.

Without using replication....

Keep them in sync.

Here is what I have done so far.

webSQL -> SQL2000
offSQL -> SQL2000

webSQL create a transaction log backup and FTP it to offSQL
execute a stored proc on offSQL to restore the trn file

All is working up until I issue...

RESTORE LOG [db_office] FROM  
DISK = N'E:\FTP_ROOT\DBBU\web_08022007_110743_LOG_bu.trn'

I get the following...

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Now I have a migraine and a dent in the wall.

Any suggestions?

I would like to issue the command

Question by:cpctech
    LVL 27

    Assisted Solution

    Is db_office in Full recovery model?

    LVL 21

    Accepted Solution

    When you do the initial full backup restore, you use norecovery (or standby).  Then subsequent log backups get shipped and applied by restoring them with norecovery (or standby).  It isn't until you need to start using the log shipping destination database for real that you issue the "with recovery" at which point you can't ship any more logs to it without starting over from the initial backup.
    LVL 2

    Author Comment

    Breakdown of the points awarded and why

      No it was not, thanks for reminding me

     What you stated
    "with recovery" at which point you can't ship any more logs to it without starting over from the initial backup.

    will kill the though process

    The database is 36 gig and would take far too long

    So I went with triggers behind the tables that create SQL scripts (insert, update, delete) and placed the SQL statements in another table in the DB. then I have a .NET command line tool that will open the list of sql statments and execute them against the remote server in the order they where executed on the local server.

    Kind of a poor mans one way transactional replication...

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now