SQLServer備份指定表

標籤：style   blog   http   ar   os   sp   for   資料   on   

先定義表類型：

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) ASDECLARE @now VARCHAR(8) = CONVERT(VARCHAR(8),GETDATE(),112), @tb_name VARCHAR(100)DECLARE @bak_db_name VARCHAR(100) = ‘db_bak_‘ + @nowEXEC(‘CREATE DATABASE ‘ + @bak_db_name)DECLARE c_tbs CURSOR LOCAL FOR SELECT * FROM @tbsOPEN c_tbsFETCH NEXT FROM c_tbs INTO @tb_nameWHILE @@FETCH_STATUS = 0BEGINDECLARE @sql VARCHAR(1000) = ‘SELECT * INTO ‘ + @bak_db_name + ‘.dbo.‘ + REPLACE(@tb_name,‘.‘,‘_‘) + ‘ FROM ‘ + @tb_name  EXEC(@sql)  FETCH NEXT FROM c_tbs INTO @tb_nameENDCLOSE c_tbs  DEALLOCATE c_tbsDECLARE @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_tablesINSERT INTO @tmp_tb VALUES (‘table_a‘),(‘table_b‘)EXEC sp_bak_tables @tmp_tb ‘D:\BACKUP\‘

參考： SQL Server 備份、還原、附加、分離資料庫 批量語句

SQLServer備份指定表