define the table type first:
CREATE TYPE T_bak_tables as TABLE (table_name VARCHAR (200))
then define the backup process:
CREATE PROC sp_bak_tables @tbs t_bak_tables READONLY, @bak_path varchar asdeclare @now varchar (8) = CONVERT (varchar (8 ), GETDATE (), @tb_name varchar DECLARE @bak_db_name varchar (+) = ' Db_bak_ ' + @nowEXEC (' CREATE DATABASE ' + @bak_ db_name) DECLARE C_tbs CURSOR LOCAL for SELECT * from @tbsOPEN c_tbsfetch NEXT to C_tbs into @tb_nameWHILE @ @FETCH_STATUS = 0BEGINDECLARE @sql VARCHAR (+) = ' SELECT * into ' + @bak_db_name + '. dbo. ' + REPLACE (@tb_name, '. ', ' _ ') + ' from ' + @ Tb_name EXEC (@sql) FETCH NEXT from C_tbs to @tb_nameENDCLOSE C_tbs deallocate c_tbsdeclare @bak_file varchar = @bak_path + ' db_bak_ ' + @now + ' _ ' + CAST (DATEDIFF (s,{d ' 1970-01-01 '},getdate ()) as VARCHAR (+)) + '. Bak ' EX EC (' BACKUP database ' + @bak_db_name + ' to DISK = ' + @bak_file + ') EXEC (' DROP database ' + @bak_db_name)
The last Call method is as follows:
DECLARE @tmp_tb Bak_tablesinsert into @tmp_tb VALUES (' table_a '), (' Table_b ') EXEC sp_bak_tables @tmp_tb ' D:\BACKUP\ '
Reference: SQL Server Backup, restore, attach, detach database bulk statements
SQL Server Backup specified table