蛙蛙推荐:利用WMI脚本批量恢复SQLSERVER数据库
问题提出
蛙蛙求助:以编程的方式还原sqlserver数据库问题
我有一个目录下面都是sqlserver的数据库备份文件,比如a.bak,b.bak,c.bak等,有没有办法一下把他们都还原到本地SQLSERVER数据库里面呀,过程是自动建立a,b,c这样的数据库,然后把a.bak恢复到a,b.bak恢复到b,依次类推,因为备份文件的原路径和新库的路径不一样,所以要有一些额外处理,谁能解决一下,因为这个目录下有几十个库的备份文件呢,现在我的机器新安装了一个SQLSERVER,要把他们全部恢复,当时没有分离库,所以不能直接附加.
设计方案
可以利用WMI脚本扫描存放数据库备份文件的目录,然后按照一定的规则生成一个恢复数据库的T-sql脚本文件,然后用脚本执行osql程序来执行这个脚本完成数据库恢复,这里没有使用过多的错误处理和事务的代码,因此要人为的确定数据库恢复的T-SQL语句尽量不要引发异常。
解决方案
一、我们先来看一下恢复数据库的T-SQL命令,以便理解后面通过脚本来创建T-SQL的原理
USE
master
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--
如果要创建的数据库已经存在,那么删除它
IF
EXISTS
(
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name
=
N
'
article
'
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
DROP
DATABASE
[
article
]
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--
创建一个新数据库,要指定新建数据库的数据文件和日志文件的名称和位置,初始化大小
--
增长幅度,最大值等内容
CREATE
DATABASE
article
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
ON
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
( NAME
=
N
'
article_dat
'
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FILENAME
=
N
'
d:\sql2000\MSSQL\data\article_Data.MDF
'
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SIZE
=
1
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
MAXSIZE
=
50
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FILEGROWTH
=
5
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
LOG
ON
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
( NAME
=
N
'
article_log
'
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FILENAME
=
N
'
d:\sql2000\MSSQL\data\article_Log.LDF
'
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
SIZE
=
1MB,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
MAXSIZE
=
25MB,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FILEGROWTH
=
5MB )
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
--
把指定的数据库备份文件恢复到刚刚建立的数据库里,这里要指定数据库备份文件的位置
--
以及要恢复到的数据库,因为备份文件来自未知的机器,备份的时候原数据库和新数据库
--
的数据文件和日志文件的位置不匹配,所以要用with move指令来完成强制文件移动,如果
--
是通过管理器备份的数据库文件,数据库文件和日志文件名分别是数据库名跟上"_Data"或
--
"_Log",这是一个假设哦,如果不是这样,脚本有可能会出错
RESTORE
DATABASE
[
article
]
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM
DISK
=
'
E:\windowdatabase\article.bak
'
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
WITH
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
MOVE
'
article_Data
'
TO
'
d:\sql2000\MSSQL\data\article_Data.MDF
'
,
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
MOVE
'
article_Log
'
TO
'
d:\sql2000\MSSQL\data\article_Log.LDF
'
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
从中可以看到
T-SQL的强大。
二、利用WMI脚本生成t-sql脚本并且调用osql程序执行它
restore.vbs
Const
ForAppending
=
8
'
指定新建的文件可以写入,因为不能使用枚举,所以用常量定义
Const
baksqlfile
=
"
c:\restore.sql
"
'
指定要生成SQL脚本文件的路径
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
'
利用FSO创建一个文本文件
Set
objFSO
=
CreateObject
(
"
Scripting.FileSystemObject
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
Set
objTextFile
=
objFSO.OpenTextFile _
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(baksqlfile, ForAppending,
True
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
'
指定要创建新数据库的数据和日志保存的目录
Const
NEWDATAPATH
=
"
d:\sql2000\MSSQL\data\"
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
'
创建一个WMI对象
strComputer
=
"
."
Set
objWMIService
=
GetObject
(
"
winmgmts:
"
_
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
&
"
{impersonationLevel=impersonate}!\\
"
&
strComputer
&
"
\root\cimv2
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
'
获取指定目录的文件列表,关于更多的WMI对象的操作方说明请查看MSDN
'
这里没有检查文件是否是数据库备份文件的逻辑,请自行保证
Set
FileList
=
objWMIService.ExecQuery _
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
(
"
ASSOCIATORS OF {Win32_Directory.Name='E:\windowdatabase'} Where
"
_
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
&
"
ResultClass = CIM_DataFile
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
'
遍历文件列表,构建t-sql脚本的文本
For
Each
objFile In FileList
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
If
objFile.Extension
=
"
bak
"
Then
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
strbakName
=
objFile.Drive
&
objFile.Path
&
_
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
objFile.FileName
&
"
.
"
&
objFile.Extension
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
Wscript.Echo strbakName
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
with
objTextFile
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
USE master
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
GO
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'
"
&
objFile.FileName
&
"
')
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
DROP DATABASE [
"
&
objFile.FileName
&
"
]
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
GO
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine()
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
CREATE DATABASE
"
&
objFile.FileName
&
""
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
ON
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
( NAME = N'
"
&
objFile.FileName
&
"
_dat',
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
FILENAME = N'
"
&
NEWDATAPATH
&
objFile.FileName
&
"
_Data.MDF',
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
SIZE = 1,
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
MAXSIZE = 50,
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
FILEGROWTH = 5 )
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
LOG ON
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
( NAME = N'
"
&
objFile.FileName
&
"
_log',
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
FILENAME = N'
"
&
NEWDATAPATH
&
objFile.FileName
&
objFile.FileName
&
"
_Log.LDF',
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
SIZE = 1MB,
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
MAXSIZE = 25MB,
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
FILEGROWTH = 5MB )
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
GO
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine()
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
RESTORE DATABASE [
"
&
objFile.FileName
&
"
]
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
FROM DISK = '
"
&
strbakName
&
"
'
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
WITH
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
MOVE '
"
&
objFile.FileName
&
"
_Data' TO '
"
&
NEWDATAPATH
&
objFile.FileName
&
objFile.FileName
&
"
_Data.MDF',
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(vbtab
&
"
MOVE '
"
&
objFile.FileName
&
"
_Log' TO '
"
&
NEWDATAPATH
&
objFile.FileName
&
objFile.FileName
&
"
_Log.LDF'
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine(
"
GO
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
.WriteLine()
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
End
with
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
End
If
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
Next
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
objTextFile.Close
'
关闭文本流
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
'
创建shell对象用来调用osql程序
Set
objShell
=
CreateObject
(
"
WScript.Shell
"
)
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
objShell.Run
"
%COMSPEC% /k osql -E -i
"
&
baksqlfile
![](http://onlytiancai.cnblogs.com/Images/OutliningIndicators/None.gif)
关于wsh调用命令行程序请参考以下链接
http://www.microsoft.com/china/technet/columns/scripts/sg1002.asp
三、执行脚本
在CMD命令下输入Cscript restore.vbs回车后会看到下图
![](https://i-blog.csdnimg.cn/blog_migrate/2acd42b84d30a62fe458805d25616792.jpeg)
然后就会出现调用OSQL的窗口,如图
![](https://i-blog.csdnimg.cn/blog_migrate/77aaa26cce1e7ade48b6ec966a0fc090.jpeg)
小结:好多情况下,脚本还是很有用的,像这次遇到这个问题,完全没有必要用.NET写一个工具来完成,用脚本,方便快捷,用了就扔,开发也方便,而且能解决问题,像好多处理excel,导入远程HTML代码等都可以用脚本来做的。偶经常用的是VBS和JS,perl,python什么的都不会。