SQLServer批量备份与还原
发布日期：2021-05-09 04:01:45
浏览次数：18
分类：博客文章
本文共 16325 字，大约阅读时间需要 54 分钟。
原文地址：
备份与还原是数据库避不开的主题，而作为DBA，经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求；
在现在都讲究自动化管理的时代，传统的界面操作备份还原的做法不仅浪费时间和精力，而且还很容易出错，并且这次完成后，
下次再有这样的要求，必须又重头开始（估计做5次就能做得人狂吐）；于是，我们需要一套应对这种频繁操作、耗时、耗精力
的通用处理方法，所以以下批处理脚本就诞生了。
脚本主要的功能：
1. 备份一个服务器上的所有数据库（当然你也可以选择），备份文件按数据库名+日期生成，以.bak 结尾；
2. 将所有的备份文件还原到一台新机器上；
3. 验证磁盘和路径的正确性；
说明：
脚本合适 SQLServer 2005 & 2008 版本；
批量备份数据库：
-----------------------------批量备份数据------------------------------------------- Use master GO /*=================Usp_BackUp_DataBase======================== =====BackUp Sigle DataBase ====== =====Ken.Guo ====== =====2010.9.10 ====== =====Version: 2005 & 2008 SQL Server ====== =====EXEC Usp_BackUp_DataBase 'MyDB','D:\BackUp' ====== ============================================================ */ CREATEPROC[dbo].[Usp_BackUp_DataBase]@DatabaseNamenvarchar(200),@Pathnvarchar(200) AS BEGIN DECLARE@fnvarchar(200) ,@sqlvarchar(1000) SET@fn=@Path+(casewhenright(@Path,1) <>'\'then'\'else''end) +@DatabaseName+'_' +convert(char(8),getdate(),112)+'_' +replace(convert(char(8),getdate(),108),':','') +'.bak' set@sql='backup database '+@DatabaseName+' to disk = N'''+@fn+'''' --SELECT @sql EXEC(@sql) END GO Use master GO /*=============BackUp Mutile DataBase=========================*/ DECLARE@dbnamenvarchar(200) ,@backup_pathnvarchar(200) SET@backup_path='D:\BackUp\' DECLARE db_info CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR--根据查询，添加其他筛选条件 SELECT name FROM master.sys.databases WITH(NOLOCK) WHERE database_id>4 OPEN db_info FETCHNEXTFROM db_info INTO@dbname WHILE@@FETCH_STATUS=0 begin EXEC master.dbo.Usp_BackUp_DataBase @dbname,@backup_path FETCHNEXTFROM db_info INTO@dbname END close db_info deallocate db_info ---------------------------------BackUp DataBase End------------------------------------
检查还原磁盘：
Use master GO /*=================Check Restore Path Drives Exists========================== =====Ken.Guo ====== =====2010.9.10 ====== =====EXEC Usp_Check_DriveExists @RestoreDataPath,@ResultCount OUTPUT ====== =========================================================================== */ CREATEPROC Usp_Check_DriveExists( @RestoreDataPathnvarchar(200) ,@ResultCountint OUTPUT) AS BEGIN --Check Restore Path and Size >1000M ifCHARINDEX(':',@RestoreDataPath)>0 begin DECLARE@Drivenvarchar(10) ,@errorinfonvarchar(500) DECLARE@DriveListTABLE ( Drive nvarchar(10) ,DSize bigint ) INSERTINTO@DriveList EXEC master.dbo.xp_fixeddrives SET@Drive=Left(@RestoreDataPath,CHARINDEX(':',@RestoreDataPath)-1) ifnotexists(SELECT * FROM@DriveList WHERE Drive=@Drive AND DSize>1024 ) begin set@errorinfo=N'找不到还原磁盘:'+@Drive+N' ，或者磁盘剩余空间小于1G' RAISERROR50001@errorinfo set@ResultCount=0 return end end elseif(LEN(@RestoreDataPath)>1) ANDCHARINDEX(':',@RestoreDataPath)=0 begin set@errorinfo=N'还原路径错误:'+@RestoreDataPath+N'，必须包含":" 号' Raiserror50001@errorinfo set@ResultCount=0 return end set@ResultCount=1 end GO 还原单个数据库：
Use master GO /*=================Usp_RestoreDataBaseFormPath======================================= =====Restore Single DataBase From a Back File ====== =====Ken.Guo ====== =====2010.9.10 ====== =====Version: 2005 & 2008 SQL Server ====== =====Usp_RestoreDataBaseFormPath 'D:\databack\dbcenter.bak','D:\Data',0 ====== =====Key Point Info: ====== --Restore HeaderOnly from disk='D:\data\xx.bak' --Restore FileListOnly from disk='D:\data\xx.bak' =================================================================================== */ CREATEPROC Usp_RestoreDataBaseFormPath (@DatabBaseBakPathnvarchar(400), @RestoreDataPathnvarchar(400)='', --RESTORE DATABASE PATH @IsRunsmallint=0-- 0 PRINT 1 run ) AS BEGIN set nocount on declare@dbnamenvarchar(200),@SQLnvarchar(4000),@DirSQLnvarchar(1000),@errorinfonvarchar(300) --add path \ if (@RestoreDataPathisnotnull) andlen(@RestoreDataPath)>1 and (right(@RestoreDataPath,1)<>'\') set@RestoreDataPath=@RestoreDataPath+'\' declare@checkdriveint set@checkdrive=1 exec master.dbo.Usp_Check_DriveExists @RestoreDataPath,@checkdrive output if(@checkdrive<>1) Goto ExitFLag DECLARE@BakFileListTABLE ( LogicalName nvarchar(128) ,PhysicalName nvarchar(260) ) DECLARE@BakHeaderInfoTABLE ( DatabaseName nvarchar(128) ) ifCharindex('Microsoft SQL Server 2008',@@VERSION)>0 begin --SQL Server 2008 DECLARE@BakFileList2008TABLE ( LogicalName nvarchar(128) ,PhysicalName nvarchar(260) ,Type char(1) ,FileGroupName nvarchar(128) ,SIZE numeric(20,0) ,MaxSize numeric(20,0) ,FileID bigint ,CreateLSN numeric(25,0) ,DropLSN numeric(25,0) NULL ,UniqueID uniqueidentifier ,ReadOnlyLSN numeric(25,0) NULL ,ReadWriteLSN numeric(25,0) NULL ,BackupSizeInBytes bigint ,SourceBlockSize int ,FileGroupID int ,LogGroupGUID uniqueidentifierNULL ,DifferentialBaseLSN numeric(25,0) NULL ,DifferentialBaseGUID uniqueidentifier ,IsReadOnly bit ,IsPresent bit ,TDEThumbprint varbinary(32) ) INSERTINTO@BakFileList2008 EXEC sp_executesql N'Restore FileListOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath DECLARE@BakHeaderInfo2008TABLE ( BackupName nvarchar(128) ,BackupDescription nvarchar(255) ,BackupType smallint ,ExpirationDate datetime ,Compressed tinyint ,POSITION smallint ,DeviceType tinyint ,UserName nvarchar(128) ,ServerName nvarchar(128) ,DatabaseName nvarchar(128) ,DatabaseVersion int ,DatabaseCreationDate datetime ,BackupSize numeric(20,0) ,FirstLSN numeric(25,0) ,LastLSN numeric(25,0) ,CheckpointLSN numeric(25,0) ,DatabaseBackupLSN numeric(25,0) ,BackupStartDate datetime ,BackupFinishDate datetime ,SortOrder smallint ,CodePage smallint ,UnicodeLocaleId int ,UnicodeComparisonStyle int ,CompatibilityLevel tinyint ,SoftwareVendorId int ,SoftwareVersionMajor int ,SoftwareVersionMinor int ,SoftwareVersionBuild int ,MachineName nvarchar(128) ,Flags int ,BindingID uniqueidentifier ,RecoveryForkID uniqueidentifier ,COLLATION nvarchar(128) ,FamilyGUID uniqueidentifier ,HasBulkLoggedData bit ,IsSnapshot bit ,IsReadOnly bit ,IsSingleUser bit ,HasBackupChecksums bit ,IsDamaged bit ,BeginsLogChain bit ,HasIncompleteMetaData bit ,IsForceOffline bit ,IsCopyOnly bit ,FirstRecoveryForkID uniqueidentifier ,ForkPointLSN numeric(25,0) NULL ,RecoveryModel nvarchar(60) ,DifferentialBaseLSN numeric(25,0) NULL ,DifferentialBaseGUID uniqueidentifier ,BackupTypeDescription nvarchar(60) ,BackupSetGUID uniqueidentifierNULL ,CompressedBackupSize numeric(20,0) ) INSERTINTO@BakHeaderInfo2008 EXEC sp_executesql N'Restore HeaderOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath insertinto@BakHeaderInfo(DatabaseName) select DatabaseName from@BakHeaderInfo2008 insertinto@BakFileList(LogicalName ,PhysicalName) select LogicalName ,PhysicalName from@BakFileList2008 end else begin --SQL Server 2005 DECLARE@BakFileList2005TABLE ( LogicalName nvarchar(128) ,PhysicalName nvarchar(260) ,Type char(1) ,FileGroupName nvarchar(128) ,SIZE numeric(20,0) ,MaxSize numeric(20,0) ,FileID bigint ,CreateLSN numeric(25,0) ,DropLSN numeric(25,0) NULL ,UniqueID uniqueidentifier ,ReadOnlyLSN numeric(25,0) NULL ,ReadWriteLSN numeric(25,0) NULL ,BackupSizeInBytes bigint ,SourceBlockSize int ,FileGroupID int ,LogGroupGUID uniqueidentifierNULL ,DifferentialBaseLSN numeric(25,0) NULL ,DifferentialBaseGUID uniqueidentifier ,IsReadOnly bit ,IsPresent bit ) INSERTINTO@BakFileList2005 EXEC sp_executesql N'Restore FileListOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath DECLARE@BakHeaderInfo2005TABLE ( BackupName nvarchar(128) ,BackupDescription nvarchar(255) ,BackupType smallint ,ExpirationDate datetime ,Compressed tinyint ,POSITION smallint ,DeviceType tinyint ,UserName nvarchar(128) ,ServerName nvarchar(128) ,DatabaseName nvarchar(128) ,DatabaseVersion int ,DatabaseCreationDate datetime ,BackupSize numeric(20,0) ,FirstLSN numeric(25,0) ,LastLSN numeric(25,0) ,CheckpointLSN numeric(25,0) ,DatabaseBackupLSN numeric(25,0) ,BackupStartDate datetime ,BackupFinishDate datetime ,SortOrder smallint ,CodePage smallint ,UnicodeLocaleId int ,UnicodeComparisonStyle int ,CompatibilityLevel tinyint ,SoftwareVendorId int ,SoftwareVersionMajor int ,SoftwareVersionMinor int ,SoftwareVersionBuild int ,MachineName nvarchar(128) ,Flags int ,BindingID uniqueidentifier ,RecoveryForkID uniqueidentifier ,COLLATION nvarchar(128) ,FamilyGUID uniqueidentifier ,HasBulkLoggedData bit ,IsSnapshot bit ,IsReadOnly bit ,IsSingleUser bit ,HasBackupChecksums bit ,IsDamaged bit ,BeginsLogChain bit ,HasIncompleteMetaData bit ,IsForceOffline bit ,IsCopyOnly bit ,FirstRecoveryForkID uniqueidentifier ,ForkPointLSN numeric(25,0) NULL ,RecoveryModel nvarchar(60) ,DifferentialBaseLSN numeric(25,0) NULL ,DifferentialBaseGUID uniqueidentifier ,BackupTypeDescription nvarchar(60) ,BackupSetGUID uniqueidentifierNULL ) INSERTINTO@BakHeaderInfo2005 EXEC sp_executesql N'Restore HeaderOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath insertinto@BakHeaderInfo(DatabaseName) select DatabaseName from@BakHeaderInfo2005 insertinto@BakFileList(LogicalName ,PhysicalName) select LogicalName ,PhysicalName from@BakFileList2005 end --Check back file info ifnotexists (select1from@BakFileList) ORnotexists (select1from@BakHeaderInfo) begin set@errorinfo=N'取不到备份文件:'+@DatabBaseBakPath+N' 的信息，请检查备份文件是否正确或者版本是否兼容' Raiserror50001@errorinfo Goto ExitFLag end --Get DataBase Name SELECTTOP1@dbname=databasename FROM@BakHeaderInfo ifexists (select1from master.sys.databases with(nolock) where name=@dbname) begin set@errorinfo=N'数据库：'+@dbname+N'已经存在，不能还原' Raiserror50001@errorinfo Goto ExitFLag end DECLARE@LogicalNamenvarchar(200),@PhysicalNamenvarchar(400) ,@posint ,@endposint,@LastPhysicalNamenvarchar(400) DECLARE db_file CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC FOR SELECT LogicalName ,PhysicalName FROM@BakFileList OPEN db_file set@DirSQL='' set@SQL=+N'RESTORE DATABASE '+QUOTENAME(@dbname)+' from disk=N'''+@DatabBaseBakPath+'''' set@SQL=@SQL+char(13)+Char(10)+N' WITH FILE=1 ' FETCHNEXTFROM db_file INTO@LogicalName,@PhysicalName WHILE@@FETCH_STATUS=0 begin ---Get DB PhysicalName set@endpos=0 whileCHARINDEX('\',@PhysicalName)>0 begin set@pos=CHARINDEX('\',@PhysicalName,@endpos) if(@pos=0) break; set@endpos=@pos+1; end --create new db path if(len(@RestoreDataPath)>1) begin set@PhysicalName=@RestoreDataPath+@dbname+'\'+SUBSTRING(@PhysicalName,@endpos,LEN(@PhysicalName)-@endpos+1) set@DirSQL=N'EXEC master.sys.xp_create_subdir N'''+@RestoreDataPath+@dbname+'''' END else begin iflen(@DirSQL)<1OR (SUBSTRING(@PhysicalName,1,@endpos-1)<>@LastPhysicalName) if(len(@DirSQL)<1) set@DirSQL=N'EXEC master.sys.xp_create_subdir N'''+SUBSTRING(@PhysicalName,1,@endpos-1)+'''' else set@DirSQL=@DirSQL+char(13)+N'EXEC master.sys.xp_create_subdir N'''+SUBSTRING(@PhysicalName,1,@endpos-1)+'''' ---Check Drives set@checkdrive=1 exec master.dbo.Usp_Check_DriveExists @PhysicalName,@checkdrive output if(@checkdrive<>1) Goto ExitFLag set@LastPhysicalName=SUBSTRING(@PhysicalName,1,@endpos-1); END set@SQL=@SQL+char(13)+Char(10)+N' ,Move N'''+@LogicalName+''''+' TO N'''+@PhysicalName+'''' FETCHNEXTFROM db_file INTO@LogicalName,@PhysicalName end set@SQL=@SQL+char(13)+Char(10)+N' ,NOUNLOAD,Recovery,STATS = 10' if(@IsRun=0) print( @DirSQL+char(13)+char(10)+'GO'+char(13)+Char(10)+@SQL+char(13)) else begin print('-----------Begin Restore Database:'+@dbname+'------------------') exec(@DirSQL) exec(@SQL) print('-----------End Restore Database:'+@dbname+'---------------------'+char(13)) end close db_file deallocate db_file ExitFLag: set nocount off end 批量还原数据库：
Use master GO /*=================Usp_RestoreMuiteDataBaseFromPath======================== =====Restore Mutite DataBase File From a Path ====== =====Ken.Guo ====== =====2010.9.10 ====== =====Version: 2005 & 2008 SQL Server ====== =====EXEC Usp_RestoreMuiteDataBaseFromPath 'D:\databack','',0 ====== ========================================================================= */ CREATEPROC Usp_RestoreMuiteDataBaseFromPath ( @DatabBaseBakPathnvarchar(400) ,@RestoreDataPathnvarchar(400)=''--RESTORE DATABASE PATH ,@IsRunsmallint=0-- 0 PRINT 1 run ) AS BEGIN set nocount on DECLARE@BackUpFileNamenvarchar(200) ,@DbNamenvarchar(200) ,@errorinfonvarchar(400) IFnotexists(SELECT1 FROM master.sys.procedures WITH(NOLOCK) WHERE name=N'Usp_RestoreDataBaseFormPath' ) begin Raiserror50001 N'找不到存储过程SP_RestoreDataBaseFormPath ' Goto ExitFLag end --add path \ if (@DatabBaseBakPathisnotnull) andlen(@DatabBaseBakPath)>1 and (right(@DatabBaseBakPath,1)<>'\') set@DatabBaseBakPath=@DatabBaseBakPath+'\' --Check Restore Path and Size >1000M DECLARE@checkdriveint SET@checkdrive=1 EXEC master.dbo.Usp_Check_DriveExists @RestoreDataPath,@checkdrive OUTPUT IF(@checkdrive<>1) Goto ExitFLag DECLARE@DirTABLE ( BackDBFileName nvarchar(100) ,DEPTH int ,[File]int ) INSERTINTO@DirEXEC xp_dirtree @DatabBaseBakPath ,1 ,1 DELETEFROM@Dir WHEREcharindex('.bak',BackDBFileName)=0 ifnotexists (selecttop11from@Dir) begin Raiserror50001 N'在提供的路径下没有找到合符要求的备份文件' Goto ExitFLag end declare db_file Cursor Local Static Read_Only Forward_Only for select BackDBFileName from@Dir Open db_file FetchNextfrom db_file into@BackUpFileName while@@FETCH_STATUS=0 begin --Restore DataBase set@BackUpFileName=@DatabBaseBakPath+@BackUpFileName exec master.dbo.Usp_RestoreDataBaseFormPath @BackUpFileName,@RestoreDataPath,@IsRun FetchNextfrom db_file into@BackUpFileName end Close db_file deallocate db_file ExitFLag: set nocount off end 发表评论
最新留言
做的很好，不错不错
[***.243.131.199]2025年04月12日 14时28分36秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
设计模式学习笔记（二十三：解释器模式）
2021-05-09
使用MongoDB和JSP实现一个简单的购物车系统
2021-05-09
算法笔记_069:Floyd算法简单介绍（Java）
2021-05-09
算法笔记_075:蓝桥杯练习 最短路（Java）
2021-05-09
from flask.ext.wtf import Form导入报错
2021-05-09
Python学习笔记_05:使用Flask+MySQL实现用户登陆注册以及增删查改操作
2021-05-09
Deepin_使用Python+MySQL创建工作日志记录
2021-05-09
dpdk在虚拟机上出错处理
2021-05-09
Nagios 系统监控基本安装配置过程详解
2021-05-09
Macbook 彻彻底底的卸载MySQL
2021-05-09
CSS 字体属性和文本属性的初步了解
2021-05-09
ASP.NET Core 一步步搭建个人网站（4）_主页和登录验证
2021-05-09
SSIS 转移数据库和SQL Server对象组件
2021-05-09
NumPy 学习 第一篇：ndarray 的创建和形状操纵
2021-05-09
NumPy 学习 第四篇：数组的基本操作
2021-05-09
正则表达式 第四篇：贪婪和消耗字符
2021-05-09
SQL Server 列存储索引 第二篇：设计
2021-05-09
ADF 第五篇：转换数据
2021-05-09
Databricks 第4篇：pyspark.sql 分组统计和窗口
2021-05-09
博客系列目录
2021-05-09
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 459893423 位访客
访问时间: 2025-04-21 07:16:36
访问IP: 3.133.128.168
Copyright © 2020 - 2025 css8.cn 京ICP备2021015314号-1
手机版