当前位置:Gxlcms > mysql > SQLServer批量备份与还原

SQLServer批量备份与还原

时间:2021-07-01 10:21:17 帮助过:29人阅读

备份与还原是数据库避不开的主题,而作为DBA,经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求; 在现在都讲究自动化管理的时代,传统的界面操作备份还原的做法不仅浪费时间和精力,而且还很容易出错,并且这次完成后, 下次再有这样的要求

备份与还原是数据库避不开的主题,而作为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

人气教程排行