求sql2005中 单表备份的语句
把该表重命名进行全部的备份

[解决办法]

SQL code

IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'tt')DROP TRIGGER tt ON DATABASE;GOCREATE TRIGGER tt ON DATABASEFOR CREATE_TABLEASBEGIN     SET CONCAT_NULL_YIELDS_NULL ON    DECLARE @AffectedTable nvarchar(255),@backUpTable nvarchar(255),@create_Script nvarchar(max)    --获取建表的语句    SELECT  @create_Script=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')    --获取表的名称    SELECT  @AffectedTable = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(255)')    --定义一个备份的表名称    SELECT  @backUpTable =@AffectedTable+'_bk'    --替换表名    SELECT  @create_Script=REPLACE(@create_Script,@AffectedTable,@backUpTable)    --执行建表语句    EXEC(@create_Script)    --给创建的表加一个DML触发器    EXEC('        CREATE TRIGGER tt_'+@AffectedTable+' ON '+@AffectedTable+'        FOR INSERT        AS         BEGIN            INSERT INTO '+@backUpTable+' SELECT * FROM INSERTED        END'    )    SET CONCAT_NULL_YIELDS_NULL OFFEND --> 测试[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(4))GO INSERT [tb]SELECT 1,'土豆' UNION ALL SELECT 2,'水锅'SELECT * FROM tb/*id    name1    土豆2    水锅*/SELECT * FROM tb_bk/*id    name2    水锅1    土豆*/DROP TABLE tb_bk