> 数据库 > SQL Server 2005 >

SQL Server数据库的表级备份

SQL Server management studio不能实现单个表的备份。Litespeed也不行。有一种例外,如果你的表在一个单独的filegroup里,那么就可以单独备份,因为SQL Server支持对单个filegroup的备份。但是显然在大多数情况下,我们的表不满足这个要求。但我们仍然有多种方式备份表。这里说的备份并不是生成一个bak备份文件,仅仅是复制表数据。

1,BCP (BULK COPY PROGRAM)

2,Generate Table Script with data

3,Make a copy of table using SELECT INTO

4,SAVE Table Data Directly in a Flat file

5,Export Data using SSIS to any destination

 

下面分别说明:

BCP (BULK COPY PROGRAM) 

BCP是常用的数据导入导出工具,因此这种方式是将数据导出,需要时再导入会原数据库。

  

  1. -- SQL Table Backup  
  2. -- Developed by DBATAG, www.DBATAG.com  
  3. DECLARE @table VARCHAR(128),  
  4. @file VARCHAR(255),  
  5. @cmd VARCHAR(512)  
  6. SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want to backup  
  7. SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup  
  8. + '.dat'  
  9. SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '  
  10. EXEC master..xp_cmdshell @cmd  
-- SQL Table Backup
-- Developed by DBATAG, www.DBATAG.com
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want to backup
SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

 

恢复时执行下面的语句:

  1. BULK INSERT AdventureWorks.Person.Contacts_Restore   
  2.     FROM 'C:\MSSQL\Backup\Contact.Dat'   
  3.     WITH (DATAFILETYPE='native');  
BULK INSERT AdventureWorks.Person.Contacts_Restore 
    FROM 'C:\MSSQL\Backup\Contact.Dat' 
    WITH (DATAFILETYPE='native');

 

Generate Table Script with data

如果你的表并不大,那么这是一个很简单易行的方法,在management studio中可以直接用菜单操作。

右键点击数据库名->Tasks->Generate Scripts 就打开了一个向导。按照向导操作,注意选择“Types of data to script” = "schema and data"。之后就会得到一个脚本文件,内容是由表中的数据构建成的insert 语句:

insert into youTable (...) values (...)

当需要恢复表数据时,打开该文件,将内容复制到SSMS中,执行一下就可以了。或者在SSMS直接选择打开文件,然后执行。

上面两种方法都需要打开脚本文件,如果脚本文件很大,则打开很慢甚至失败。此时用下面语句在SSMS中直接执行脚本,避免打开文件:

 

 
  1. EXEC master.dbo.xp_cmdshell ‘isql -Hhostname -ddbname -Uusername -Ppassword -i file’  
  2.   
  3. example  
  4. EXEC master.dbo.xp_cmdshell ‘isql -H10.203.420.155 -dmdd3 -Umed -Piddmapis -i c:\upgrade110490.sql’  
EXEC master.dbo.xp_cmdshell ‘isql -Hhostname -ddbname -Uusername -Ppassword -i file’

example
EXEC master.dbo.xp_cmdshell ‘isql -H10.203.420.155 -dmdd3 -Umed -Piddmapis -i c:\upgrade110490.sql’

Make a copy of table using SELECT INTO

 

如果你的数据库不是production,而且不需要永久保存备份数据,那么用select into语句暂时将表数据插入一个新表里,无疑是最快最简单的办法了。等确实需要恢复,或不需要恢复后,将备份表删除即可。

 

SAVE Table Data Directly in a Flat file

在SSMS菜单中点击query->Results to->file,将数据直接保存成flat文件。但是这种方法恢复起来较麻烦。

Export Data using SSIS to any destination

SSIS可以轻松将数据复制到多个地点,还可以实现复杂的业务逻辑。但是由于使用SSIS的同学不多,相对也复杂一些,这里只是给大家一个选项,不累述。

 

(责任编辑:IT)