建站
咨詢
售后
建站咨詢
新聞中心
SQL Server是Microsoft公司開發(fā)的關系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS），許多企業(yè)和組織使用SQL Server數(shù)據(jù)庫存儲重要數(shù)據(jù)和信息。為了保護這些數(shù)據(jù)免受數(shù)據(jù)損失、硬件故障和其他意外事件的影響，必須定期備份SQL Server數(shù)據(jù)庫。本文將在詳細講解SQL Server數(shù)據(jù)庫備份操作的基礎上，提供一些有關如何在備份數(shù)據(jù)庫方面采取更佳實踐的建議。
一. SQL Server數(shù)據(jù)庫備份概述
SQL Server數(shù)據(jù)庫備份是指復制數(shù)據(jù)庫中的所有數(shù)據(jù)和對象并存儲在另一個位置以進行恢復。 要備份SQL Server數(shù)據(jù)庫，必須使用SQL Server Management Studio或Transact-SQL（T-SQL）命令的一個或多個命令，具體取決于您的選擇和需要。數(shù)據(jù)庫備份的頻率應與業(yè)務要求和數(shù)據(jù)重要性相關，一般建議每天備份一次、增量備份及日志備份，太高頻可能會增加服務器負擔。SQL Server數(shù)據(jù)庫備份通常存儲在磁盤、網(wǎng)絡共享或磁帶上，以便將數(shù)據(jù)庫恢復到在發(fā)生數(shù)據(jù)損壞或其他錯誤時的先前狀態(tài)。數(shù)據(jù)庫備份可以進行完整備份、差異備份和日志備份。下面我們來詳細介紹一下這幾種備份方式。
二. SQL Server完整備份
完整備份包括整個數(shù)據(jù)庫的完整備份，包括用戶數(shù)據(jù)、元數(shù)據(jù)和所有組件的備份。完整備份需要占用的存儲空間較大，尤其是對于大型數(shù)據(jù)庫，備份時間和恢復時間也會很長。完整備份通常是創(chuàng)建備份的開始，然后使用增量備份和/或事務日志備份來進一步保護數(shù)據(jù)。完整備份是最基本的備份類型，也是最常見的備份方式。
三. SQL Server差異備份
差異備份是基于最近的完整備份創(chuàng)建的備份。它包含自上次完整備份以來增加的所有更改，這些更改都是在差異備份進行時發(fā)生的。這樣做可以解決完整備份時間過長和備份數(shù)據(jù)過程帶來的復雜性問題。 然而，差異備份也有缺點，主要是它需要更多的存儲空間和更長的恢復時間，因為差異備份只包括自上次完整備份以來的更改。
四. SQL Server日志備份
日志備份是對事務日志的備份。它只包含自上次備份以來的所有事務日志更改，這使得日志備份相對較小，而且只需花費較少的時間。這種備份類型通常是為了在災難發(fā)生后，快速而可靠地還原數(shù)據(jù)而保留的，修正系統(tǒng)故障或人為的數(shù)據(jù)操作而作為最后備手段。日志備份不需要完整備份或差異備份作為前提條件，它們不同之處在于，日志備份是只備份事務日志，而不是備份整個數(shù)據(jù)庫。
五. 備份數(shù)據(jù)庫的更佳實踐
以下是幾個備份數(shù)據(jù)庫的更佳實踐：
1. 定期備份：根據(jù)業(yè)務重要性，定期備份的頻率應該是每天都要備份一次，每周、每月進行完整備份。
2. 將備份文件存儲在多個位置：即使您的備份位置很安全，也應該將備份文件主存儲位置保存在多個位置。
3. 定期測試備份：確保您的備份文件是可以恢復的，定期測試備份文件中數(shù)據(jù)的完整性和有效性。
4. 關閉數(shù)據(jù)庫以進行完整備份：為了確保在完整備份期間沒有數(shù)據(jù)丟失，應該將數(shù)據(jù)庫關閉，然后進行完整備份，然后再重新啟動數(shù)據(jù)。
5. 根據(jù)需要進行增量備份：差異備份可以在完整備份之間提供額外的保護，它可以減少恢復時間，增量備份可以只備份修改過的數(shù)據(jù)，減少了備份時間。
6. 不保留太多備份：不應保留過多的備份文件，否則會占用太多磁盤空間。
表面上來看，備份SQL Server數(shù)據(jù)庫貌似是一個簡單的過程，但在實踐中，一些看似不重要的細節(jié)卻可能導致數(shù)據(jù)庫的損壞。 在這篇文章中，我們介紹了SQL Server數(shù)據(jù)庫的3種備份方式：完整備份，差異備份和日志備份；并提供了幾個更佳實踐，以確保您有效的備份SQL Server數(shù)據(jù)庫并確保您的數(shù)據(jù)安全。了解和遵循這些操作和建議，可以幫助您更好地保護數(shù)據(jù)并減少潛在的風險。
相關問題拓展閱讀：
- 用SQL語句備份數(shù)據(jù)庫
- 怎么備份SQL Server 2023數(shù)據(jù)庫
用SQL語句備份數(shù)據(jù)庫
用SQL2023還原bak文件
1.右擊SQL Server 2023實例下的“數(shù)據(jù)庫”文件夾。就是master等數(shù)據(jù)庫上一級的那個圖標。選擇“所有任務”，“還原數(shù)據(jù)庫”
2.在“還原為數(shù)據(jù)庫”中填上你希望恢復的數(shù)據(jù)庫名字。這個名字應該與你的源碼中使用的數(shù)據(jù)庫名字一致。
3.在彈出的對話框中，選“從設備”
4.點擊“選擇設備”
5.點擊“添鏈橘加”
6.點擊“文件名”文本框右側的“…”按鈕，選中你的“.BAK”文件，并點擊確定回到“選擇還原設備”對話框。
7.點擊確定回到“還原數(shù)據(jù)庫”對話框。
8.點擊“選項”選項卡
9.將所有“移至物理文件名”下面的路徑，改為你想還原后的將數(shù)據(jù)庫文件保存到的路徑。如果你不希望改變，可以直接點擊確定。這時便肢弊恢復歷喚族成功了。
很不錯!我今天終于把.bak搞定了,這里有個要注意的地方就是選項中的“移至物理文件名”下面的路徑,這個路徑一定要修改哦,不然會出現(xiàn)錯誤
利用T-SQL語句,實現(xiàn)數(shù)據(jù)庫的備份和還原的功能
體現(xiàn)了SQL Server中的四個知識點：
1． 獲取SQL Server服務器上的默認目錄
2． 備份SQL語句的使用
3． 恢復SQL語句的使用，同時考慮了強制恢復時關閉其他用戶進程的處理
4． 作業(yè)創(chuàng)建SQL語句的使用
/*1.–得到數(shù)坦喊灶據(jù)庫的文件目錄
@dbname 指定要取得目錄的數(shù)據(jù)庫名
如果指定的數(shù)據(jù)不存在,返回安裝SQL時設置的默認數(shù)據(jù)目錄
如果指定NULL,則返回默認的SQL備份目錄名
*/
/*–調(diào)用示例
select 數(shù)據(jù)庫文件目錄=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ù)庫
*/
/*–調(diào)用示例
–備份當前數(shù)據(jù)庫
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_db.bak’
–差異備份當前讓扮數(shù)據(jù)庫
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_df.bak’,@bktype=’DF’
–備份當前數(shù)據(jù)庫日志
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ù)庫名稱,不指定則備份當前數(shù)據(jù)庫
@bkpath nvarchar(260)=’’, –備份文件的存放目錄,不指定則使用SQL默認的備份目錄
@bkfname nvarchar(260)=’’, –備份文件名,文件名中能用＼DBNAME＼代表數(shù)據(jù)庫名,＼DATE＼代表日期,＼TIME＼代表時間
@bktype nvarchar(10)=’DB’, –備份類型:’DB’備份數(shù)據(jù)庫,’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.–恢復數(shù)據(jù)庫
*/
/*–調(diào)用示例
–完整恢復數(shù)據(jù)庫
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’
–差異備份恢復
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’,@retype=’DBNOR’
exec p_backupdb @bkfile=’c:＼db__df.bak’,@dbname=’db’,@retype=’DF’
–日志備份恢復
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), –定義要恢復的備份文件名
@dbname sysname=’’, –定義恢復后的數(shù)據(jù)庫名,默認為備份的文件名
@dbpath nvarchar(260)=’’, –恢復后的數(shù)據(jù)庫存放目錄,不指定則為SQL的默認數(shù)據(jù)目錄
@retype nvarchar(10)=’DB’, –恢復類型:’DB’完事恢復數(shù)據(jù)庫,’DBNOR’ 為差異恢復,日志恢復進行完整恢復,’DF’ 差異備份的恢復,’LOG’ 日志恢復
@filenumber int=1, –恢復的文件號
@overexist bit=1, –是否覆蓋已存在的數(shù)據(jù)庫,僅@retype為
@killuser bit=1 –是否關閉用戶使用進程,僅@overexist=1時有效
as
declare @sql varchar(8000)
–得到恢復后的數(shù)據(jù)庫名
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)
–得到恢復后的數(shù)據(jù)庫存放目錄
if isnull(@dbpath,’’)=’’ set @dbpath=dbo.f_getdbpath(’’)
–生成數(shù)據(jù)庫恢復語句
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
–添加移動邏輯文件的處理
if @retype=’DB’ or @retype=’DBNOR’
begin
–從備份文件中獲取邏輯文件名
declare @lfn nvarchar(128),@tp char(1),@i int
–創(chuàng)建臨時表,保存獲取的信息
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
–關閉用戶進程處理
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
–恢復數(shù)據(jù)庫
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’
–每日執(zhí)行的作業(yè)
exec p_createjob @jobname=’a’,@sql=’select * from syscolumns’
–每日執(zhí)行的作業(yè),每天隔4小時重復的作業(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è)名稱
@sql varchar(8000), –要執(zhí)行的命令
@dbname sysname=’’, –默認為當前的數(shù)據(jù)庫名
@freqtype varchar(6)=’day’, –時間周期,month 月,week 周,day 日
@fsinterval int=1, –相對于每日的重復次數(shù)
@time int=開始執(zhí)行時間,對于重復執(zhí)行的作業(yè),將從0點到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 = ’時間安排’,
@freq_type=@ftype , –每天,8 每周,16 每月
@freq_interval=1, –重復執(zhí)行次數(shù)
@freq_subday_type=@fstype, –是否重復執(zhí)行
@freq_subday_interval=@fsinterval, –重復周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time –下午17:00:00分執(zhí)行
go
/*–應用案例–備份方案:
完整備份（每個星期天一次）+差異備份（每天備份一次）+日志備份（每2小時備份一次）
調(diào)用上面的存儲過程來實現(xiàn)
–*/
declare @sql varchar(8000)
–完整備份（每個星期天一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫名’’’
exec p_createjob @jobname=’每周備份’,@sql,@freqtype=’week’
–差異備份（每天備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫名’’,@bktype=’DF’’
exec p_createjob @jobname=’每天差異備份’,@sql,@freqtype=’day’
–日志備份（每2小時備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫名’’,@bktype=’LOG’’
exec p_createjob @jobname=’每2小時日志備份’,@sql,@freqtype=’day’,@fsinterval=2
/*–應用案例2
生產(chǎn)數(shù)據(jù)核心庫：PRODUCE
備份方案如下：
1.設置三個作業(yè),分別對PRODUCE庫進行每日備份,每周備份,每月備份
2.新建三個新庫,分別命名為:每日備份,每周備份,每月備份
3.建立三個作業(yè)，分別把三個備份庫還原到以上的三個新庫。
目的:當用戶在produce庫中有所有的數(shù)據(jù)丟失時,均能從上面的三個備份庫中導入相應的TABLE數(shù)據(jù)。
–*/
declare @sql varchar(8000)
–1.建立每月備份和生成月備份數(shù)據(jù)庫的作業(yè),每月每1天下午16:40分進行:
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ù)備份生成每月新庫
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_月’’
–為周數(shù)據(jù)庫恢復準備基礎數(shù)據(jù)庫
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DBNOR’’
–為日數(shù)據(jù)庫恢復準備基礎數(shù)據(jù)庫
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’DBNOR’’
’
exec p_createjob @jobname=’每月備份’,@sql,@freqtype=’month’,@time=
–2.建立每周差異備份和生成周備份數(shù)據(jù)庫的作業(yè),每周日下午17:00分進行:
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’’
–差異恢復周數(shù)據(jù)庫
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DF’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’week’,@time=
–3.建立每日日志備份和生成日備份數(shù)據(jù)庫的作業(yè),每周日下午17:15分進行:
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’’
–日志恢復日數(shù)據(jù)庫
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’LOG’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’day’,@time=171500
這個說的對！
/*1.–得到數(shù)據(jù)庫的文件目錄
@dbname 指定要取得目錄的數(shù)據(jù)庫名
如果指定的數(shù)據(jù)不存在,返回安裝SQL時設置的默認數(shù)據(jù)目錄
如果指定NULL,則返回默認的SQL備份目錄名
*/
/*–調(diào)用示例
select 數(shù)據(jù)庫文件目錄=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ù)庫
*/
/*–調(diào)用示例
–備份當前數(shù)據(jù)庫
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_db.bak’
–差異備份當前數(shù)據(jù)庫
exec p_backupdb @bkpath=’c:＼’,@bkfname=’db_＼DATE＼_df.bak’,@bktype=’DF’
–備份當前數(shù)據(jù)庫日志
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ù)庫名稱,不指定則備份當前數(shù)據(jù)庫
@bkpath nvarchar(260)=’’, –備份文件的存放目錄,不指定則使用SQL默認的備份目錄
@bkfname nvarchar(260)=’’, –備份文件名,文件名中能用＼DBNAME＼代表數(shù)據(jù)庫名,＼DATE＼代表日期,＼TIME＼代表時間
@bktype nvarchar(10)=’磨純DB’, –備份類型:’瞎茄咐DB’備份數(shù)據(jù)庫,’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.–恢復數(shù)據(jù)庫
*/
/*–調(diào)用示例
–完整恢復數(shù)據(jù)庫
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’
–差異備份恢復
exec p_RestoreDb @bkfile=’c:＼db__db.bak’,@dbname=’db’,@retype=’DBNOR’
exec p_backupdb @bkfile=’c:＼db__df.bak’,@dbname=’db’,@retype=’DF’
–日志備份恢復
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), –定義要恢復的備份文件名
@dbname sysname=’’, –定義恢復后的數(shù)據(jù)庫名,默認為備份的文件名
@dbpath nvarchar(260)=’’, –恢復后的數(shù)據(jù)庫存放目錄,不指定則為SQL的默認數(shù)據(jù)目錄
@retype nvarchar(10)=’DB’, –恢復類型:’DB’完事恢復數(shù)據(jù)庫,’DBNOR’ 為差異恢復,日志恢復進行完整恢復,’DF’ 差異備份的恢復,’LOG’ 日志恢復
@filenumber int=1, –恢復的文件號
@overexist bit=1, –是否覆蓋已存在的數(shù)據(jù)庫,僅@retype為
@killuser bit=1 –是否關閉用戶使用進程,僅@overexist=1時有效
as
declare @sql varchar(8000)
–得到恢復后的數(shù)據(jù)庫名
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)
–得到恢復后的數(shù)據(jù)庫存放目錄
if isnull(@dbpath,’’)=’’ set @dbpath=dbo.f_getdbpath(’’)
–生成數(shù)據(jù)庫恢復語句
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
–添加移動邏輯文件的處理
if @retype=’DB’ or @retype=’DBNOR’
begin
–從備份文件中獲取邏輯文件名
declare @lfn nvarchar(128),@tp char(1),@i int
–創(chuàng)建臨時表,保存獲取的信息
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
–關閉用戶進程處理
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
–恢復數(shù)據(jù)庫
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’
–每日執(zhí)行的作業(yè)
exec p_createjob @jobname=’a’,@sql=’select * from syscolumns’
–每日執(zhí)行的作業(yè),每天隔4小時重復的作業(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è)名稱
@sql varchar(8000), –要執(zhí)行的命令
@dbname sysname=’’, –默認為當前的數(shù)據(jù)庫名
@freqtype varchar(6)=’day’, –時間周期,month 月,week 周,day 日
@fsinterval int=1, –相對于每日的重復次數(shù)
@time int=開始執(zhí)行時間,對于重復執(zhí)行的作業(yè),將從0點到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 = ’時間安排’,
@freq_type=@ftype , –每天,8 每周,16 每月
@freq_interval=1, –重復執(zhí)行次數(shù)
@freq_subday_type=@fstype, –是否重復執(zhí)行
@freq_subday_interval=@fsinterval, –重復周期
@freq_recurrence_factor=@ffactor,
@active_start_time=@time –下午17:00:00分執(zhí)行
go
/*–應用案例–備份方案:
完整備份（每個星期天一次）+差異備份（每天備份一次）+日志備份（每2小時備份一次）
調(diào)用上面的存儲過程來實現(xiàn)
–*/
declare @sql varchar(8000)
–完整備份（每個星期天一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫名’’’
exec p_createjob @jobname=’每周備份’,@sql,@freqtype=’week’
–差異備份（每天備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫名’’,@bktype=’DF’’
exec p_createjob @jobname=’每天差異備份’,@sql,@freqtype=’day’
–日志備份（每2小時備份一次）
set @sql=’exec p_backupdb @dbname=’’要備份的數(shù)據(jù)庫名’’,@bktype=’LOG’’
exec p_createjob @jobname=’每2小時日志備份’,@sql,@freqtype=’day’,@fsinterval=2
/*–應用案例2
生產(chǎn)數(shù)據(jù)核心庫：PRODUCE
備份方案如下：
1.設置三個作業(yè),分別對PRODUCE庫進行每日備份,每周備份,每月備份
2.新建三個新庫,分別命名為:每日備份,每周備份,每月備份
3.建立三個作業(yè)，分別把三個備份庫還原到以上的三個新庫。
目的:當用戶在produce庫中有所有的數(shù)據(jù)丟失時,均能從上面的三個備份庫中導入相應的TABLE數(shù)據(jù)。
–*/
declare @sql varchar(8000)
–1.建立每月備份和生成月備份數(shù)據(jù)庫的作業(yè),每月每1天下午16:40分進行:
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ù)備份生成每月新庫
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_月’’
–為周數(shù)據(jù)庫恢復準備基礎數(shù)據(jù)庫
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DBNOR’’
–為日數(shù)據(jù)庫恢復準備基礎數(shù)據(jù)庫
exec p_RestoreDb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’DBNOR’’
’
exec p_createjob @jobname=’每月備份’,@sql,@freqtype=’month’,@time=
–2.建立每周差異備份和生成周備份數(shù)據(jù)庫的作業(yè),每周日下午17:00分進行:
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’’
–差異恢復周數(shù)據(jù)庫
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_周’’,@retype=’’DF’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’week’,@time=
–3.建立每日日志備份和生成日備份數(shù)據(jù)庫的作業(yè),每周日下午17:15分進行:
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’’
–日志恢復日數(shù)據(jù)庫
exec p_backupdb @bkfile=@path,@dbname=’’PRODUCE_日’’,@retype=’’LOG’’
’
exec p_createjob @jobname=’每周差異備份’,@sql,@freqtype=’day’,@time=
怎么備份SQL Server 2023數(shù)據(jù)庫
1.鼠標右薯族旅鍵選擇你要進行備份的數(shù)據(jù)庫-任務-備份
可以在常規(guī)選項頁面你可以選擇備份類型是進行完整數(shù)據(jù)庫備份還是差異數(shù)據(jù)庫備份
2.點擊添加選項，選擇數(shù)據(jù)庫文件的存放路徑
注意文件名記得加后綴.bak,便于恢復時的查找
3.你還可以在選項穗悉頁面是追加到現(xiàn)有的備份集，還是覆蓋所有的現(xiàn)有備份集，還可以選擇備份驗證完整性（建議選擇），還可以選擇是否壓縮備份等。
二.語句備份數(shù)據(jù)庫
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ù)庫
備份’,
SKIP,
NOREWIND,
NOUNLOAD,
STATS
=
10GO
數(shù)據(jù)庫日志備份
首先需要注意，數(shù)據(jù)庫日志的備份是基于數(shù)據(jù)庫完整備份，也就是說你備份數(shù)據(jù)庫日志之前你首先要先對數(shù)據(jù)庫進行一次完整的備份，因為之間會數(shù)凳涉及到堅持到檢查點lsn，這也是本文接下來要講的重點。
一.手動備份數(shù)據(jù)庫日志
1.右鍵數(shù)據(jù)庫-任務-備份-選擇備份類型（事務日志）
2.點添加，添加日志文件備份存儲路徑
關于備份數(shù)據(jù)庫sql server語句的介紹到此就結束了，不知道你從中找到你需要的信息了嗎 ？如果你還想了解更多這方面的信息，記得收藏關注本站。
成都服務器托管選創(chuàng)新互聯(lián)，先上架開通再付費。
創(chuàng)新互聯(lián)（www.cdcxhl.com）專業(yè)-網(wǎng)站建設,軟件開發(fā)老牌服務商！微信小程序開發(fā),APP開發(fā),網(wǎng)站制作，網(wǎng)站營銷推廣服務眾多企業(yè)。電話：028-86922220
當前標題：SQL Server數(shù)據(jù)庫備份操作詳解 (備份數(shù)據(jù)庫sql server語句)
網(wǎng)站鏈接：http://www.dlmjj.cn/article/cdjjsio.html
