.NET error when trying to create snapshot for transactional Replication

In the end I simply did what the server wanted, i.e. created all the "publication" tables (*dbo.MSpeer_** and *dbo.sys**) in the problematic database (the one that was the publisher), through Script Table As -> CREATE To dialog. Thanks to this server allowed me to disable publishing and distribution. Then I only needed to reconfigure replication.

Hope, next time it crashes (actually I hope it won't), we'll be wise enough to backup tables involved in replication thoroughly.

Additional information by Zane, originally left in a comment:

The tables dbo.MSpeer_* and dbo.sys* can also be created by configuring the distributor/publisher on a different server with SQL Server installed. This will create these tables in the database that you've set for publishing. See -> Tables - >System Tables in SSMS.

Then, generate the schemas for all these tables and then create these tables in the problematic database which will allow you to disable distribution and publishing and reconfigure the distributor.

There are a variety of ways you can do this.

If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in a very quick transaction, you switch the copies of the table between schemas, and it's essentially a metadata operation. I describe this in detail in the following two posts:

• Schema Switch-A-Roo
• Schema Switch-A-Roo : Part 2

If you need most or all of the database, then it is going to be much simpler to use log shipping to facilitate readable secondaries. It just so happens that today I published a very thorough walk-through of creating readable secondaries with Express:

• Readable Secondaries on a Budget

Essentially, you play round-robin with which instance gets read from at any one time; since you want multiple active simultaneously, it would only be a little bit more complex. You might have two or four instances of Express on each web server, and every 15 minutes you switch the standby on all of the servers to have the most recent data.

Since you're on Express for both source and destination, in either case, you will need an agent of some kind to schedule the daily (or more frequent) pushes.