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

SQL 2008 Replication

We are testing ways of backing up are SQL database using replication.

So I have a test setup of 2 servers both systems running server 2008, with SQL server 2008 running.

Ive imported our database and created a local publication on one machine.

Then on the 2nd machine, the subcription is running successfully.

The problem is the snapshop agent fails during the replication with the error:
Message: Script failed for Table 'dbo.sysdiagrams'.

heres the rest of the error message:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[])
Message: Script failed for Table 'dbo.sysdiagrams'.
Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0
Source: Microsoft.SqlServer.Smo
Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingOptions)
Message: Column definition in object sysdiagrams contains type VarBinaryMax, which is not supported in the target server version, SQL Server 2000.
Stack: at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingOptions options)
at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)
at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

  • 2
1 Solution
this is a system table so there is no point in replicating it
why don't you use mirroring and log shipping?
Dave_ANDAuthor Commented:
Could you expand on your answer?

Were looking for fault tolorence without using clustering. would this method provide a solution?

log shipping is a technique to replicate an entire database
you have an active database, and every time it switches a log file, the log file is sent to a secondary database which applies the log file and becomes more updated.
this could be a solution for fault tolorence but you must consider the following factors:
1) the target database will be more behind the primary database when using the method compared to using replication
2) the target database is not available during regular operation (when the primary database is available) as opposed to the replication method
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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