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