bcp 備份所有資料表
利用 bcp 指令，將資料庫中所有的資料表匯出至 C:\bcp 目錄上。
1: -- STEP 1, Create Directory bcp in C:\
2: 3: -- STEP 2, Modify Server Configuration
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: -- STEP 3, Backup All Tables in Some Database
15: USE DBName
16: GO
17: 18: SET NOCOUNT ON
19: 20: CREATE TABLE #a (name varchar(128), id int identity)
21: 22: INSERT #a (name) SELECT name FROM sysobjects WHERE XTYPE = 'U'
23: 24: DECLARE @id int, @cmd varchar( 2000 )
25: 26: SELECT @id = 0
27: WHILE @id < ( SELECT max(id) FROM #a)
28: BEGIN
29: SELECT @id = min(id) FROM #a where id > @id
30: SELECT @cmd = 'bcp ' + db_name( ) + '.' + '[' + name + '] '+' out "c:\bcp\' + name + '.txt" -c -S' + @@servername + ' -Uusername -Ppassword'
31: FROM #a where id = @id
32: EXEC master..xp_cmdshell @cmd
33: END
34: 35: DROP TABLE #a
36: GO