如何从 SQL Server 中的图表创建备份脚本? [英] How can I create backup script from diagram in SQL Server?

问题描述

我使用 SQL Server 2012 和 SQL Server 2008 R2.

I use SQL Server 2012 and SQL Server 2008 R2.

我从数据库中的所有对象(表/触发器/存储过程/函数...)创建了一个脚本.

I create a script from all object (tables / trigger / stored procedure / function ...) in my database.

我从 SQL Server Management Studio 生成了这个脚本.我可以在另一台服务器上用这个脚本重新创建我的数据库.但是在运行我的脚本以创建另一个数据库后，我想念我的数据库的所有图表.

I generated this script from SQL Server Management Studio. I can recreate my database with this scrips on the other server. But I miss all diagrams of my database after run my script for create another database.

因此，我需要从数据库中存在的所有图表创建备份脚本.

Therefore, I need create backup script from all diagrams that exist in my database.

我需要在目标数据库上执行此脚本以重新创建我的所有图表.

I need execute this script on the destination database for recreating all my diagrams.

我找到了这个链接.但我需要一些想法来自动创建所有脚本(插入命令).

I found this Link. but i need some thinks that create all script (Insert Command) automatically.

推荐答案

我找到了一个合理的解决方案.问题是 Management Studio 无法为非 XML 数据显示超过 65535 个字符，并且无法设置为显示超过 65535 个字符.

I have found a reasonable solution. The problem is that Management Studio cannot display more that 65535 characters for Non-XML data, and cannot be set to display more than 65535.

查看文档代码:)

备份脚本:

-- 1. Read from DB, using XML to workaround the 65535 character limit
declare @definition varbinary(max)
select @definition = definition from dbo.sysdiagrams where name = 'ReportingDBDiagram' 

select
    '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@definition") )', 'varchar(max)')
for xml path('')

-- 2. Open the result XML in Management Studio
-- 3. Copy the result
-- 4. Paste this in backup script for @definition variable

恢复脚本:

declare @definition varbinary(max)
set @definition = 0xD0CF -- Paste 0x0 value from Backup script

-- Create diagram using 'official' Stored Procedure
exec dbo.sp_creatediagram
    @diagramname = 'ReportingDBDiagramCopy',
    @owner_id = null,
    @version = 1,
    @definition = @definition

这篇关于如何从 SQL Server 中的图表创建备份脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！