最近有需求,需要用脚本/命令行的形式实现对数据库的备份与还原！尝试几遍,那个郁闷啊！网上的例子又少，大多又运行不了,很是让人无语,弄了几天,最后总算搞清楚了一些,资料也找到了合适的一些!不过这之中还是得自己琢磨着！幸好最终能够实现功能.
首先以下分为两种:
1) 一种是脚本（.sql）的生成.
2 )一种是数据库的备份与还原. 首先贴出SQL生成的脚本:(PowerShell 脚本)
1. release_1.1.ps1
#clear screen(清屏)
cls
#以下是相关加载
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$serverName=$(read-host "Server ('localhost' okay)") #服务器localhost或远程IP
$serverConn=new-object Microsoft.SqlServer.Management.Common.ServerConnection #服务器连接
$serverConn.ServerInstance=$serverName #设置SQL实例
$serverConn.LoginSecure =$false
$serverConn.Login = $(read-host "Login UserName") #登录用户名
$serverConn.Password =$(read-host "Login PassWord") #登录密码(不过是明文显示的)
$user_folder=$(read-host "Please choice a folder location to backup the database") #指定备份存放位置
$objectoption = $(read-host "All Objects = 0, Tables = 1, Table Triggers = 2, Views = 3, Functions = 4, Stored Procedures = 5")
#选择备份的对象 (All Object 、table、trigger、view、function、procedure)
$reg=$(read-host "('WildCard' okay)") #通配符(要什么条件的对象)
#write-host $user_folder #输出备份位置（Debug）
#trap errors #出错日志记录
$errors =$user_folder+"\errors.txt" #指定一个出错日志存放目录文件.
#write-host $errors #输出日志路径(Debug)
trap
{
"______________________" | out-file $errors -append;
"ERROR SCRIPTING TABLES" | out-file $errors -append;
get-date | out-file $errors -append;
"ERROR: " + $_ | out-file $errors -append;
"`serverName = $serverName" | out-file $errors -append;
"`user_folder = $user_folder" | out-file $errors -append;
#throw "ERROR: See $errors"
}
#Checks to see if the supplied Directory is there and creates it if not. Inside a Process to allow a Pipe to use it.
#判断目录是否存在、创建
function MakeDirectory
{
param([string]$DirName)
Process
{
if (!(Test-Path -path $DirName))
{
New-Item $DirName -type directory | Out-Null
}
}
}
MakeDirectory ($user_folder) #创建用户备份目录
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($serverConn) #server对象
#"==============The Database Server have some databases below:============="
#foreach($database in $srv.databases)
#{
# write-host $database.Name
#}
#$db=$(read-host "Choice A database to backup")
#$dbName=$srv.databases[$db].Name
#write-host $dbName
#"==============You choice database have some tables below:============="
#foreach($table in $srv.databases[$db].tables)
#{
# write-host $table.Name
#}
#$objectOption = $(read-host "All Objects = 0, Tables = 1, Table Triggers = 2, Views = 3, Functions = 4, Stored Procedures = 5")
#write-host $objectOption
#$wildcards=$(read-host "Wildcards okay")
#write-host $wildcards
#if($objectOption -eq 0)
#{
# write-host "All Objects"
#}
$dbInput=$(read-host "Choice a database to backup")
$db=$srv.databases[$dbInput]
$dbName=$db.Name
write-host "Current Database:"$dbName
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$scr.Server = $srv
$scrOptions = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$scrOptions.AllowSystemObjects = $false
$scrOptions.IncludeDatabaseContext = $false
$scrOptions.IncludeIfNotExists = $false
$scrOptions.ClusteredIndexes = $true
$scrOptions.NoCollation = $true
$scrOptions.Default = $true
$scrOptions.DriAll = $true
$scrOptions.Indexes = $true
$scrOptions.NonClusteredIndexes = $true
$scrOptions.IncludeHeaders = $false
$scrOptions.ToFileOnly = $true
$scrOptions.Permissions = $true
$scrOptions.ScriptDataCompression = $true
$scrOptions.ScriptDrops = $false
$scrOptions.AppendToFile = $true #是追加还是覆盖
#Set options for SMO.Scripter
$scr.Options = $scrOptions
$backupfolder=$user_folder+"\"+$dbName+"_backup" #filefolder
remove-item $backupfolder\* #删除之前的备份
#write-host $backupfolder（Debug）
$backupfile=$backupfolder+"\"+$dbName+".backup.sql"; #file
#write-host $backupfile(Debug)
">>>>>>>>>>>>Start backup"
$tables=$db.Tables | where {$_.IsSystemObject -eq $false} | where-object {$_.name -like $reg}
if ($objectoption -eq 0 -or $objectoption -eq 1)
{
# script each table
foreach ($table in $tables)
{
MakeDirectory ($backupfolder); #Check for folder, and create if needed
$scrOptions.FileName = $backupfile;
$scr.Options = $scrOptions;
$scr.Script($table);
}
}
if ($objectoption -eq 0 -or $objectoption -eq 2)
{
# Script table triggers (go into tables then triggers)
foreach ($table in $tables)
{
foreach ($trigger in $Table.Triggers | where {$_.IsSystemObject -eq $false})
{
MakeDirectory ($backupfolder); #Check for folder, and create if needed
$scrOptions.FileName = $backupfile;
$scr.Options = $scrOptions;
$scr.Script($trigger);
}
}
}
if ($objectoption -eq 0 -or $objectoption -eq 3)
{
# script each view
foreach ($view in $db.Views | where {$_.IsSystemObject -eq $false})
{
MakeDirectory ($backupfolder); #Check for folder, and create if needed
$scrOptions.FileName = $backupfile;
$scr.Options = $scrOptions;
$scr.Script($view);
}
}
if ($objectoption -eq 0 -or $objectoption -eq 4)
{
# script each function
foreach ($function in $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false})
{
MakeDirectory ($backupfolder); #Check for folder, and create if needed
$scrOptions.FileName = $backupfile;
$scr.Options = $scrOptions;
$scr.Script($function);
}
}
if ($objectoption -eq 0 -or $objectoption -eq 5)
{
# script each stored procedure
foreach ($procedure in $db.StoredProcedures | where {$_.IsSystemObject -eq $false})
{
MakeDirectory ($backupfolder); #Check for folder, and create if needed
$scrOptions.FileName = $backupfile;
$scr.Options = $scrOptions;
$scr.Script($procedure);
}
}
">>>>>>>>>>>>Backup Complete"
2. 批处理 (run.bat):
其中set-location " "自己定义
cls
powershell -command "&{.\release_1.1.ps1 Club.Secretary}"
以下是备份与还原数据库的脚本(PowerShell 脚本)
release_1.0.ps1
#============================================================
# Restore a Database using PowerShell and SQL Server SMO
# Restore to the same database, overwrite existing db
#============================================================
#clear screen
cls
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$serverName=$(read-host "Server ('localhost' okay)")
$serverConn=new-object Microsoft.SqlServer.Management.Common.ServerConnection
$serverConn.ServerInstance=$serverName
$serverConn.LoginSecure =$false
$serverConn.Login = $(read-host "Login UserName")
$serverConn.Password =$(read-host "Login PassWord")
#$database = $(read-host "Database"),
$user_folder=$(read-host "Please choice a folder location to backup the database")
#$objectoption = $(read-host "All Objects = 0, Tables = 1, Table Triggers = 2, Views = 3, Functions = 4, Stored Procedures = 5")
#write-host $user_folder
# trap errors
$errors =$user_folder+"\errors.txt"
#write-host $errors
trap
{
"______________________" | out-file $errors -append;
"ERROR SCRIPTING TABLES" | out-file $errors -append;
get-date | out-file $errors -append;
"ERROR: " + $_ | out-file $errors -append;
"`serverName = $serverName" | out-file $errors -append;
#"`userName = $serverConn.Login" | out-file $errors -append;
"`user_folder = $user_folder" | out-file $errors -append;
#"`$tables = $tables" | out-file $errors -append;
#"`$path = $path" | out-file $errors -append;
#"`$scripts = $scripts" | out-file $errors -append;
#throw "ERROR: See $errors"
}
#Checks to see if the supplied Directory is there and creates it if not. Inside a Process to allow a Pipe to use it.
function MakeDirectory
{
param([string]$DirName)
Process
{
if (!(Test-Path -path $DirName))
{
New-Item $DirName -type directory | Out-Null
}
}
}
MakeDirectory ($user_folder)
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($serverConn)
"==============The Database Server have some databases below:============="
foreach($database in $srv.databases)
{
write-host $database.Name
}
$db=$(read-host "Choice A database to backup")
$dbName=$srv.databases[$db].Name
write-host $dbName
"==============The Database Server have some tables below:============="
foreach($table in $srv.databases[$db].tables)
{
write-host $table.Name
}
#$table=$(read-host "Choice A table to backup")
#write-host $user_folder"\"$dbName".bak"
$folder=$user_folder+"\"+$dbName+".bak"
#write-host $folder
$backupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($folder, "File")
################# Backup ###############
">>>>>>>>>>>>>>>>>>>>>>Start Backup..."
$smoBackup=New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
#2010.10.16 paulus: overwrite
$smoBackup.Initialize = $TRUE
#2010.10.16 paulus: no need for timestamp
#$smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File")
$smoBackup.Devices.Add($backupDevice) #$smoBackup.Devices.AddDevice($user_folder+"\"+$dbName+".bak", "File");
$smoBackup.SqlBackup($srv)
">>>>>>>>>>>>>>>>>>>>>Backup Complete"
################ Start Restore #############
">>>>>>>>>>>>>>>>>>>>>>Start Restore..."
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$smoRestore.NoRecovery = $false;
$smoRestore.Partial = $false
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestore.Devices.Add($backupDevice)
$RestoreName=$(read-host "What you want restore name")
$smoRestore.Database =$RestoreName
$smoRestore.SqlRestore($srv)
#plus: set owner to sa
#$db = New-Object Microsoft.SqlServer.Management.Smo.Database
#$db = $srv.Databases.Item($RestoreName) #$smoRestoreDetails.Rows[0]["DatabaseName"]
#$db.SetOwner("sa", $TRUE)
#"Done"
">>>>>>>>>>>>>>>>>>>>>>Restore Complete"
同时提供以下资料可供参考:
http://www.simple-talk.com/sql/sql-tools/using-powershell-to-generate-table-creation-scripts/
利用 PowerShell 做 SQL 數據庫備份:
利用 PowerShell 自動 Restore 數據庫:
SMO 方法、属性速查：
http://msdn.microsoft.com/zh-cn/library/ms162169.aspx
4028
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
