在SQL SERVER 中如何备份和恢复单个表?

2019独角兽企业重金招聘Python工程师标准>>>

如果只想备份或恢复单个表而不想备份或恢复整个数据库的话,往往有以下方法:

使用命令bcp:
导出到filename文件中:
Master..xp_CmdShell 'bcp 库名..表名 out D:\filename-c -Sservername -Usa -Ppassword'
---导入:
Master..xp_cmdShell 'bcp 库名..表名 in D:\filename -c -Sservername -Usa -Ppassword'

declare @table_name varchar(100)
set @table_name='Site_us'
declare @the_db varchar(50) 
 declare @the_sql varchar(500) 
 select @the_db=name from master..SysDataBases where DbId=(Select Dbid From master..SysProcesses where Spid = @@spid)   
 set @the_sql = 'bcp "select ID,Accounts,MD5PassWord,PassWord,InitCoin,AddDate,SiteID,TypeID from ' + @the_db + '..'+@table_name+' with(nolock) where ID>=9582636 and ID<=9882635" queryout D:\'+@table_name+'.dat' + ' -c -T -E'  
 exec master..xp_cmdshell @the_sql

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

转载于:https://my.oschina.net/sansom/blog/178285