bcp 備份所有資料表 (2)

2008-06-30

bcp 備份所有資料表 (2)

bcp 備份所有資料表的另一種寫法，利用 Cursor 來拼湊執行指令然後執行

SQL 指令 :

   1:  -- STEP 1, C:\ 建立目錄 bcp

2:

   3:  -- STEP 2, 變更伺服器設定

   4:  EXEC sp_configure 'show advanced options', 1

   5:  GO

   6:  RECONFIGURE

   7:  GO

8:

   9:  EXEC sp_configure 'xp_cmdshell', 1

  10:  GO

  11:  RECONFIGURE

  12:  GO

13:

  14:  -- 切換資料庫

  15:  USE DBName

  16:  go

17:

  18:  -- 宣告必要變數

  19:  DECLARE @cmd varchar(2000)

  20:  DECLARE @tableName varchar(100)

21:

  22:  -- 宣告 Cursor

  23:  DECLARE tCursor CURSOR FOR

  24:  SELECT name FROM sysobjects WHERE XTYPE = 'U' ORDER BY NAME

25:

  26:  -- 開啟 Curosr

  27:  OPEN tCursor

28:

  29:  -- 抓取資料

  30:  FETCH NEXT FROM tCursor INTO @tableName

31:

  32:  -- 迴圈

  33:  WHILE @@FETCH_STATUS = 0

  34:  BEGIN

  35:      SET @cmd = 'bcp ' + db_name() + '.[' + @tableName +  '] out "c:\bcp\' + @tableName + '.txt" -c -T -t "," -r "\n"'

  36:      EXEC master..xp_cmdshell @cmd

  37:      FETCH NEXT FROM tCursor INTO @tableName

  38:  END

39:

  40:  -- 關閉 Cursor

  41:  CLOSE tCursor

42:

  43:  -- 刪除 Cursor

  44:  DEALLOCATE tCursor