我试了一下用2008自带的导出数据的工具，一秒钟大概能倒9999行，这样的话就大概要十几个小时，这样就会影响第二天的使用。
我本来想直接把数据库分离出来，然后拷贝一份，但是我发现我没有sa权限，所以我没法子了。
我想问一下有没有更快的备份的方法？
14 个解决方案
#1
SQL备份
1、SQL数据库恢复模型
1）完全恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。
（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。
（2）大容量日志记录恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。
（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。
-------------------
（3）简单恢复模型
-------------------
（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。
（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。
（3）不适于生产系统和大规模*作环境下选用。
-----------------------------------------
alter database d1 set recovery simple --设置数据库恢复模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
2、备份设备
----------------------------------------------------------------------
1）物理设备
---------------------------
disk：支持本地磁盘或者网络备份
tape：支持磁带机备份
name pipe：支持第三方备份软件
---------------------------
2）逻辑设备
---------------------------
永久备份文件：可以重复使用，应该在备份前创建。
临时备份文件：用于一次性备份，在备份时创建。
-------------------------------------------------
exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak' --创建永久磁盘备份设备
exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak'
----------------------------------------------------------------------
exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak' --创建网络永久磁盘备份设备
exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak'
----------------------------------------------------------------------
exec sp_dropdevice 'bak5' --删除备份设备
----------------------------------------------------------------------
backup database d3 to bak3 --将数据库备份到备份设备
backup database d4 to bak4
----------------------------------------------------------------------
restore headeronly from bak2 --查看备份设备中的内容
----------------------------------------------------------------------
backup database d3 to disk='e:\back_file\d3.bak' --将数据库备份到临时备份文件
backup database d4 to disk='e:\back_file\d4.bak'
----------------------------------------------------------------------
restore database d3 from bak3 --从备份设备还原数据库
restore database d4 from disk='e:\back_file\d4.bak' --从备份文件还原数据库
----------------------------------------------------------------------
3、使用多个备份文件存储备份
----------------------------------------------------------------------
1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。
2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。
3）媒体集中的文件必须同时使用，而不能单独使用。
4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。
--------------------------------------------------------------------
backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份D4并形成Media Set
backup database d3 to bak4 --失败，因Media set中文件必须同时使用
backup database d3 to bak4,bak5,bak6 --成功，将D3也备份到Media Set中
restore headeronly from bak4,bak5,bak6--查看Media Set中的备份内容
----------------------------------------------------------------------
backup database d4 to bak4 with medianame='bak4',format --重新划分Media Set
backup database d3 to bak5,bak6 with medianame='bak56',format
----------------------------------------------------------------------
backup database d1 to bak1 with init --with init重写备份设备中内容
backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中
restore headeronly from bak1
----------------------------------------------------------------------
1）完全备份
-------------------------------------------
（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。
（2）完全备份会备份数据库的所有数据文件、数据对象和数据。
（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。
--------------------------------------------
backup database d1 to bak1 with init --完全备份
backup database d1 to bak1 with noinit
----------------------------------------------------------------------
2）差异备份
---------------------------------------------
（1）基于完全备份。
（2）备份自最近一次完全备份以来的所有数据库改变。
（3）恢复时，只应用最近一次完全备份和最新的差异备份。
-----------------------------------------------
backup database d2 to bak2 with init,name='d2_full' --差异备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup database d2 to bak2 with differential,name='d2_diff1'
insert b1 values(1,'a')
backup database d2 to bak2 with differential,name='d2_diff2'
insert b1 values(2,'b')
backup database d2 to bak2 with differential,name='d2_diff3'
insert b1 values(3,'c')
backup database d2 to bak2 with differential,name='d2_diff4'
restore headeronly from bak2
----------------------------------------------------------------------
3）事务日志备份
-------------------------------------------------------------
（1）基于完全备份。
（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。
（3）允许恢复到故障时刻或者一个强制时间点。
（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。
-------------------------------------------------------------
backup database d3 to bak3 with init,name='d3_full' --日志备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup log d3 to bak3 with
insert b1 values(1,'a')
backup log d3 to bak3 with
insert b1 values(2,'b')
backup log d3 to bak3 with
insert b1 values(3,'c')
backup log d3 to bak3 with
restore headeronly from bak3
----------------------------------------------------------------------
create table b1(c1 int not null,c2 char(10) not null) --Full+Log+Diff
backup log d4 to bak4 with
insert b1 values(1,'a')
backup log d4 to bak4 with
insert b1 values(2,'b')
backup database d4 to bak4 with differential,name='d4_diff1'
insert b1 values(3,'c')
backup log d4 to bak4 with
insert b1 values(4,'d')
backup log d4 to bak4 with
insert b1 values(5,'d')
backup database d4 to bak4 with differential,name='d4_diff2'
restore headeronly from bak4
1、SQL数据库恢复模型
1）完全恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。
（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。
（2）大容量日志记录恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。
（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。
-------------------
（3）简单恢复模型
-------------------
（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。
（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。
（3）不适于生产系统和大规模*作环境下选用。
-----------------------------------------
alter database d1 set recovery simple --设置数据库恢复模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
2、备份设备
----------------------------------------------------------------------
1）物理设备
---------------------------
disk：支持本地磁盘或者网络备份
tape：支持磁带机备份
name pipe：支持第三方备份软件
---------------------------
2）逻辑设备
---------------------------
永久备份文件：可以重复使用，应该在备份前创建。
临时备份文件：用于一次性备份，在备份时创建。
-------------------------------------------------
exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak' --创建永久磁盘备份设备
exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak'
----------------------------------------------------------------------
exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak' --创建网络永久磁盘备份设备
exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak'
----------------------------------------------------------------------
exec sp_dropdevice 'bak5' --删除备份设备
----------------------------------------------------------------------
backup database d3 to bak3 --将数据库备份到备份设备
backup database d4 to bak4
----------------------------------------------------------------------
restore headeronly from bak2 --查看备份设备中的内容
----------------------------------------------------------------------
backup database d3 to disk='e:\back_file\d3.bak' --将数据库备份到临时备份文件
backup database d4 to disk='e:\back_file\d4.bak'
----------------------------------------------------------------------
restore database d3 from bak3 --从备份设备还原数据库
restore database d4 from disk='e:\back_file\d4.bak' --从备份文件还原数据库
----------------------------------------------------------------------
3、使用多个备份文件存储备份
----------------------------------------------------------------------
1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。
2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。
3）媒体集中的文件必须同时使用，而不能单独使用。
4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。
--------------------------------------------------------------------
backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份D4并形成Media Set
backup database d3 to bak4 --失败，因Media set中文件必须同时使用
backup database d3 to bak4,bak5,bak6 --成功，将D3也备份到Media Set中
restore headeronly from bak4,bak5,bak6--查看Media Set中的备份内容
----------------------------------------------------------------------
backup database d4 to bak4 with medianame='bak4',format --重新划分Media Set
backup database d3 to bak5,bak6 with medianame='bak56',format
----------------------------------------------------------------------
backup database d1 to bak1 with init --with init重写备份设备中内容
backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中
restore headeronly from bak1
----------------------------------------------------------------------
1）完全备份
-------------------------------------------
（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。
（2）完全备份会备份数据库的所有数据文件、数据对象和数据。
（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。
--------------------------------------------
backup database d1 to bak1 with init --完全备份
backup database d1 to bak1 with noinit
----------------------------------------------------------------------
2）差异备份
---------------------------------------------
（1）基于完全备份。
（2）备份自最近一次完全备份以来的所有数据库改变。
（3）恢复时，只应用最近一次完全备份和最新的差异备份。
-----------------------------------------------
backup database d2 to bak2 with init,name='d2_full' --差异备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup database d2 to bak2 with differential,name='d2_diff1'
insert b1 values(1,'a')
backup database d2 to bak2 with differential,name='d2_diff2'
insert b1 values(2,'b')
backup database d2 to bak2 with differential,name='d2_diff3'
insert b1 values(3,'c')
backup database d2 to bak2 with differential,name='d2_diff4'
restore headeronly from bak2
----------------------------------------------------------------------
3）事务日志备份
-------------------------------------------------------------
（1）基于完全备份。
（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。
（3）允许恢复到故障时刻或者一个强制时间点。
（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。
-------------------------------------------------------------
backup database d3 to bak3 with init,name='d3_full' --日志备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup log d3 to bak3 with
insert b1 values(1,'a')
backup log d3 to bak3 with
insert b1 values(2,'b')
backup log d3 to bak3 with
insert b1 values(3,'c')
backup log d3 to bak3 with
restore headeronly from bak3
----------------------------------------------------------------------
create table b1(c1 int not null,c2 char(10) not null) --Full+Log+Diff
backup log d4 to bak4 with
insert b1 values(1,'a')
backup log d4 to bak4 with
insert b1 values(2,'b')
backup database d4 to bak4 with differential,name='d4_diff1'
insert b1 values(3,'c')
backup log d4 to bak4 with
insert b1 values(4,'d')
backup log d4 to bak4 with
insert b1 values(5,'d')
backup database d4 to bak4 with differential,name='d4_diff2'
restore headeronly from bak4
#2
日志清除
-----------------------------------------
1）如果日志空间被填满，数据库将不能记录修改。
2）数据库在做完全备份时日志被截断。
3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。
4）with truncate_only和with no_log设置日志满时清除日志
5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。
-------------------------------------------
exec sp_dboption d3
exec sp_dboption
sp_dboption 'd3','trunc. log on chkpt.','true' --设置自动清除数据库日志
sp_dboption 'd3','trunc. log on chkpt.','false' --将自动清除数据库日志的选项去除
----------------------------------------------------------------------
backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录
----------------------------------------------------------------------
backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录
----------------------------------------------------------------------
backup log d4 to bak4 with no_truncate--在D4数据库损坏时马上备份当前数据库日志(DEMO)
--------
使用no_truncate
完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL
backup log no_truncate
再还原，可还原到修改3
----------------------------------------------------------------------
4）文件/文件组备份
------------------------------------------------------------------
（1）用于超大型数据库。
（2）只备份选定的文件或者文件组。
（3）必须同时作日志备份。
（4）还原时用文件/文件组备份和日志备份进行还原。
（5）备份量少，恢复速度快。
create database d5
on primary
(name=d5_data1,
filename='e:\data\d5\d5_data1.mdf',
size=2MB),
filegroup FG2 --创建数据库时创建filegroup FG2
(name=d5_data2,
filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:\data\d5\d5_log1.ldf',
size=2Mb)
use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:\data\d5\d5_data5.ndf',
size=2MB)
to filegroup FG2 --将d5_data3加到文件组FG2中
alter database d5 add filegroup FG3 --增加文件组FG3
alter database d5 --将d5_data4加到文件组FG2中
add file
(name=d5_data4,
filename='e:\data\d5\d5_data4.ndf',
size=2MB)
to filegroup FG3
sp_helpdb d5
create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中
create table t2(c1 int not null,c2 char(10) not null) on FG2
create table t3(c1 int not null,c2 char(10) not null) on FG3
----------------------------------------------------------------------
backup database d5 to bak5 with init,name='d5_full' --filegroup备份
backup database d5 filegroup='primary' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG2' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG3' to bak5 with
backup log d5 to bak5 with
----------------------------------------------------------------------
backup database d5 to bak6 with init,name='d5_full' --file备份
backup database d5 file='d5_data1' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data2' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data3' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data4' to bak6 with
backup log d5 to bak6 with
restore headeronly from bak6
-----------------------------------------
1）如果日志空间被填满，数据库将不能记录修改。
2）数据库在做完全备份时日志被截断。
3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。
4）with truncate_only和with no_log设置日志满时清除日志
5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。
-------------------------------------------
exec sp_dboption d3
exec sp_dboption
sp_dboption 'd3','trunc. log on chkpt.','true' --设置自动清除数据库日志
sp_dboption 'd3','trunc. log on chkpt.','false' --将自动清除数据库日志的选项去除
----------------------------------------------------------------------
backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录
----------------------------------------------------------------------
backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录
----------------------------------------------------------------------
backup log d4 to bak4 with no_truncate--在D4数据库损坏时马上备份当前数据库日志(DEMO)
--------
使用no_truncate
完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL
backup log no_truncate
再还原，可还原到修改3
----------------------------------------------------------------------
4）文件/文件组备份
------------------------------------------------------------------
（1）用于超大型数据库。
（2）只备份选定的文件或者文件组。
（3）必须同时作日志备份。
（4）还原时用文件/文件组备份和日志备份进行还原。
（5）备份量少，恢复速度快。
create database d5
on primary
(name=d5_data1,
filename='e:\data\d5\d5_data1.mdf',
size=2MB),
filegroup FG2 --创建数据库时创建filegroup FG2
(name=d5_data2,
filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:\data\d5\d5_log1.ldf',
size=2Mb)
use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:\data\d5\d5_data5.ndf',
size=2MB)
to filegroup FG2 --将d5_data3加到文件组FG2中
alter database d5 add filegroup FG3 --增加文件组FG3
alter database d5 --将d5_data4加到文件组FG2中
add file
(name=d5_data4,
filename='e:\data\d5\d5_data4.ndf',
size=2MB)
to filegroup FG3
sp_helpdb d5
create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中
create table t2(c1 int not null,c2 char(10) not null) on FG2
create table t3(c1 int not null,c2 char(10) not null) on FG3
----------------------------------------------------------------------
backup database d5 to bak5 with init,name='d5_full' --filegroup备份
backup database d5 filegroup='primary' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG2' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG3' to bak5 with
backup log d5 to bak5 with
----------------------------------------------------------------------
backup database d5 to bak6 with init,name='d5_full' --file备份
backup database d5 file='d5_data1' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data2' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data3' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data4' to bak6 with
backup log d5 to bak6 with
restore headeronly from bak6
#3
楼主们数据库管理完全混乱，7亿的一个SQL Server数据表可以想象有多慢，而且楼主们估计也从来没有查看过indexes 有没有脏或者rebuild index， 7亿的表创建火车rebuild 一个index 估计要很长时间。
像你这种情况，如果最后有用的数据少，我会申请数据库维护，比如3个小时，然后直接re-name 这张大表，
然后创建一张一模一样的新表，把有用的数据反过来导入到新表里面，旧表就可以等有空慢慢来处理。
像你这种情况，如果最后有用的数据少，我会申请数据库维护，比如3个小时，然后直接re-name 这张大表，
然后创建一张一模一样的新表，把有用的数据反过来导入到新表里面，旧表就可以等有空慢慢来处理。
#4
可以试试分区表吧，安范围分区，再创建一个存放过期数据的表，通过分区表的滑动分区方案，用switch partition 能实现数据表元数据的移动从而对表实现逻辑上的分离，而不用再磁盘上移动数据。就算是7亿条数据的转移也应该几十秒就能完成吧。我只试过500万的数据。至于在另一个表中的旧数据，要删除也可以啊。
#5
分离，找到数据库文件，然后复制一份。用数据库自带的备份会慢死的。
#6
大家给出的方法都有可以借鉴的。
#7
没有sa权限的话就比较麻烦。如果这个表有个主键id的话，找到今年最早一个id，然后用以下的语句导出数据就比较快
select * from 原表 where id>最早id
select * from 原表 where id>最早id
#8
用数据仓库吧，让数据库自动把历史数据转存
#9
FILEGROUP将历史数据保存到FILEGROUP然后备份单独的FILEGROUP，可以同时选择备份到多个备份文件，这样既可以使用多线程同时可以提升IO的速度。
#10
历史的FILEGROUP还可以设置为只读。
#11
我觉得直接bcpout表里的内容到一个文本文件后把这个文本文件当成备份如何？
不用死磕backup操作吧···
不用死磕backup操作吧···
#12
改名重建表应该比较快吧。
如果没SA权限，有不少办法都可以，直接登录服务器，或是用专门的代码来提升权限。
如果没SA权限，有不少办法都可以，直接登录服务器，或是用专门的代码来提升权限。
#13
连接两个数据库，清空TST环境，然后用insert into dbo1.test1 select * from dbo2.test1
就算有7亿，最多也是10多分钟就可以搞掂
就算有7亿，最多也是10多分钟就可以搞掂
#14
个人觉得最合适的方法肯定是分区表
另外如果老数据不怎么访问的话，也可以直接老数据单独拿出来分表或者分库，这样那个分出来的库如果内容不变，那么只需要备份一次即可。新数据在新表，通过VIEW可以变成1个表
另外也可以试试差异备份，应该可以应付备份慢的问题
另外如果老数据不怎么访问的话，也可以直接老数据单独拿出来分表或者分库，这样那个分出来的库如果内容不变，那么只需要备份一次即可。新数据在新表，通过VIEW可以变成1个表
另外也可以试试差异备份，应该可以应付备份慢的问题
#1
SQL备份
1、SQL数据库恢复模型
1）完全恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。
（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。
（2）大容量日志记录恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。
（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。
-------------------
（3）简单恢复模型
-------------------
（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。
（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。
（3）不适于生产系统和大规模*作环境下选用。
-----------------------------------------
alter database d1 set recovery simple --设置数据库恢复模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
2、备份设备
----------------------------------------------------------------------
1）物理设备
---------------------------
disk：支持本地磁盘或者网络备份
tape：支持磁带机备份
name pipe：支持第三方备份软件
---------------------------
2）逻辑设备
---------------------------
永久备份文件：可以重复使用，应该在备份前创建。
临时备份文件：用于一次性备份，在备份时创建。
-------------------------------------------------
exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak' --创建永久磁盘备份设备
exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak'
----------------------------------------------------------------------
exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak' --创建网络永久磁盘备份设备
exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak'
----------------------------------------------------------------------
exec sp_dropdevice 'bak5' --删除备份设备
----------------------------------------------------------------------
backup database d3 to bak3 --将数据库备份到备份设备
backup database d4 to bak4
----------------------------------------------------------------------
restore headeronly from bak2 --查看备份设备中的内容
----------------------------------------------------------------------
backup database d3 to disk='e:\back_file\d3.bak' --将数据库备份到临时备份文件
backup database d4 to disk='e:\back_file\d4.bak'
----------------------------------------------------------------------
restore database d3 from bak3 --从备份设备还原数据库
restore database d4 from disk='e:\back_file\d4.bak' --从备份文件还原数据库
----------------------------------------------------------------------
3、使用多个备份文件存储备份
----------------------------------------------------------------------
1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。
2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。
3）媒体集中的文件必须同时使用，而不能单独使用。
4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。
--------------------------------------------------------------------
backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份D4并形成Media Set
backup database d3 to bak4 --失败，因Media set中文件必须同时使用
backup database d3 to bak4,bak5,bak6 --成功，将D3也备份到Media Set中
restore headeronly from bak4,bak5,bak6--查看Media Set中的备份内容
----------------------------------------------------------------------
backup database d4 to bak4 with medianame='bak4',format --重新划分Media Set
backup database d3 to bak5,bak6 with medianame='bak56',format
----------------------------------------------------------------------
backup database d1 to bak1 with init --with init重写备份设备中内容
backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中
restore headeronly from bak1
----------------------------------------------------------------------
1）完全备份
-------------------------------------------
（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。
（2）完全备份会备份数据库的所有数据文件、数据对象和数据。
（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。
--------------------------------------------
backup database d1 to bak1 with init --完全备份
backup database d1 to bak1 with noinit
----------------------------------------------------------------------
2）差异备份
---------------------------------------------
（1）基于完全备份。
（2）备份自最近一次完全备份以来的所有数据库改变。
（3）恢复时，只应用最近一次完全备份和最新的差异备份。
-----------------------------------------------
backup database d2 to bak2 with init,name='d2_full' --差异备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup database d2 to bak2 with differential,name='d2_diff1'
insert b1 values(1,'a')
backup database d2 to bak2 with differential,name='d2_diff2'
insert b1 values(2,'b')
backup database d2 to bak2 with differential,name='d2_diff3'
insert b1 values(3,'c')
backup database d2 to bak2 with differential,name='d2_diff4'
restore headeronly from bak2
----------------------------------------------------------------------
3）事务日志备份
-------------------------------------------------------------
（1）基于完全备份。
（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。
（3）允许恢复到故障时刻或者一个强制时间点。
（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。
-------------------------------------------------------------
backup database d3 to bak3 with init,name='d3_full' --日志备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup log d3 to bak3 with
insert b1 values(1,'a')
backup log d3 to bak3 with
insert b1 values(2,'b')
backup log d3 to bak3 with
insert b1 values(3,'c')
backup log d3 to bak3 with
restore headeronly from bak3
----------------------------------------------------------------------
create table b1(c1 int not null,c2 char(10) not null) --Full+Log+Diff
backup log d4 to bak4 with
insert b1 values(1,'a')
backup log d4 to bak4 with
insert b1 values(2,'b')
backup database d4 to bak4 with differential,name='d4_diff1'
insert b1 values(3,'c')
backup log d4 to bak4 with
insert b1 values(4,'d')
backup log d4 to bak4 with
insert b1 values(5,'d')
backup database d4 to bak4 with differential,name='d4_diff2'
restore headeronly from bak4
1、SQL数据库恢复模型
1）完全恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。
（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。
（2）大容量日志记录恢复模型
（1）备份时要备份数据库的数据文件和日志文件
（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。
（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。
（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。
-------------------
（3）简单恢复模型
-------------------
（1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。
（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。
（3）不适于生产系统和大规模*作环境下选用。
-----------------------------------------
alter database d1 set recovery simple --设置数据库恢复模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
2、备份设备
----------------------------------------------------------------------
1）物理设备
---------------------------
disk：支持本地磁盘或者网络备份
tape：支持磁带机备份
name pipe：支持第三方备份软件
---------------------------
2）逻辑设备
---------------------------
永久备份文件：可以重复使用，应该在备份前创建。
临时备份文件：用于一次性备份，在备份时创建。
-------------------------------------------------
exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak' --创建永久磁盘备份设备
exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak'
----------------------------------------------------------------------
exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak' --创建网络永久磁盘备份设备
exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak'
----------------------------------------------------------------------
exec sp_dropdevice 'bak5' --删除备份设备
----------------------------------------------------------------------
backup database d3 to bak3 --将数据库备份到备份设备
backup database d4 to bak4
----------------------------------------------------------------------
restore headeronly from bak2 --查看备份设备中的内容
----------------------------------------------------------------------
backup database d3 to disk='e:\back_file\d3.bak' --将数据库备份到临时备份文件
backup database d4 to disk='e:\back_file\d4.bak'
----------------------------------------------------------------------
restore database d3 from bak3 --从备份设备还原数据库
restore database d4 from disk='e:\back_file\d4.bak' --从备份文件还原数据库
----------------------------------------------------------------------
3、使用多个备份文件存储备份
----------------------------------------------------------------------
1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。
2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。
3）媒体集中的文件必须同时使用，而不能单独使用。
4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。
--------------------------------------------------------------------
backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份D4并形成Media Set
backup database d3 to bak4 --失败，因Media set中文件必须同时使用
backup database d3 to bak4,bak5,bak6 --成功，将D3也备份到Media Set中
restore headeronly from bak4,bak5,bak6--查看Media Set中的备份内容
----------------------------------------------------------------------
backup database d4 to bak4 with medianame='bak4',format --重新划分Media Set
backup database d3 to bak5,bak6 with medianame='bak56',format
----------------------------------------------------------------------
backup database d1 to bak1 with init --with init重写备份设备中内容
backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中
restore headeronly from bak1
----------------------------------------------------------------------
1）完全备份
-------------------------------------------
（1）是备份的基准。在做备份时第一次备份都建议使用完全备份。
（2）完全备份会备份数据库的所有数据文件、数据对象和数据。
（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。
--------------------------------------------
backup database d1 to bak1 with init --完全备份
backup database d1 to bak1 with noinit
----------------------------------------------------------------------
2）差异备份
---------------------------------------------
（1）基于完全备份。
（2）备份自最近一次完全备份以来的所有数据库改变。
（3）恢复时，只应用最近一次完全备份和最新的差异备份。
-----------------------------------------------
backup database d2 to bak2 with init,name='d2_full' --差异备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup database d2 to bak2 with differential,name='d2_diff1'
insert b1 values(1,'a')
backup database d2 to bak2 with differential,name='d2_diff2'
insert b1 values(2,'b')
backup database d2 to bak2 with differential,name='d2_diff3'
insert b1 values(3,'c')
backup database d2 to bak2 with differential,name='d2_diff4'
restore headeronly from bak2
----------------------------------------------------------------------
3）事务日志备份
-------------------------------------------------------------
（1）基于完全备份。
（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。
（3）允许恢复到故障时刻或者一个强制时间点。
（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。
-------------------------------------------------------------
backup database d3 to bak3 with init,name='d3_full' --日志备份，第一次备份时应做完全备份
create table b1(c1 int not null,c2 char(10) not null)
backup log d3 to bak3 with
insert b1 values(1,'a')
backup log d3 to bak3 with
insert b1 values(2,'b')
backup log d3 to bak3 with
insert b1 values(3,'c')
backup log d3 to bak3 with
restore headeronly from bak3
----------------------------------------------------------------------
create table b1(c1 int not null,c2 char(10) not null) --Full+Log+Diff
backup log d4 to bak4 with
insert b1 values(1,'a')
backup log d4 to bak4 with
insert b1 values(2,'b')
backup database d4 to bak4 with differential,name='d4_diff1'
insert b1 values(3,'c')
backup log d4 to bak4 with
insert b1 values(4,'d')
backup log d4 to bak4 with
insert b1 values(5,'d')
backup database d4 to bak4 with differential,name='d4_diff2'
restore headeronly from bak4
#2
日志清除
-----------------------------------------
1）如果日志空间被填满，数据库将不能记录修改。
2）数据库在做完全备份时日志被截断。
3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。
4）with truncate_only和with no_log设置日志满时清除日志
5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。
-------------------------------------------
exec sp_dboption d3
exec sp_dboption
sp_dboption 'd3','trunc. log on chkpt.','true' --设置自动清除数据库日志
sp_dboption 'd3','trunc. log on chkpt.','false' --将自动清除数据库日志的选项去除
----------------------------------------------------------------------
backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录
----------------------------------------------------------------------
backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录
----------------------------------------------------------------------
backup log d4 to bak4 with no_truncate--在D4数据库损坏时马上备份当前数据库日志(DEMO)
--------
使用no_truncate
完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL
backup log no_truncate
再还原，可还原到修改3
----------------------------------------------------------------------
4）文件/文件组备份
------------------------------------------------------------------
（1）用于超大型数据库。
（2）只备份选定的文件或者文件组。
（3）必须同时作日志备份。
（4）还原时用文件/文件组备份和日志备份进行还原。
（5）备份量少，恢复速度快。
create database d5
on primary
(name=d5_data1,
filename='e:\data\d5\d5_data1.mdf',
size=2MB),
filegroup FG2 --创建数据库时创建filegroup FG2
(name=d5_data2,
filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:\data\d5\d5_log1.ldf',
size=2Mb)
use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:\data\d5\d5_data5.ndf',
size=2MB)
to filegroup FG2 --将d5_data3加到文件组FG2中
alter database d5 add filegroup FG3 --增加文件组FG3
alter database d5 --将d5_data4加到文件组FG2中
add file
(name=d5_data4,
filename='e:\data\d5\d5_data4.ndf',
size=2MB)
to filegroup FG3
sp_helpdb d5
create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中
create table t2(c1 int not null,c2 char(10) not null) on FG2
create table t3(c1 int not null,c2 char(10) not null) on FG3
----------------------------------------------------------------------
backup database d5 to bak5 with init,name='d5_full' --filegroup备份
backup database d5 filegroup='primary' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG2' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG3' to bak5 with
backup log d5 to bak5 with
----------------------------------------------------------------------
backup database d5 to bak6 with init,name='d5_full' --file备份
backup database d5 file='d5_data1' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data2' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data3' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data4' to bak6 with
backup log d5 to bak6 with
restore headeronly from bak6
-----------------------------------------
1）如果日志空间被填满，数据库将不能记录修改。
2）数据库在做完全备份时日志被截断。
3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。
4）with truncate_only和with no_log设置日志满时清除日志
5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。
-------------------------------------------
exec sp_dboption d3
exec sp_dboption
sp_dboption 'd3','trunc. log on chkpt.','true' --设置自动清除数据库日志
sp_dboption 'd3','trunc. log on chkpt.','false' --将自动清除数据库日志的选项去除
----------------------------------------------------------------------
backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录
----------------------------------------------------------------------
backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录
----------------------------------------------------------------------
backup log d4 to bak4 with no_truncate--在D4数据库损坏时马上备份当前数据库日志(DEMO)
--------
使用no_truncate
完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL
backup log no_truncate
再还原，可还原到修改3
----------------------------------------------------------------------
4）文件/文件组备份
------------------------------------------------------------------
（1）用于超大型数据库。
（2）只备份选定的文件或者文件组。
（3）必须同时作日志备份。
（4）还原时用文件/文件组备份和日志备份进行还原。
（5）备份量少，恢复速度快。
create database d5
on primary
(name=d5_data1,
filename='e:\data\d5\d5_data1.mdf',
size=2MB),
filegroup FG2 --创建数据库时创建filegroup FG2
(name=d5_data2,
filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:\data\d5\d5_log1.ldf',
size=2Mb)
use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:\data\d5\d5_data5.ndf',
size=2MB)
to filegroup FG2 --将d5_data3加到文件组FG2中
alter database d5 add filegroup FG3 --增加文件组FG3
alter database d5 --将d5_data4加到文件组FG2中
add file
(name=d5_data4,
filename='e:\data\d5\d5_data4.ndf',
size=2MB)
to filegroup FG3
sp_helpdb d5
create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中
create table t2(c1 int not null,c2 char(10) not null) on FG2
create table t3(c1 int not null,c2 char(10) not null) on FG3
----------------------------------------------------------------------
backup database d5 to bak5 with init,name='d5_full' --filegroup备份
backup database d5 filegroup='primary' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG2' to bak5 with
backup log d5 to bak5 with
backup database d5 filegroup='FG3' to bak5 with
backup log d5 to bak5 with
----------------------------------------------------------------------
backup database d5 to bak6 with init,name='d5_full' --file备份
backup database d5 file='d5_data1' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data2' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data3' to bak6 with
backup log d5 to bak6 with
backup database d5 file='d5_data4' to bak6 with
backup log d5 to bak6 with
restore headeronly from bak6
#3
楼主们数据库管理完全混乱，7亿的一个SQL Server数据表可以想象有多慢，而且楼主们估计也从来没有查看过indexes 有没有脏或者rebuild index， 7亿的表创建火车rebuild 一个index 估计要很长时间。
像你这种情况，如果最后有用的数据少，我会申请数据库维护，比如3个小时，然后直接re-name 这张大表，
然后创建一张一模一样的新表，把有用的数据反过来导入到新表里面，旧表就可以等有空慢慢来处理。
像你这种情况，如果最后有用的数据少，我会申请数据库维护，比如3个小时，然后直接re-name 这张大表，
然后创建一张一模一样的新表，把有用的数据反过来导入到新表里面，旧表就可以等有空慢慢来处理。
#4
可以试试分区表吧，安范围分区，再创建一个存放过期数据的表，通过分区表的滑动分区方案，用switch partition 能实现数据表元数据的移动从而对表实现逻辑上的分离，而不用再磁盘上移动数据。就算是7亿条数据的转移也应该几十秒就能完成吧。我只试过500万的数据。至于在另一个表中的旧数据，要删除也可以啊。
#5
分离，找到数据库文件，然后复制一份。用数据库自带的备份会慢死的。
#6
大家给出的方法都有可以借鉴的。
#7
没有sa权限的话就比较麻烦。如果这个表有个主键id的话，找到今年最早一个id，然后用以下的语句导出数据就比较快
select * from 原表 where id>最早id
select * from 原表 where id>最早id
#8
用数据仓库吧，让数据库自动把历史数据转存
#9
FILEGROUP将历史数据保存到FILEGROUP然后备份单独的FILEGROUP，可以同时选择备份到多个备份文件，这样既可以使用多线程同时可以提升IO的速度。
#10
历史的FILEGROUP还可以设置为只读。
#11
我觉得直接bcpout表里的内容到一个文本文件后把这个文本文件当成备份如何？
不用死磕backup操作吧···
不用死磕backup操作吧···
#12
改名重建表应该比较快吧。
如果没SA权限，有不少办法都可以，直接登录服务器，或是用专门的代码来提升权限。
如果没SA权限，有不少办法都可以，直接登录服务器，或是用专门的代码来提升权限。
#13
连接两个数据库，清空TST环境，然后用insert into dbo1.test1 select * from dbo2.test1
就算有7亿，最多也是10多分钟就可以搞掂
就算有7亿，最多也是10多分钟就可以搞掂
#14
个人觉得最合适的方法肯定是分区表
另外如果老数据不怎么访问的话，也可以直接老数据单独拿出来分表或者分库，这样那个分出来的库如果内容不变，那么只需要备份一次即可。新数据在新表，通过VIEW可以变成1个表
另外也可以试试差异备份，应该可以应付备份慢的问题
另外如果老数据不怎么访问的话，也可以直接老数据单独拿出来分表或者分库，这样那个分出来的库如果内容不变，那么只需要备份一次即可。新数据在新表，通过VIEW可以变成1个表
另外也可以试试差异备份，应该可以应付备份慢的问题