|
我们在搭建网狐端的时候数据库很多，需要一个一个的去还原，这里我做了一个一键还原脚本 只需要修改数据库的实例名 数据库保存路径执行命令就可以还原数据库
命令： - # 加载SQL Server管理对象
- [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
- [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
- # 配置参数
- $sqlServer = "C20250416167611\SQLEXPRESS" # SQL Server实例名
- $backupFolder = "C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup" # 备份文件存放目录
- $dataFolder = "D:\Data" # 数据文件存放目录
- # 创建目录(如果不存在)
- if (!(Test-Path -Path $backupFolder)) {
- New-Item -ItemType Directory -Path $backupFolder | Out-Null
- }
- # 获取所有.bak文件
- $bakFiles = Get-ChildItem -Path $backupFolder -Filter "*.bak"
- foreach ($file in $bakFiles) {
- try {
- $dbName = [System.IO.Path]::GetFileNameWithoutExtension($file.Name)
- $backupFile = $file.FullName
-
- # 首先获取备份文件中的逻辑文件名
- $getFileListSQL = @"
- RESTORE FILELISTONLY FROM DISK = N'$backupFile'
- "@
- $fileList = Invoke-Sqlcmd -ServerInstance $sqlServer -Query $getFileListSQL
-
- # 提取逻辑文件名(假设第一个是数据文件，第二个是日志文件)
- $dataLogicalName = $fileList[0].LogicalName
- $logLogicalName = $fileList[1].LogicalName
-
- # 构建还原语句
- $restoreSQL = @"
- RESTORE DATABASE [$dbName]
- FROM DISK = N'$backupFile'
- WITH FILE = 1,
- MOVE N'$dataLogicalName' TO N'$dataFolder$dbName.mdf',
- MOVE N'$logLogicalName' TO N'$dataFolder${dbName}_log.ldf',
- NOUNLOAD, REPLACE, STATS = 5;
- "@
-
- Write-Host "正在还原数据库: $dbName ..."
- Invoke-Sqlcmd -ServerInstance $sqlServer -Query $restoreSQL -QueryTimeout 0
- Write-Host "成功还原数据库: $dbName" -ForegroundColor Green
-
- # 设置数据库为简单恢复模式(可选)
- $setRecoverySQL = "ALTER DATABASE [$dbName] SET RECOVERY SIMPLE WITH NO_WAIT"
- Invoke-Sqlcmd -ServerInstance $sqlServer -Query $setRecoverySQL
- }
- catch {
- Write-Host "还原数据库 $dbName 时出错: $_" -ForegroundColor Red
- }
- }
- Write-Host "所有数据库还原操作完成"
| 
