[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

Poor mans Log shipping over the internet?

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

2 Solutions
Is db_office in Full recovery model?

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.
cpctechAuthor Commented:
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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now