Microsoft SQL server 2005/2008:Backup Database Tables Script

1.Create Stored Procedure to create 'BAK_' named Tables in Database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_backup_table]

@tablename varchar(5000)
as

begin

Declare @sql varchar(5000)

DECLARE db_cursor CURSOR FOR select name from sys.sysobjects where xtype='U' and name not like 'BAK_%'

if @tablename is null

begin

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0 

BEGIN

SET @sql='select * into [BAK_'+@tablename+convert(varchar(19),getdate(),121)+'] from '+@tablename

print @sql

EXEC(@sql)



FETCH NEXT FROM db_cursor INTO @tablename

END

end

else

begin

SET @sql='select *into [BAK_'+@tablename+convert(varchar(19),getdate(),121)+'] from '+@tablename

print @tablename

EXEC(@sql)

end

CLOSE db_cursor

DEALLOCATE db_cursor

end

2.Execute it,Null value means all database table backup else provide table name as input.

3.Drop Backed Tables when they don't require.

4.Create Stored Procedure for dropping backup tables.

CREATE procedure usp_drop_backup_table
@tablename varchar(5000)
as

begin

Declare @sql varchar(5000)

DECLARE db_cursor CURSOR FOR select name from sys.sysobjects where xtype='U' and name like 'BAK_%'

if @tablename is null

begin

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0 

BEGIN

SET @sql='DROP TABLE ['+@tablename+']'

print @sql

EXEC(@sql)

FETCH NEXT FROM db_cursor INTO @tablename

END

end

else

begin

SET @sql='DROP TABLE ['+@tablename+']'

EXEC(@sql)

end

CLOSE db_cursor

DEALLOCATE db_cursor

end