前言
最近公司伺服器到期，需要進行資料遷移，而資料庫屬於多而繁瑣，通過圖形化介面一個一個備份所需時間成本很大，所以想著寫一個sql指令碼來執行。
開始
-
資料庫單個備份
-
資料庫批量備份
-
資料庫還原
-
資料庫還原報錯問題記錄
-
總結
1.資料庫單個備份
圖形化介面備份這裡就不展示了，可以自信百度，下面直接貼程式碼
USE MASTER
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE BackupDataProc
go
create proc BackupDataProc
@FullName Varchar(200)--入參（資料庫名）
as
begin
Declare @FileFlag varchar(50)
Set @FileFlag='C:\myfile\database\'+@FullName+'.bak'--備份到哪個路徑（C:\myfile\database\）根據自己需求來定
BackUp DataBase @FullName To Disk=@FileFlag with init--核心程式碼
end
exec BackupDataProc xxx
執行成功後便會生成一個.bak檔案到指定資料夾中，如圖
2.資料庫批量備份（時間有點長，請等待）
USE MASTER
if exists(SELECT * FROM sys.types WHERE name = 'AllDatabasesNameType')
drop type AllDatabasesNameType
go
create type AllDatabasesNameType as table--自定義表型別用於儲存資料庫名稱
(
rowNum int ,
name nvarchar(60),
filename nvarchar(300)
)
go
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BachBackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE BachBackupDataProc
go
create proc BachBackupDataProc
@filePath nvarchar(300)--入參，備份時的目標路徑
as
begin
Declare @AllDatabasesName as AllDatabasesNameType --用於儲存系統中的資料庫名
Declare @i int --迴圈變數
insert into @AllDatabasesName(name,filename,rowNum) select name,filename,ROW_NUMBER() over(order by name) as rowNum from sysdatabases where name not in('master','tempdb','model','msdb') --賦值
set @i =1
--迴圈備份資料庫
while @i <= (select COUNT(*) from @AllDatabasesName)
begin
Declare @FileFlag varchar(500)
Declare @FullName varchar(50)
Select @FullName =name from @AllDatabasesName where rowNum = @i
Set @FileFlag=@filePath+@FullName+'.bak'
BackUp DataBase @FullName To Disk=@FileFlag with init
set @i = @i + 1
end
end
exec BachBackupDataProc 'C:\myfile\database\'
執行結果效果如下圖：
3.資料庫還原
IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[ReductionProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE ReductionProc
go
create proc ReductionProc
@Name nvarchar(200)--入參 資料庫名稱
as
begin
Declare @DiskName nvarchar(500)
Declare @FileLogName nvarchar(100)
Declare @FileFlagData nvarchar(500)
Declare @FileFlagLog nvarchar(500)
Set @FileLogName = @Name + '_log'
Set @DiskName = 'C:\myfile\database\'+@Name+'.bak' ---（源）備份檔案路徑
Set @FileFlagData='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@Name+'.mdf'---（目標）指定資料檔案路徑
Set @FileFlagLog='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@FileLogName+'.ldf'---目標）指定日誌檔案路徑
RESTORE DATABASE @Name --為待還原庫名
FROM DISK = @DiskName ---備份檔名
WITH MOVE @Name TO @FileFlagData, ---指定資料檔案路徑
MOVE @FileLogName TO @FileFlagLog, ---指定日誌檔案路徑
STATS = 10, REPLACE
end
go
exec ReductionProc xxx
執行後便能還原庫（我是拿這三個庫做測試，截的圖可能沒什麼變化，你們可以嘗試下）
4.資料庫還原報錯問題記錄
當然還原的過程可能會遇到一些問題，比如：
1.版本不一樣
2.SQL Sql 邏輯檔案'XXXXX ' 不是資料庫'YYY'的一部分。請使用 RESTORE FILELISTONLY 來列出邏輯檔名。
版本的話我試過了，高版本可以向下相容，但是低版本不能向上相容，可以統一版本來解決（如有更好的解決方案歡迎打擾）
第二個問題呢就是指令碼中‘MOVE’ 他只能跟邏輯名，而有些資料庫的邏輯名並不是資料庫名稱，所以需要替換一下，
下面是查詢資料庫邏輯名的sql語句：
USE MASTER
restore filelistonly from disk='D:\sql201database\Sence.bak'--根據自己的需求要變更路徑
如圖，
對於這些邏輯名與資料庫名稱不一致的情況可以單獨拿出來重新執行一下即可：
USE MASTER --這裡注意要使用MASTER，以免出現待還原庫被佔用的情況
RESTORE DATABASE Sence --為待還原庫名
FROM DISK = 'D:\sql201database\Sence.bak' ---備份檔名
WITH MOVE 'Sence_Guangxi' TO 'D:\Database\Data\Sence.mdf', ---指定資料檔案路徑
MOVE 'Sence_Guangxi_log' TO 'D:\Database\Data\Sence_log.ldf', ---指定日誌檔案路徑
STATS = 10, REPLACE
GO
5.總結
資料是無價的，對資料庫操作時備份是必須的。
資料是無價的，對資料庫操作時備份是必須的。
資料是無價的，對資料庫操作時備份是必須的。（重要的事說三遍）
初次發文，請斧正！！！