SQL 2005中的备份架构脚本 [英] Backup schema scripts in sql 2005

问题描述

我们如何使用脚本在sql 2005中备份模式的数据?

how can we backup data of a schema in sql 2005 using scripts ?

推荐答案

试试这个

Hi,
Try This one

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0 

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1 

        -- Not master, tempdb or model
    and db_name(s_mf.database_id) not in (''Master'',''tempdb'',''model'')
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)    
    set @DBFileName = datename(dw, getdate()) + '' - '' + 
                       replace(replace(@DBName,'':'',''_''),''\'',''_'')

    exec (''BACKUP DATABASE ['' + @DBName + ''] TO  DISK = N''''c:\db backup\'' + 
        @DBFileName + '''''' WITH NOFORMAT, INIT,  NAME = N'''''' + 
        @DBName + ''-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD,  STATS = 100'')

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

最好的方法是直接从SQL Management Studio中生成架构脚本.

在Codeproject上有一篇文章涉及此主题: SQL SERVER-2005-创建脚本以复制数据库架构和所有对象-存储过程，函数，触发器，表，视图，约束和所有其他数据库对象 [ ^ ]

The best way is to generate the schema script directly from the SQL Management Studio.

There is an article on Codeproject covering this topic: SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects[^]

这篇关于SQL 2005中的备份架构脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！