建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
SQL Server是Microsoft公司開(kāi)發(fā)的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS），許多企業(yè)和組織使用SQL Server數(shù)據(jù)庫(kù)存儲(chǔ)重要數(shù)據(jù)和信息。為了保護(hù)這些數(shù)據(jù)免受數(shù)據(jù)損失、硬件故障和其他意外事件的影響，必須定期備份SQL Server數(shù)據(jù)庫(kù)。本文將在詳細(xì)講解SQL Server數(shù)據(jù)庫(kù)備份操作的基礎(chǔ)上，提供一些有關(guān)如何在備份數(shù)據(jù)庫(kù)方面采取更佳實(shí)踐的建議。
一. SQL Server數(shù)據(jù)庫(kù)備份概述
SQL Server數(shù)據(jù)庫(kù)備份是指復(fù)制數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)和對(duì)象并存儲(chǔ)在另一個(gè)位置以進(jìn)行恢復(fù)。 要備份SQL Server數(shù)據(jù)庫(kù)，必須使用SQL Server Management Studio或Transact-SQL（T-SQL）命令的一個(gè)或多個(gè)命令，具體取決于您的選擇和需要。數(shù)據(jù)庫(kù)備份的頻率應(yīng)與業(yè)務(wù)要求和數(shù)據(jù)重要性相關(guān)，一般建議每天備份一次、增量備份及日志備份，太高頻可能會(huì)增加服務(wù)器負(fù)擔(dān)。SQL Server數(shù)據(jù)庫(kù)備份通常存儲(chǔ)在磁盤(pán)、網(wǎng)絡(luò)共享或磁帶上，以便將數(shù)據(jù)庫(kù)恢復(fù)到在發(fā)生數(shù)據(jù)損壞或其他錯(cuò)誤時(shí)的先前狀態(tài)。數(shù)據(jù)庫(kù)備份可以進(jìn)行完整備份、差異備份和日志備份。下面我們來(lái)詳細(xì)介紹一下這幾種備份方式。
二. SQL Server完整備份
完整備份包括整個(gè)數(shù)據(jù)庫(kù)的完整備份，包括用戶(hù)數(shù)據(jù)、元數(shù)據(jù)和所有組件的備份。完整備份需要占用的存儲(chǔ)空間較大，尤其是對(duì)于大型數(shù)據(jù)庫(kù)，備份時(shí)間和恢復(fù)時(shí)間也會(huì)很長(zhǎng)。完整備份通常是創(chuàng)建備份的開(kāi)始，然后使用增量備份和/或事務(wù)日志備份來(lái)進(jìn)一步保護(hù)數(shù)據(jù)。完整備份是最基本的備份類(lèi)型，也是最常見(jiàn)的備份方式。
三. SQL Server差異備份
差異備份是基于最近的完整備份創(chuàng)建的備份。它包含自上次完整備份以來(lái)增加的所有更改，這些更改都是在差異備份進(jìn)行時(shí)發(fā)生的。這樣做可以解決完整備份時(shí)間過(guò)長(zhǎng)和備份數(shù)據(jù)過(guò)程帶來(lái)的復(fù)雜性問(wèn)題。 然而，差異備份也有缺點(diǎn)，主要是它需要更多的存儲(chǔ)空間和更長(zhǎng)的恢復(fù)時(shí)間，因?yàn)椴町悅浞葜话ㄗ陨洗瓮暾麄浞菀詠?lái)的更改。
四. SQL Server日志備份
日志備份是對(duì)事務(wù)日志的備份。它只包含自上次備份以來(lái)的所有事務(wù)日志更改，這使得日志備份相對(duì)較小，而且只需花費(fèi)較少的時(shí)間。這種備份類(lèi)型通常是為了在災(zāi)難發(fā)生后，快速而可靠地還原數(shù)據(jù)而保留的，修正系統(tǒng)故障或人為的數(shù)據(jù)操作而作為最后備手段。日志備份不需要完整備份或差異備份作為前提條件，它們不同之處在于，日志備份是只備份事務(wù)日志，而不是備份整個(gè)數(shù)據(jù)庫(kù)。
五. 備份數(shù)據(jù)庫(kù)的更佳實(shí)踐
以下是幾個(gè)備份數(shù)據(jù)庫(kù)的更佳實(shí)踐：
1. 定期備份：根據(jù)業(yè)務(wù)重要性，定期備份的頻率應(yīng)該是每天都要備份一次，每周、每月進(jìn)行完整備份。
2. 將備份文件存儲(chǔ)在多個(gè)位置：即使您的備份位置很安全，也應(yīng)該將備份文件主存儲(chǔ)位置保存在多個(gè)位置。
3. 定期測(cè)試備份：確保您的備份文件是可以恢復(fù)的，定期測(cè)試備份文件中數(shù)據(jù)的完整性和有效性。
4. 關(guān)閉數(shù)據(jù)庫(kù)以進(jìn)行完整備份：為了確保在完整備份期間沒(méi)有數(shù)據(jù)丟失，應(yīng)該將數(shù)據(jù)庫(kù)關(guān)閉，然后進(jìn)行完整備份，然后再重新啟動(dòng)數(shù)據(jù)。
5. 根據(jù)需要進(jìn)行增量備份：差異備份可以在完整備份之間提供額外的保護(hù)，它可以減少恢復(fù)時(shí)間，增量備份可以只備份修改過(guò)的數(shù)據(jù)，減少了備份時(shí)間。
6. 不保留太多備份：不應(yīng)保留過(guò)多的備份文件，否則會(huì)占用太多磁盤(pán)空間。
表面上來(lái)看，備份SQL Server數(shù)據(jù)庫(kù)貌似是一個(gè)簡(jiǎn)單的過(guò)程，但在實(shí)踐中，一些看似不重要的細(xì)節(jié)卻可能導(dǎo)致數(shù)據(jù)庫(kù)的損壞。 在這篇文章中，我們介紹了SQL Server數(shù)據(jù)庫(kù)的3種備份方式：完整備份，差異備份和日志備份；并提供了幾個(gè)更佳實(shí)踐，以確保您有效的備份SQL Server數(shù)據(jù)庫(kù)并確保您的數(shù)據(jù)安全。了解和遵循這些操作和建議，可以幫助您更好地保護(hù)數(shù)據(jù)并減少潛在的風(fēng)險(xiǎn)。
相關(guān)問(wèn)題拓展閱讀：
- 用SQL語(yǔ)句備份數(shù)據(jù)庫(kù)
- 怎么備份SQL Server 2023數(shù)據(jù)庫(kù)
用SQL語(yǔ)句備份數(shù)據(jù)庫(kù)
用SQL2023還原bak文件
1.右擊SQL Server 2023實(shí)例下的“數(shù)據(jù)庫(kù)”文件夾。就是master等數(shù)據(jù)庫(kù)上一級(jí)的那個(gè)圖標(biāo)。選擇“所有任務(wù)”，“還原數(shù)據(jù)庫(kù)”
2.在“還原為數(shù)據(jù)庫(kù)”中填上你希望恢復(fù)的數(shù)據(jù)庫(kù)名字。這個(gè)名字應(yīng)該與你的源碼中使用的數(shù)據(jù)庫(kù)名字一致。
3.在彈出的對(duì)話框中，選“從設(shè)備”
4.點(diǎn)擊“選擇設(shè)備”
5.點(diǎn)擊“添鏈橘加”
6.點(diǎn)擊“文件名”文本框右側(cè)的“…”按鈕，選中你的“.BAK”文件，并點(diǎn)擊確定回到“選擇還原設(shè)備”對(duì)話框。
7.點(diǎn)擊確定回到“還原數(shù)據(jù)庫(kù)”對(duì)話框。
8.點(diǎn)擊“選項(xiàng)”選項(xiàng)卡
9.將所有“移至物理文件名”下面的路徑，改為你想還原后的將數(shù)據(jù)庫(kù)文件保存到的路徑。如果你不希望改變，可以直接點(diǎn)擊確定。這時(shí)便肢弊恢復(fù)歷喚族成功了。
很不錯(cuò)!我今天終于把.bak搞定了,這里有個(gè)要注意的地方就是選項(xiàng)中的“移至物理文件名”下面的路徑,這個(gè)路徑一定要修改哦,不然會(huì)出現(xiàn)錯(cuò)誤
利用T-SQL語(yǔ)句,實(shí)現(xiàn)數(shù)據(jù)庫(kù)的備份和還原的功能
體現(xiàn)了SQL Server中的四個(gè)知識(shí)點(diǎn)：
1． 獲取SQL Server服務(wù)器上的默認(rèn)目錄
2． 備份SQL語(yǔ)句的使用
3． 恢復(fù)SQL語(yǔ)句的使用，同時(shí)考慮了強(qiáng)制恢復(fù)時(shí)關(guān)閉其他用戶(hù)進(jìn)程的處理
4． 作業(yè)創(chuàng)建SQL語(yǔ)句的使用
/*1.–得到數(shù)坦喊灶據(jù)庫(kù)的文件目錄
@dbname 指定要取得目錄的數(shù)據(jù)庫(kù)名
如果指定的數(shù)據(jù)不存在,返回安裝SQL時(shí)設(shè)置的默認(rèn)數(shù)據(jù)目錄
如果指定NULL,則返回默認(rèn)的SQL備份目錄名
*/
/*–調(diào)用示例
select 數(shù)據(jù)庫(kù)文件目錄=dbo.f_getdbpath(’tempdb’)
,=dbo.f_getdbpath(’’)
,=dbo.f_getdbpath(null)
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function .
GO
create function f_getdbpath(@dbname sysname)
returns nvarchar(260)
as
begin
declare @re nvarchar(260)
if @dbname is null or db_id(@dbname) is null
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=’master’
else
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname
if @dbname is null
set @re=reverse(substring(@re,charindex(’＼’,@re)+5,260))+’BACKUP’
else
set @re=reverse(substring(@re,charindex(’＼’,@re),260))
return(@re)
end
go
/*2.–備份數(shù)據(jù)庫(kù)
*/
/*–調(diào)用示例
–備份當(dāng)前數(shù)據(jù)庫(kù)
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_db.bak’
–差異備份當(dāng)前讓扮數(shù)據(jù)庫(kù)
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_df.bak’,@bktype=’DF’
–備份當(dāng)前數(shù)據(jù)庫(kù)日志
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_log.bak’,@bktype=’LOG’
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.
’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure .
GO
create proc p_backupdb
@dbname sysname=’’, –要備份的數(shù)據(jù)庫(kù)名稱(chēng),不指定則備份當(dāng)前數(shù)據(jù)庫(kù)
@bkpath nvarchar(260)=’’, –備份文件的存放目錄,不指定則使用SQL默認(rèn)的備份目錄
@bkfname nvarchar(260)=’’, –備份文件名,文件名中能用＼DBNAME＼代表數(shù)據(jù)庫(kù)名,＼DATE＼代表日期,＼TIME＼代表時(shí)間
@bktype nvarchar(10)=’DB’, –備份類(lèi)型:’DB’備份數(shù)據(jù)庫(kù),’DF’ 差異滲碼備份,’LOG’ 日志備份
@appendfile bit=1 –追加/覆蓋備份文件
as
declare @sql varchar(8000)
if isnull(@dbname,’’)=’’ set @dbname=db_name()
if isnull(@bkpath,’’)=’’ set @bkpath=dbo.f_getdbpath(null)
if isnull(@bkfname,’’)=’’ set @bkfname=’＼DBNAME＼_＼DATE＼_＼TIME＼.BAK’
set @bkfname=replace(replace(replace(@bkfname,’＼DBNAME＼’,@dbname)
,’＼DATE＼’,convert(varchar,getdate(),112))
,’＼TIME＼’,replace(convert(varchar,getdate(),108),’:’,’’))
set @sql=’backup ’+case @bktype when ’LOG’ then ’log ’ else ’database ’ end +@dbname
+’ to disk=’’’+@bkpath+@bkfname
+’’’ with ’+case @bktype when ’DF’ then ’DIFFERENTIAL,’ else ’’ end
+case @appendfile when 1 then ’NOINIT’ else ’INIT’ end
print @sql
exec(@sql)
go
/*3.–恢復(fù)數(shù)據(jù)庫(kù)
*/
/*–調(diào)用示例
–完整恢復(fù)數(shù)據(jù)庫(kù)
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’
–差異備份恢復(fù)
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’,@retype=’DBNOR’
exec p_backupdb @bkfile=’c:＼db__df.bak’,@dbname=’db’,@retype=’DF’
–日志備份恢復(fù)
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’,@retype=’DBNOR’
exec p_backupdb @bkfile=’c:＼db__log.bak’,@dbname=’db’,@retype=’LOG’
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.
’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure .
GO
create proc p_RestoreDb
@bkfile nvarchar(1000), –定義要恢復(fù)的備份文件名
@dbname sysname=’’, –定義恢復(fù)后的數(shù)據(jù)庫(kù)名,默認(rèn)為備份的文件名
@dbpath nvarchar(260)=’’, –恢復(fù)后的數(shù)據(jù)庫(kù)存放目錄,不指定則為SQL的默認(rèn)數(shù)據(jù)目錄
@retype nvarchar(10)=’DB’, –恢復(fù)類(lèi)型:’DB’完事恢復(fù)數(shù)據(jù)庫(kù),’DBNOR’ 為差異恢復(fù),日志恢復(fù)進(jìn)行完整恢復(fù),’DF’ 差異備份的恢復(fù),’LOG’ 日志恢復(fù)
@filenumber int=1, –恢復(fù)的文件號(hào)
@overexist bit=1, –是否覆蓋已存在的數(shù)據(jù)庫(kù),僅@retype為
@killuser bit=1 –是否關(guān)閉用戶(hù)使用進(jìn)程,僅@overexist=1時(shí)有效
as
declare @sql varchar(8000)
–得到恢復(fù)后的數(shù)據(jù)庫(kù)名
if isnull(@dbname,’’)=’’
select @sql=reverse(@bkfile)
,@sql=case when charindex(’.’,@sql)=0 then @sql
else substring(@sql,charindex(’.’,@sql)+1,1000) end
,@sql=case when charindex(’＼’,@sql)=0 then @sql
else left(@sql,charindex(’＼’,@sql)-1) end
,@dbname=reverse(@sql)
–得到恢復(fù)后的數(shù)據(jù)庫(kù)存放目錄
if isnull(@dbpath,’’)=’’ set @dbpath=dbo.f_getdbpath(’’)
–生成數(shù)據(jù)庫(kù)恢復(fù)語(yǔ)句
set @sql=’restore ’+case @retype when ’LOG’ then ’log ’ else ’database ’ end+@dbname
+’ from disk=’’’+@bkfile+’’’’
+’ with file=’+cast(@filenumber as varchar)
+case when @overexist=1 and @retype in(’DB’,’DBNOR’) then ’,replace’ else ’’ end
+case @retype when ’DBNOR’ then ’,NORECOVERY’ else ’,RECOVERY’ end
print @sql
–添加移動(dòng)邏輯文件的處理
if @retype=’DB’ or @retype=’DBNOR’
begin
–從備份文件中獲取邏輯文件名
declare @lfn nvarchar(128),@tp char(1),@i int
–創(chuàng)建臨時(shí)表,保存獲取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
–從備份文件中獲取信息
insert into #tb exec(’restore filelistonly from disk=’’’+@bkfile+’’’’)
declare #f cursor for select ln,tp from #tb
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+’,move ’’’+@lfn+’’’ to ’’’+@dbpath+@dbname+cast(@i as varchar)
+case @tp when ’D’ then ’.mdf’’’ else ’.ldf’’’ end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
end
–關(guān)閉用戶(hù)進(jìn)程處理
if @overexist=1 and @killuser=1
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec(’kill ’+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
end
–恢復(fù)數(shù)據(jù)庫(kù)
exec(@sql)
go
/*4.–創(chuàng)建作業(yè)
*/
/*–調(diào)用示例
–每月執(zhí)行的作業(yè)
exec p_createjob @jobname=’mm’,@sql=’select * from syscolumns’,@freqtype=’month’
–每周執(zhí)行的作業(yè)
exec p_createjob @jobname=’ww’,@sql=’select * from syscolumns’,@freqtype=’week’
–每日?qǐng)?zhí)行的作業(yè)
exec p_createjob @jobname=’a’,@sql=’select * from syscolumns’
–每日?qǐng)?zhí)行的作業(yè),每天隔4小時(shí)重復(fù)的作業(yè)
exec p_createjob @jobname=’b’,@sql=’select * from syscolumns’,@fsinterval=4
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.
’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure .
GO
create proc p_createjob
@jobname varchar(100), –作業(yè)名稱(chēng)
@sql varchar(8000), –要執(zhí)行的命令
@dbname sysname=’’, –默認(rèn)為當(dāng)前的數(shù)據(jù)庫(kù)名
@freqtype varchar(6)=’day’, –時(shí)間周期,month 月,week 周,day 日
@fsinterval int=1, –相對(duì)于每日的重復(fù)次數(shù)
@time int=開(kāi)始執(zhí)行時(shí)間,對(duì)于重復(fù)執(zhí)行的作業(yè),將從0點(diǎn)到23:59分
as
if isnull(@dbname,’’)=’’ set @dbname=db_name()
–創(chuàng)建作業(yè)
exec msdb..sp_add_job @job_name=@jobname
–創(chuàng)建作業(yè)步驟
exec msdb..sp_add_jobstep @job_name=@jobname,
@step_name = ’數(shù)據(jù)處理’,
@subsystem = ’TSQL’,
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, –重試次數(shù)
@retry_interval = 5 –重試間隔
–創(chuàng)建調(diào)度
declare @ftype int,@fstype int,@ffactor int
select @ftype=case @freqtype when ’day’ then 4
when ’week’ then 8
when ’month’ then 16 end
,@fstype=case @fsinterval when 1 then 0 else 8 end
if @fsinterval1 set @time=0
set @ffactor=case @freqtype when ’day’ then 0 else 1 end
EXEC msdb..sp_add_jobschedule @job_name=@jobname,
@name = ’時(shí)間安排’,
@freq_type=@ftype , –每天,8 每周,16 每月
@freq_interval=1, –重復(fù)執(zhí)行次數(shù)
@freq_subday_type=@fstype, –是否重復(fù)執(zhí)行
@freq_subday_interval=@fsinterval, –重復(fù)周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time –下午17:00:00分執(zhí)行
go
/*–應(yīng)用案例–備份方案:
完整備份（每個(gè)星期天一次）+差異備份（每天備份一次）+日志備份（每2小時(shí)備份一次）
調(diào)用上面的存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)
–*/
declare @sql varchar(8000)
–完整備份（每個(gè)星期天一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫(kù)名’’’
exec p_createjob @jobname=’每周備份’,@sql,@freqtype=’week’
–差異備份（每天備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫(kù)名’’,@bktype=’DF’’
exec p_createjob @jobname=’每天差異備份’,@sql,@freqtype=’day’
–日志備份（每2小時(shí)備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫(kù)名’’,@bktype=’LOG’’
exec p_createjob @jobname=’每2小時(shí)日志備份’,@sql,@freqtype=’day’,@fsinterval=2
/*–應(yīng)用案例2
生產(chǎn)數(shù)據(jù)核心庫(kù)：PRODUCE
備份方案如下：
1.設(shè)置三個(gè)作業(yè),分別對(duì)PRODUCE庫(kù)進(jìn)行每日備份,每周備份,每月備份
2.新建三個(gè)新庫(kù),分別命名為:每日備份,每周備份,每月備份
3.建立三個(gè)作業(yè)，分別把三個(gè)備份庫(kù)還原到以上的三個(gè)新庫(kù)。
目的:當(dāng)用戶(hù)在produce庫(kù)中有所有的數(shù)據(jù)丟失時(shí),均能從上面的三個(gè)備份庫(kù)中導(dǎo)入相應(yīng)的TABLE數(shù)據(jù)。
–*/
declare @sql varchar(8000)
–1.建立每月備份和生成月備份數(shù)據(jù)庫(kù)的作業(yè),每月每1天下午16:40分進(jìn)行:
set @sql=’
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=’’PRODUCE_’’+convert(varchar(10),getdate(),112)+’’_m.bak’’
set @path=dbo.f_getdbpath(null)+@fname
–備份
exec p_backupdb @dbname=’’PRODUCE’’,@bkfname=@fname
–根據(jù)備份生成每月新庫(kù)
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_月’’
–為周數(shù)據(jù)庫(kù)恢復(fù)準(zhǔn)備基礎(chǔ)數(shù)據(jù)庫(kù)
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DBNOR’’
–為日數(shù)據(jù)庫(kù)恢復(fù)準(zhǔn)備基礎(chǔ)數(shù)據(jù)庫(kù)
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’DBNOR’’
’
exec p_createjob @jobname=’每月備份’,@sql,@freqtype=’month’,@time=
–2.建立每周差異備份和生成周備份數(shù)據(jù)庫(kù)的作業(yè),每周日下午17:00分進(jìn)行:
set @sql=’
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=’’PRODUCE_’’+convert(varchar(10),getdate(),112)+’’_w.bak’’
set @path=dbo.f_getdbpath(null)+@fname
–差異備份
exec p_backupdb @dbname=’’PRODUCE’’,@bkfname=@fname,@bktype=’’DF’’
–差異恢復(fù)周數(shù)據(jù)庫(kù)
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DF’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’week’,@time=
–3.建立每日日志備份和生成日備份數(shù)據(jù)庫(kù)的作業(yè),每周日下午17:15分進(jìn)行:
set @sql=’
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=’’PRODUCE_’’+convert(varchar(10),getdate(),112)+’’_l.bak’’
set @path=dbo.f_getdbpath(null)+@fname
–日志備份
exec p_backupdb @dbname=’’PRODUCE’’,@bkfname=@fname,@bktype=’’LOG’’
–日志恢復(fù)日數(shù)據(jù)庫(kù)
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’LOG’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’day’,@time=171500
這個(gè)說(shuō)的對(duì)！
/*1.–得到數(shù)據(jù)庫(kù)的文件目錄
@dbname 指定要取得目錄的數(shù)據(jù)庫(kù)名
如果指定的數(shù)據(jù)不存在,返回安裝SQL時(shí)設(shè)置的默認(rèn)數(shù)據(jù)目錄
如果指定NULL,則返回默認(rèn)的SQL備份目錄名
*/
/*–調(diào)用示例
select 數(shù)據(jù)庫(kù)文件目錄=dbo.f_getdbpath(’tempdb’)
,=dbo.f_getdbpath(’’)
,=dbo.f_getdbpath(null)
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function .
GO
create function f_getdbpath(@dbname sysname)
returns nvarchar(260)
as
begin
declare @re nvarchar(260)
if @dbname is null or db_id(@dbname) is null
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=’master’
else
select @re=rtrim(reverse(filename)) from master..sysdatabases where name=@dbname
if @dbname is null
set @re=reverse(substring(@re,charindex(’＼’,@re)+5,260))+’BACKUP’
else
set @re=reverse(substring(@re,charindex(’＼’,@re),260))
return(@re)
end
go
/*2.–備份數(shù)據(jù)庫(kù)
*/
/*–調(diào)用示例
–備份當(dāng)前數(shù)據(jù)庫(kù)
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_db.bak’
–差異備份當(dāng)前數(shù)據(jù)庫(kù)
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_df.bak’,@bktype=’DF’
–備份當(dāng)前數(shù)據(jù)庫(kù)日志
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_log.bak’,@bktype=’LOG’
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.
’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure .
GO
create proc p_backupdb
@dbname sysname=’’, –要備份的數(shù)據(jù)庫(kù)名稱(chēng),不指定則備份當(dāng)前數(shù)據(jù)庫(kù)
@bkpath nvarchar(260)=’’, –備份文件的存放目錄,不指定則使用SQL默認(rèn)的備份目錄
@bkfname nvarchar(260)=’’, –備份文件名,文件名中能用＼DBNAME＼代表數(shù)據(jù)庫(kù)名,＼DATE＼代表日期,＼TIME＼代表時(shí)間
@bktype nvarchar(10)=’磨純DB’, –備份類(lèi)型:’瞎茄咐DB’備份數(shù)據(jù)庫(kù),’DF’ 差異備份,’LOG’ 日志備份
@appendfile bit=1 –追加/覆蓋備份文件
as
declare @sql varchar(8000)
if isnull(@dbname,’’)=’’ set @dbname=db_name()
if isnull(@bkpath,’’)=’’ set @bkpath=dbo.f_getdbpath(null)
if isnull(@bkfname,’’)=’’ set @bkfname=’＼納源DBNAME＼_＼DATE＼_＼TIME＼.BAK’
set @bkfname=replace(replace(replace(@bkfname,’＼DBNAME＼’,@dbname)
,’＼DATE＼’,convert(varchar,getdate(),112))
,’＼TIME＼’,replace(convert(varchar,getdate(),108),’:’,’’))
set @sql=’backup ’+case @bktype when ’LOG’ then ’log ’ else ’database ’ end +@dbname
+’ to disk=’’’+@bkpath+@bkfname
+’’’ with ’+case @bktype when ’DF’ then ’DIFFERENTIAL,’ else ’’ end
+case @appendfile when 1 then ’NOINIT’ else ’INIT’ end
print @sql
exec(@sql)
go
/*3.–恢復(fù)數(shù)據(jù)庫(kù)
*/
/*–調(diào)用示例
–完整恢復(fù)數(shù)據(jù)庫(kù)
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’
–差異備份恢復(fù)
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’,@retype=’DBNOR’
exec p_backupdb @bkfile=’c:＼db__df.bak’,@dbname=’db’,@retype=’DF’
–日志備份恢復(fù)
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’,@retype=’DBNOR’
exec p_backupdb @bkfile=’c:＼db__log.bak’,@dbname=’db’,@retype=’LOG’
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.
’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure .
GO
create proc p_RestoreDb
@bkfile nvarchar(1000), –定義要恢復(fù)的備份文件名
@dbname sysname=’’, –定義恢復(fù)后的數(shù)據(jù)庫(kù)名,默認(rèn)為備份的文件名
@dbpath nvarchar(260)=’’, –恢復(fù)后的數(shù)據(jù)庫(kù)存放目錄,不指定則為SQL的默認(rèn)數(shù)據(jù)目錄
@retype nvarchar(10)=’DB’, –恢復(fù)類(lèi)型:’DB’完事恢復(fù)數(shù)據(jù)庫(kù),’DBNOR’ 為差異恢復(fù),日志恢復(fù)進(jìn)行完整恢復(fù),’DF’ 差異備份的恢復(fù),’LOG’ 日志恢復(fù)
@filenumber int=1, –恢復(fù)的文件號(hào)
@overexist bit=1, –是否覆蓋已存在的數(shù)據(jù)庫(kù),僅@retype為
@killuser bit=1 –是否關(guān)閉用戶(hù)使用進(jìn)程,僅@overexist=1時(shí)有效
as
declare @sql varchar(8000)
–得到恢復(fù)后的數(shù)據(jù)庫(kù)名
if isnull(@dbname,’’)=’’
select @sql=reverse(@bkfile)
,@sql=case when charindex(’.’,@sql)=0 then @sql
else substring(@sql,charindex(’.’,@sql)+1,1000) end
,@sql=case when charindex(’＼’,@sql)=0 then @sql
else left(@sql,charindex(’＼’,@sql)-1) end
,@dbname=reverse(@sql)
–得到恢復(fù)后的數(shù)據(jù)庫(kù)存放目錄
if isnull(@dbpath,’’)=’’ set @dbpath=dbo.f_getdbpath(’’)
–生成數(shù)據(jù)庫(kù)恢復(fù)語(yǔ)句
set @sql=’restore ’+case @retype when ’LOG’ then ’log ’ else ’database ’ end+@dbname
+’ from disk=’’’+@bkfile+’’’’
+’ with file=’+cast(@filenumber as varchar)
+case when @overexist=1 and @retype in(’DB’,’DBNOR’) then ’,replace’ else ’’ end
+case @retype when ’DBNOR’ then ’,NORECOVERY’ else ’,RECOVERY’ end
print @sql
–添加移動(dòng)邏輯文件的處理
if @retype=’DB’ or @retype=’DBNOR’
begin
–從備份文件中獲取邏輯文件名
declare @lfn nvarchar(128),@tp char(1),@i int
–創(chuàng)建臨時(shí)表,保存獲取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
–從備份文件中獲取信息
insert into #tb exec(’restore filelistonly from disk=’’’+@bkfile+’’’’)
declare #f cursor for select ln,tp from #tb
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+’,move ’’’+@lfn+’’’ to ’’’+@dbpath+@dbname+cast(@i as varchar)
+case @tp when ’D’ then ’.mdf’’’ else ’.ldf’’’ end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
end
–關(guān)閉用戶(hù)進(jìn)程處理
if @overexist=1 and @killuser=1
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec(’kill ’+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
end
–恢復(fù)數(shù)據(jù)庫(kù)
exec(@sql)
go
/*4.–創(chuàng)建作業(yè)
*/
/*–調(diào)用示例
–每月執(zhí)行的作業(yè)
exec p_createjob @jobname=’mm’,@sql=’select * from syscolumns’,@freqtype=’month’
–每周執(zhí)行的作業(yè)
exec p_createjob @jobname=’ww’,@sql=’select * from syscolumns’,@freqtype=’week’
–每日?qǐng)?zhí)行的作業(yè)
exec p_createjob @jobname=’a’,@sql=’select * from syscolumns’
–每日?qǐng)?zhí)行的作業(yè),每天隔4小時(shí)重復(fù)的作業(yè)
exec p_createjob @jobname=’b’,@sql=’select * from syscolumns’,@fsinterval=4
–*/
if exists (select * from dbo.sysobjects where id = object_id(N’.
’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure .
GO
create proc p_createjob
@jobname varchar(100), –作業(yè)名稱(chēng)
@sql varchar(8000), –要執(zhí)行的命令
@dbname sysname=’’, –默認(rèn)為當(dāng)前的數(shù)據(jù)庫(kù)名
@freqtype varchar(6)=’day’, –時(shí)間周期,month 月,week 周,day 日
@fsinterval int=1, –相對(duì)于每日的重復(fù)次數(shù)
@time int=開(kāi)始執(zhí)行時(shí)間,對(duì)于重復(fù)執(zhí)行的作業(yè),將從0點(diǎn)到23:59分
as
if isnull(@dbname,’’)=’’ set @dbname=db_name()
–創(chuàng)建作業(yè)
exec msdb..sp_add_job @job_name=@jobname
–創(chuàng)建作業(yè)步驟
exec msdb..sp_add_jobstep @job_name=@jobname,
@step_name = ’數(shù)據(jù)處理’,
@subsystem = ’TSQL’,
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, –重試次數(shù)
@retry_interval = 5 –重試間隔
–創(chuàng)建調(diào)度
declare @ftype int,@fstype int,@ffactor int
select @ftype=case @freqtype when ’day’ then 4
when ’week’ then 8
when ’month’ then 16 end
,@fstype=case @fsinterval when 1 then 0 else 8 end
if @fsinterval1 set @time=0
set @ffactor=case @freqtype when ’day’ then 0 else 1 end
EXEC msdb..sp_add_jobschedule @job_name=@jobname,
@name = ’時(shí)間安排’,
@freq_type=@ftype , –每天,8 每周,16 每月
@freq_interval=1, –重復(fù)執(zhí)行次數(shù)
@freq_subday_type=@fstype, –是否重復(fù)執(zhí)行
@freq_subday_interval=@fsinterval, –重復(fù)周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time –下午17:00:00分執(zhí)行
go
/*–應(yīng)用案例–備份方案:
完整備份（每個(gè)星期天一次）+差異備份（每天備份一次）+日志備份（每2小時(shí)備份一次）
調(diào)用上面的存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)
–*/
declare @sql varchar(8000)
–完整備份（每個(gè)星期天一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫(kù)名’’’
exec p_createjob @jobname=’每周備份’,@sql,@freqtype=’week’
–差異備份（每天備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫(kù)名’’,@bktype=’DF’’
exec p_createjob @jobname=’每天差異備份’,@sql,@freqtype=’day’
–日志備份（每2小時(shí)備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫(kù)名’’,@bktype=’LOG’’
exec p_createjob @jobname=’每2小時(shí)日志備份’,@sql,@freqtype=’day’,@fsinterval=2
/*–應(yīng)用案例2
生產(chǎn)數(shù)據(jù)核心庫(kù)：PRODUCE
備份方案如下：
1.設(shè)置三個(gè)作業(yè),分別對(duì)PRODUCE庫(kù)進(jìn)行每日備份,每周備份,每月備份
2.新建三個(gè)新庫(kù),分別命名為:每日備份,每周備份,每月備份
3.建立三個(gè)作業(yè)，分別把三個(gè)備份庫(kù)還原到以上的三個(gè)新庫(kù)。
目的:當(dāng)用戶(hù)在produce庫(kù)中有所有的數(shù)據(jù)丟失時(shí),均能從上面的三個(gè)備份庫(kù)中導(dǎo)入相應(yīng)的TABLE數(shù)據(jù)。
–*/
declare @sql varchar(8000)
–1.建立每月備份和生成月備份數(shù)據(jù)庫(kù)的作業(yè),每月每1天下午16:40分進(jìn)行:
set @sql=’
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=’’PRODUCE_’’+convert(varchar(10),getdate(),112)+’’_m.bak’’
set @path=dbo.f_getdbpath(null)+@fname
–備份
exec p_backupdb @dbname=’’PRODUCE’’,@bkfname=@fname
–根據(jù)備份生成每月新庫(kù)
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_月’’
–為周數(shù)據(jù)庫(kù)恢復(fù)準(zhǔn)備基礎(chǔ)數(shù)據(jù)庫(kù)
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DBNOR’’
–為日數(shù)據(jù)庫(kù)恢復(fù)準(zhǔn)備基礎(chǔ)數(shù)據(jù)庫(kù)
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’DBNOR’’
’
exec p_createjob @jobname=’每月備份’,@sql,@freqtype=’month’,@time=
–2.建立每周差異備份和生成周備份數(shù)據(jù)庫(kù)的作業(yè),每周日下午17:00分進(jìn)行:
set @sql=’
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=’’PRODUCE_’’+convert(varchar(10),getdate(),112)+’’_w.bak’’
set @path=dbo.f_getdbpath(null)+@fname
–差異備份
exec p_backupdb @dbname=’’PRODUCE’’,@bkfname=@fname,@bktype=’’DF’’
–差異恢復(fù)周數(shù)據(jù)庫(kù)
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DF’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’week’,@time=
–3.建立每日日志備份和生成日備份數(shù)據(jù)庫(kù)的作業(yè),每周日下午17:15分進(jìn)行:
set @sql=’
declare @path nvarchar(260),@fname nvarchar(100)
set @fname=’’PRODUCE_’’+convert(varchar(10),getdate(),112)+’’_l.bak’’
set @path=dbo.f_getdbpath(null)+@fname
–日志備份
exec p_backupdb @dbname=’’PRODUCE’’,@bkfname=@fname,@bktype=’’LOG’’
–日志恢復(fù)日數(shù)據(jù)庫(kù)
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’LOG’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’day’,@time=
怎么備份SQL Server 2023數(shù)據(jù)庫(kù)
1.鼠標(biāo)右薯族旅鍵選擇你要進(jìn)行備份的數(shù)據(jù)庫(kù)-任務(wù)-備份
可以在常規(guī)選項(xiàng)頁(yè)面你可以選擇備份類(lèi)型是進(jìn)行完整數(shù)據(jù)庫(kù)備份還是差異數(shù)據(jù)庫(kù)備份
2.點(diǎn)擊添加選項(xiàng)，選擇數(shù)據(jù)庫(kù)文件的存放路徑
注意文件名記得加后綴.bak,便于恢復(fù)時(shí)的查找
3.你還可以在選項(xiàng)穗悉頁(yè)面是追加到現(xiàn)有的備份集，還是覆蓋所有的現(xiàn)有備份集，還可以選擇備份驗(yàn)證完整性（建議選擇），還可以選擇是否壓縮備份等。
二.語(yǔ)句備份數(shù)據(jù)庫(kù)
use
master
goBACKUP
DATABASE
TO
DISK
=
N’D:\Microsoft
sql
server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test.bak’
WITH
NOFORMAT,
NOINIT,
NAME
=
N’test-完整
數(shù)據(jù)庫(kù)
備份’,
SKIP,
NOREWIND,
NOUNLOAD,
STATS
=
10GO
數(shù)據(jù)庫(kù)日志備份
首先需要注意，數(shù)據(jù)庫(kù)日志的備份是基于數(shù)據(jù)庫(kù)完整備份，也就是說(shuō)你備份數(shù)據(jù)庫(kù)日志之前你首先要先對(duì)數(shù)據(jù)庫(kù)進(jìn)行一次完整的備份，因?yàn)橹g會(huì)數(shù)凳涉及到堅(jiān)持到檢查點(diǎn)lsn，這也是本文接下來(lái)要講的重點(diǎn)。
一.手動(dòng)備份數(shù)據(jù)庫(kù)日志
1.右鍵數(shù)據(jù)庫(kù)-任務(wù)-備份-選擇備份類(lèi)型（事務(wù)日志）
2.點(diǎn)添加，添加日志文件備份存儲(chǔ)路徑
關(guān)于備份數(shù)據(jù)庫(kù)sql server語(yǔ)句的介紹到此就結(jié)束了，不知道你從中找到你需要的信息了嗎 ？如果你還想了解更多這方面的信息，記得收藏關(guān)注本站。
成都服務(wù)器托管選創(chuàng)新互聯(lián)，先上架開(kāi)通再付費(fèi)。
創(chuàng)新互聯(lián)（www.cdcxhl.com）專(zhuān)業(yè)-網(wǎng)站建設(shè),軟件開(kāi)發(fā)老牌服務(wù)商！微信小程序開(kāi)發(fā),APP開(kāi)發(fā),網(wǎng)站制作，網(wǎng)站營(yíng)銷(xiāo)推廣服務(wù)眾多企業(yè)。電話：028-86922220
網(wǎng)站欄目：SQL Server數(shù)據(jù)庫(kù)備份操作詳解 (備份數(shù)據(jù)庫(kù)sql server語(yǔ)句)
文章起源：http://m.5511xx.com/article/cdjjsio.html
