沒錢做備份也好，花心血練習，以後還到事就【大丈夫】 囉。
只有一台SQL SERVER主機，這樣設計
每天做一次資料庫完整備份，並複製到另一台主機做檔案備份 。
每天每30分鐘做一次交易備份， 並複製到另一台主機做檔案備份 。
機器掛掉時 ，找一台相同版本DB的主機，把最後一次完整DB備份還原，依序將交易日誌還原，運氣好的話，RPO ( Recovery Point Objective )可忍受資料遺失的時間長度為30分鐘或1小時。
就看系統重不重要，急迫性如何了。
備份指令
use master
go
--step 1: 每天完整的備份資料
declare @fileName = 'MyDB_' + right( '00'+ cast(datepart(day,getdate()) as nvarchar(10)),2) +'.bak'
BACKUP DATABASE MyDB TO DISK = @fileName
WITH COMPRESSION ,NOFORMAT, INIT, NAME = N'完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--step 2:每天完整備份後的第一次LOG備份要為INIT, 且LOG命名固定
declare @logname nvarchar(50)
set @logname =(select 'D:\DBLog\MyDB_'+ convert(char(8),getdate(),112)+'_first.TRN')
BACKUP LOG MyDB TO DISK = @logname WITH COMPRESSION,INIT
GO
--step 3:每小時備份LOG
set @logname =(select 'D:\DBLog\MyDB_'+ convert(char(8),getdate(),112)+'_'+ right('00'+cast(datepart(hour,getdate()) as nvarchar(4)),2) +
case when datepart(minute,getdate()) <=30 then '00' else '30' end +'.TRN')
BACKUP LOG MyDB TO DISK = @logname WITH COMPRESSION
GO
go
--step 1: 每天完整的備份資料
declare @fileName = 'MyDB_' + right( '00'+ cast(datepart(day,getdate()) as nvarchar(10)),2) +'.bak'
BACKUP DATABASE MyDB TO DISK = @fileName
WITH COMPRESSION ,NOFORMAT, INIT, NAME = N'完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--step 2:每天完整備份後的第一次LOG備份要為INIT, 且LOG命名固定
declare @logname nvarchar(50)
set @logname =(select 'D:\DBLog\MyDB_'+ convert(char(8),getdate(),112)+'_first.TRN')
BACKUP LOG MyDB TO DISK = @logname WITH COMPRESSION,INIT
GO
--step 3:每小時備份LOG
set @logname =(select 'D:\DBLog\MyDB_'+ convert(char(8),getdate(),112)+'_'+ right('00'+cast(datepart(hour,getdate()) as nvarchar(4)),2) +
case when datepart(minute,getdate()) <=30 then '00' else '30' end +'.TRN')
BACKUP LOG MyDB TO DISK = @logname WITH COMPRESSION
GO
還原指令
--獨佔
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
--Restore到上一個完整的備份
RESTORE DATABASE MyDB FROM DISK = 'C:\temp\DB\MyDB_07.BAK' WITH NORECOVERY, REPLACE
GO
--依序還原前一日完整DB備份後的第一個TRN檔，以WITH NORECOVERY 才可逐檔還原
RESTORE LOG MyDB FROM DISK = 'C:\temp\Log\MyDB_20160407_first.TRN' WITH NORECOVERY
GO
--產出LOG還原指令
declare @stm datetime,@etm datetime,@tm datetime,@sql nvarchar(200)
set @stm='2016/04/07 22:00:00'
set @etm = '20104/08 19:00:00'
while @stm <= @etm
begin
set @sql =(select 'RESTORE LOG MyDB FROM DISK = ''C:\temp\Log\MyDB_' +
convert(char(8),@stm,112)+'_'+ right('00'+cast(datepart(hour,@stm) as nvarchar(4)),2) +
right('00'+ cast(datepart(minute,@stm) as nvarchar(4)) ,2) +'.TRN'' WITH NORECOVERY ;' )
set @stm = dateadd(minute,30,@stm)
exec sp_executesql @sql
end
--最後強制回復
RESTORE DATABASE MyDB WITH RECOVERY,REPLACE
--切為多人模式
ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
--Restore到上一個完整的備份
RESTORE DATABASE MyDB FROM DISK = 'C:\temp\DB\MyDB_07.BAK' WITH NORECOVERY, REPLACE
GO
--依序還原前一日完整DB備份後的第一個TRN檔，以WITH NORECOVERY 才可逐檔還原
RESTORE LOG MyDB FROM DISK = 'C:\temp\Log\MyDB_20160407_first.TRN' WITH NORECOVERY
GO
--產出LOG還原指令
declare @stm datetime,@etm datetime,@tm datetime,@sql nvarchar(200)
set @stm='2016/04/07 22:00:00'
set @etm = '20104/08 19:00:00'
while @stm <= @etm
begin
set @sql =(select 'RESTORE LOG MyDB FROM DISK = ''C:\temp\Log\MyDB_' +
convert(char(8),@stm,112)+'_'+ right('00'+cast(datepart(hour,@stm) as nvarchar(4)),2) +
right('00'+ cast(datepart(minute,@stm) as nvarchar(4)) ,2) +'.TRN'' WITH NORECOVERY ;' )
set @stm = dateadd(minute,30,@stm)
exec sp_executesql @sql
end
--最後強制回復
RESTORE DATABASE MyDB WITH RECOVERY,REPLACE
--切為多人模式
ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
go
