sql server数据备份----在线等
sql server数据库采用什么工具进行数据备份。请各路大虾指教。
[解决办法]
自带的。。。
[解决办法]
自带的。。。
命令
第3方软件
不知道有没有第4方方法
[解决办法]
右键DB Name -->tasks-->backup
[解决办法]
企业管理器里面，一直展开展到数据库节点，右键——所有任务——备份数据库，就可以备份。
另外也可以在查询分析器中用backup database命令来备份数据库
[解决办法]
--http://blog.csdn.net/sinpoal/archive/2009/11/09/4790995.aspx--推荐看一下
[解决办法]
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/faf37e0c-612d-4943-b13c-ec88a1408ffa.htm
[解决办法]
http://www.orsoon.com/Article/Article_21364.html
[解决办法]
-- 给你一个备份的存储过程（适用于SQL Server 2005 和 SQL Server 2008)-- (你只需要在master数据库中创建此存储过程，然后写个执行计划，调用此存储过程即可）USE [master]GO/****** 对象: StoredProcedure [dbo].[sp_backupdatabase] 脚本日期: 07/15/2009 16:23:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [dbo].[sp_backupdatabase] @bak_path nvarchar(4000)='', --备份路径; @baktype int = null, --备份类型0为全备,1为差异备,2为日志备份 @type int = null, --设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库; @dbnames nvarchar(4000)='', --需要备份或排除的数据库，用,隔开，当@type=3或4时生效 @overdueDay int = null, --设置过期天数，默认天; @compression int =0 --是否采用sql2008的压缩备份,0为否,1为采用压缩as--sql server 2005/2008备份/删除过期备份T-sql 版本v1.0/*author:perfectactiondate :2009.04desc :适用于sql2005/2008备份，自动生成库文件夹，可以自定义备份类型和备份库名等 可以自定义备份过期的天数 删除过期备份功能不会删除最后一次备份，哪怕已经过期 如果某库不再备份，那么也不会再删除之前过期的备份 如有错误请指正，谢谢.*/set nocount on--开启xp_cmdshell支持exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 1 reconfigure with overrideexec sp_configure 'show advanced options', 0reconfigure with overrideprint char(13)+'------------------------'--判断是否填写路径if isnull(@bak_path,'')='' begin print('error:请指定备份路径') return end--判断是否指定需要备份的库if isnull(ltrim(@baktype),'')='' begin print('error:请指定备份类型aa:0为全备,1为差异备,2为日志备份') return endelse begin if @baktype not between 0 and 2 begin print('error:指定备份类型只能为,1,2: 0为全备,1为差异备,2为日志备份') return end end--判断是否指定需要备份的库if isnull(ltrim(@type),'')='' begin print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库') return endelse begin if @type not between 0 and 4 begin print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库') return end end--判断指定库或排除库时，是否填写库名if @type>2 if @dbnames='' begin print('error:备份类型为'+ltrim(@type)+'时，需要指定@dbnames参数') return end--判断指定指定过期时间if isnull(ltrim(@overdueDay),'')=''begin print('error:必须指定备份过期时间,单位为天,0为永不过期') return end--判断是否支持压缩if @compression=1 if charindex('2008',@@version)=0 or charindex('Enterprise',@@version)=0 begin print('error:压缩备份只支持sql2008企业版') return end--判断是否存在该磁盘declare @drives table(drive varchar(1),[size] varchar(20))insert into @drives exec('master.dbo.xp_fixeddrives')if not exists(select 1 from @drives where drive=left(@bak_path,1)) begin print('error:不存在该磁盘:'+left(@bak_path,1)) return end--格式化参数select @bak_path=rtrim(ltrim(@bak_path)),@dbnames=rtrim(ltrim(@dbnames))if right(isnull(@bak_path,''),1)!='\' set @bak_path=@bak_path+'\'if isnull(@dbnames,'')!='' set @dbnames = ','+@dbnames+','set @dbnames=replace(@dbnames,' ','')--定义变量declare @bak_sql nvarchar(max),@del_sql nvarchar(max),@i int,@maxid intdeclare @dirtree_1 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int)declare @dirtree_2 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int,dbname varchar(300),baktime datetime,isLastbak int)declare @createfolder nvarchar(max),@delbackupfile nvarchar(max),@delbak nvarchar(max)--获取需要备份的库名--------------------startdeclare @t table(id int identity(1,1) primary key,name nvarchar(max))declare @sql nvarchar(max)set @sql = 'select name from sys.databases where state=0 and name!=''tempdb'' ' + case when @baktype=2 then ' and recovery_model!=3 ' else '' end + case @type when 0 then 'and 1=1' when 1 then 'and database_id<=4' when 2 then 'and database_id>4' when 3 then 'and charindex('',''+Name+'','','''+@dbnames+''')>0' when 4 then 'and charindex('',''+Name+'','','''+@dbnames+''')=0 and database_id>4' else '1>2' endinsert into @t exec(@sql)--获取需要备份的库名---------------------end--获取需要创建的文件夹------------------startinsert into @dirtree_1 exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')select @createfolder=isnull(@createfolder,'')+'exec master.dbo.xp_cmdshell ''md '+@bak_path+''+name+''',no_output '+char(13)from @t as a left join @dirtree_1 as b on a.name=b.subdirectory and b.files=0 and depth=1 where b.id is null--获取需要创建的文件夹-------------------end--生成处理过期备份的sql语句-------------startif @overdueDay>0begin insert into @dirtree_2(subdirectory,depth,files) exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1') if @baktype =0 delete from @dirtree_2 where depth=1 or files=0 or charindex('_Full_bak_',subdirectory)=0 if @baktype =1 delete from @dirtree_2 where depth=1 or files=0 or charindex('_Diff_bak_',subdirectory)=0 if @baktype=2 delete from @dirtree_2 where depth=1 or files=0 or charindex('_Log_bak_',subdirectory)=0 if exists(select 1 from @dirtree_2) delete from @dirtree_2 where isdate( left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' + substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2) )=0 if exists(select 1 from @dirtree_2) update @dirtree_2 set dbname = case when @baktype=0 then left(subdirectory,charindex('_Full_bak_',subdirectory)-1) when @baktype=1 then left(subdirectory,charindex('_Diff_bak_',subdirectory)-1) when @baktype=2 then left(subdirectory,charindex('_Log_bak_',subdirectory)-1) else '' end ,baktime=left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' + substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2) from @dirtree_2 as a delete @dirtree_2 from @dirtree_2 as a left join @t as b on b.name=a.dbname where b.id is null update @dirtree_2 set isLastbak= case when (select max(baktime) from @dirtree_2 where dbname=a.dbname)=baktime then 1 else 0 end from @dirtree_2 as a select @delbak=isnull(@delbak,'')+'exec master.dbo.xp_cmdshell ''del '+@bak_path+''+dbname+'\' +subdirectory+''',no_output '+char(13) from @dirtree_2 where isLastbak=0 and datediff(day,baktime,getdate())>@overdueDayend--生成处理过期备份的sql语句--------------endbegin try print(@createfolder) --创建备份所需文件夹 exec(@createfolder) --创建备份所需文件夹end trybegin catch print 'err:'+ltrim(error_number()) print 'err:'+error_message() returnend catchselect @i=1 ,@maxid=max(id) from @twhile @i<=@maxidbegin select @bak_sql=''+char(13)+'backup '+ case when @baktype=2 then 'log ' else 'database ' end +quotename(Name)+' to disk='''+@bak_path + Name+'\'+ Name+ case when @baktype=0 then '_Full_bak_' when @baktype=1 then '_Diff_bak_' when @baktype=2 then '_Log_bak_' else null end + case when @compression=1 then 'compression_' else '' end+ replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+ case when @baktype=2 then '.trn' when @baktype=1 then '.dif' else '.bak' end +'''' + case when @compression=1 or @baktype=1 then ' with ' else '' end + case when @compression=1 then 'compression,' else '' end + case when @baktype=1 then 'differential,' else '' end + case when @compression=1 or @baktype=1 then ' noformat' else '' end from @t where id=@i set @i=@i+1 begin try print(@bak_sql)--循环执行备份 exec(@bak_sql) --循环执行备份 end try begin catch print 'err:'+ltrim(error_number()) print 'err:'+error_message() end catchendbegin try print(@delbak) --删除超期的备份 exec(@delbak) --删除超期的备份end trybegin catch print 'err:'+ltrim(error_number()) print 'err:'+error_message()end catch--关闭xp_cmdshell支持--exec sp_configure 'show advanced options', 1--reconfigure with override--exec sp_configure 'xp_cmdshell', 0 --reconfigure with override--exec sp_configure 'show advanced options', 0--reconfigure with override
[解决办法]
-- 11楼是日志备份（每半个小时一次），-- 下面是数据库完全备份（每天0点执行一次)：-- 还是用的执行计划：---------------Step 1: 定义变量----------------------declare @back_lg_dir nvarchar(100) --日志备份目录declare @back_db_dir varchar(100) ---数据备份目录declare @cmd1 varchar(100) ---用于压缩数据备份文件declare @cmd2 varchar(100) ---用于压缩日志备份文件declare @tmp_dir varchar(100) ---存放压缩文件的共享文件夹declare @cmd3 varchar(100) ---用于存放数据压缩文件副本，以便远程拷贝declare @cmd4 varchar(100) ---用于存放日志压缩文件副本，以便远程拷贝declare @del_db_5ago varchar(100) ---删除5天前的数据备份declare @cmd5 varchar(100) ---删除5天前的数据备份文件夹declare @cmd6 varchar(100) ---删除5天前的数据备份压缩文件declare @del_lg_5ago varchar(100) ---删除5天前的日志备份declare @cmd7 varchar(100) ---删除5天前的日志备份文件夹declare @cmd8 varchar(100) ---删除5天前的日志备份压缩文件set @back_lg_dir = convert(nvarchar(10),getdate()-1,120)set @back_lg_dir = replace(@back_lg_dir, '-' , '')set @back_lg_dir = 'I:\MRMSQL2005Backup\mrm_lg_'+@back_lg_dirset @back_db_dir = convert(nvarchar(10),getdate(),120)set @back_db_dir = replace(@back_db_dir, '-' , '')set @back_db_dir = 'I:\MRMSQL2005Backup\mrm_db_'+@back_db_dirset @tmp_dir = 'I:\mrm_tmp_db_dir'set @del_db_5ago = convert(nvarchar(10),getdate()-5,120)set @del_db_5ago = replace(@del_db_5ago, '-' , '')set @del_db_5ago = 'I:\MRMSQL2005Backup\mrm_db_'+@del_db_5agoset @del_lg_5ago = convert(nvarchar(10),getdate()-5,120)set @del_lg_5ago = replace(@del_lg_5ago, '-' , '')set @del_lg_5ago = 'I:\MRMSQL2005Backup\mrm_lg_'+@del_lg_5agoset @cmd1 = 'rar a ' + @back_db_dir + '.rar' + ' ' + @back_db_dirset @cmd2 = 'rar a ' + @back_lg_dir + '.rar' + ' ' + @back_lg_dirset @cmd3 = 'copy ' + @back_db_dir + '.rar' + ' ' + @tmp_dirset @cmd4 = 'copy ' + @back_lg_dir + '.rar' + ' ' + @tmp_dirset @cmd5 = 'rd /s /q ' + @del_db_5agoset @cmd6 = 'del ' + @del_db_5ago + '.rar'set @cmd7 = 'rd /s /q ' + @del_lg_5agoset @cmd8 = 'del ' + @del_lg_5ago + '.rar'------Step 2: 备份当前数据库日志------(包括MRM20080608和sbo-common数据库)----exec dbo.sp_backupdatabase @back_lg_dir,2,3,MRM20080608,30,0exec dbo.sp_backupdatabase @back_lg_dir,2,3,'sbo-common',30,0------Step 3: 收缩数据库日志----------DUMP TRANSACTION [MRM20080608] WITH NO_LOGBACKUP LOG MRM20080608 WITH NO_LOGDBCC SHRINKDATABASE(MRM20080608)DUMP TRANSACTION [sbo-common] WITH NO_LOGBACKUP LOG [sbo-common] WITH NO_LOGDBCC SHRINKDATABASE([sbo-common])DUMP TRANSACTION [TEMPDB] WITH NO_LOGBACKUP LOG TEMPDB WITH NO_LOGDBCC SHRINKDATABASE(TEMPDB)------Step 4: 全面备份数据库----------exec dbo.sp_backupdatabase @back_db_dir,0,0,'',30,0exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 1 reconfigure with overrideexec master..xp_cmdshell @cmd1 ---因为上一步：压缩数据库文件 需要很久的时间(超过30分钟)---而我的日志备份计划每30分钟运行一次，此时(压缩完数据库备份文件后)---已经由另外一个运行计划(日志备份计划)更改了其xp_cmdshell的设置，---所以需要再次运行下面的语句，以便再次打开xp_cmdshell支持exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 1 reconfigure with overrideexec master..xp_cmdshell @cmd2exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 1 reconfigure with overrideexec master..xp_cmdshell @cmd3exec master..xp_cmdshell @cmd4exec master..xp_cmdshell @cmd5exec master..xp_cmdshell @cmd6exec master..xp_cmdshell @cmd7exec master..xp_cmdshell @cmd8--安全起见，关闭xp_cmdshell支持exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 0 reconfigure with overrideexec sp_configure 'show advanced options', 0reconfigure with override
[解决办法]
自带的工具
企业管理器中的备份与还原操作。