首页 > 代码库 > SQLServer备份指定表
SQLServer备份指定表
先定义表类型:
CREATE TYPE t_bak_tables AS TABLE(table_name VARCHAR(200))
然后再定义备份过程:
CREATE PROC sp_bak_tables @tbs t_bak_tables READONLY, @bak_path VARCHAR(200) AS DECLARE @now VARCHAR(8) = CONVERT(VARCHAR(8),GETDATE(),112), @tb_name VARCHAR(100) DECLARE @bak_db_name VARCHAR(100) = ‘db_bak_‘ + @now EXEC(‘CREATE DATABASE ‘ + @bak_db_name) DECLARE c_tbs CURSOR LOCAL FOR SELECT * FROM @tbs OPEN c_tbs FETCH NEXT FROM c_tbs INTO @tb_name WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sql VARCHAR(1000) = ‘SELECT * INTO ‘ + @bak_db_name + ‘.dbo.‘ + REPLACE(@tb_name,‘.‘,‘_‘) + ‘ FROM ‘ + @tb_name EXEC(@sql) FETCH NEXT FROM c_tbs INTO @tb_name END CLOSE c_tbs DEALLOCATE c_tbs DECLARE @bak_file VARCHAR(100) = @bak_path + ‘db_bak_‘ + @now + ‘_‘ + CAST(DATEDIFF(s,{d‘1970-01-01‘},GETDATE()) AS VARCHAR(100)) + ‘.bak‘ EXEC(‘BACKUP DATABASE ‘ + @bak_db_name + ‘ TO DISK = ‘‘‘ + @bak_file + ‘‘‘‘) EXEC(‘DROP DATABASE ‘ + @bak_db_name)
最后调用方法如下:
DECLARE @tmp_tb bak_tables INSERT INTO @tmp_tb VALUES (‘table_a‘),(‘table_b‘) EXEC sp_bak_tables @tmp_tb ‘D:\BACKUP\‘
参考: SQL Server 备份、还原、附加、分离数据库 批量语句
SQLServer备份指定表
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。