Quick SQL script to back up data in some tables.

Disclosure: I used cursor in this script.

/********************************
 ********************************
 ********************************
   Quick data backup for tables in
   a particular database schema
 
   Change the table search criteria
   in the cursor declaration
 ********************************
 ********************************
 ********************************/
USE {some database name}
GO

DECLARE TablesToBackup CURSOR
      FOR
            SELECT IS_T.TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES AS IS_T
            WHERE IS_T.TABLE_SCHEMA = '{schema name}'
                  AND IS_T.TABLE_TYPE = 'BASE TABLE'
                  AND IS_T.TABLE_NAME LIKE 'CI%'
            ORDER BY IS_T.TABLE_NAME

DECLARE @table_names NVARCHAR(255)
      , @ExcSQL NVARCHAR(4000)

OPEN TablesToBackup
FETCH NEXT FROM TablesToBackup
      INTO @table_names

    IF @@FETCH_STATUS <> 0
        PRINT 'No tables found!'    

    WHILE @@FETCH_STATUS = 0
    BEGIN

            SET @ExcSQL = 'SELECT *
INTO dbo.' + @table_names + '_DataBK_' + CONVERT(VARCHAR(50), GETDATE(), 112) + '
FROM dbo.' + @table_names
            --PRINT @ExcSQL
            --PRINT '======================='
            EXEC (@ExcSQL)
           
            FETCH NEXT FROM TablesToBackup
                  INTO @table_names
      END

CLOSE TablesToBackup;
DEALLOCATE TablesToBackup;



Print | posted on Wednesday, July 18, 2012 8:54 AM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski