How do I backup a group of tables all at once?

×

Sometimes you need a quick backup of multiple tables. You could do them one at a time, but sometimes it is easier to just do a batch all at once. Use the following query:

DECLARE @name NVARCHAR(100)
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE TABLE_TYPE='BASE TABLE'
--Let's not back up previous back ups
and ISNUMERIC(right(table_name,8))=0
--Let's backup all BirdDog tables
and table_name like 'bd%'

OPEN @getid
FETCH NEXT
FROM @getid INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
declare @ls_sql varchar(max);
set @ls_sql = 'select * into ' + @name + cast(dbo.DateToISODate(getdate()) as char(8)) + ' from ' + @name
execute (@ls_sql);

FETCH NEXT
FROM @getid INTO @name
END

CLOSE @getid
DEALLOCATE @getid