Posts

Showing posts from August 17, 2012

Microsoft SQL server 2005/2008:Backup Database Tables Script

Image
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 d