之前在做公司的數據庫備份，然后就想到了用代碼從創建數據庫開始，再備份再恢復再定期清理備份巴拉巴拉的全套

先膜拜一下大神http://www.cnblogs.com/gaizai/p/3535567.html 這個博主真心牛叉，主要是在跟著他的博學習的，

非常感謝博主的分享。。。

另外里面的數據庫名是我有些在測試庫上弄得，有些在正式庫上弄得，就不是很統一，反正沒差，只要看懂了可以自己修改的

一、創建數據庫

create database web_1 ?
on PRIMARY
( ?
??? NAME = web, ?
??? FILENAME='E:\test\web.mdf',?? --此路徑必須存在才能建成功
??? SIZE = 10, ?
??? MAXSIZE = UNLIMITED, ?
??? FILEGROWTH = 5 ?
) ?
LOG ON
( ?
??? NAME='web_dat', ?
??? FILENAME='E:\test\web.ldf',? --此路徑必須存在才能建成功
??? SIZE =5MB, ?
??? MAXSIZE = 25MB, ?
??? FILEGROWTH =5MB ?
) ?
GO
這個代碼創建跟從數據庫可視化界面操作有一些參數的區別，詳情說明本寶已經寫在另外一篇文章里面的

http://www.cnblogs.com/liu-shiliu/p/5557210.html

二 創建備份日志記錄表

USE [msdb]
GO
CREATE TABLE [dbo].[JobLog](
??? [Id] [int] IDENTITY(1,1) NOT NULL,
??? [DB_Name] [varchar](50) NULL,
??? [Backup_Date] [int] NULL,
??? [Backup_Time] [int] NULL ,
??? [Backup_Duration] [int] NULL,
??? [Backup_Type] [char](4) NULL,
?CONSTRAINT [PK_JobLog] PRIMARY KEY CLUSTERED
(
??? [Id] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY] --備份數據庫表

--錯誤記錄表
USE [msdb]
GO
CREATE TABLE [dbo].[ErrorLog](
??? [Id] [int] IDENTITY(1,1) NOT NULL,
??? [DB_Name] [varchar](50) NOT NULL,
??? [Backup_Time] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_Backup_Time]? DEFAULT (getdate()),
??? [Messages] [nvarchar](500) NULL,
?CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
??? [Id] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]


select * from [msdb].[dbo].[ErrorLog]

select * from [msdb].[dbo].[JobLog]

三、創建備份的文件夾，由于可能有多個數據庫，按數據庫名稱建文件夾，再依次備份到對應的文件夾里面

EXEC sp_configure 'show advanced options', 1 --若不開啟此項,會提示不能對系統時間進行即時更新
RECONFIGURE WITH override -- 有些sql版本不加WITH override這個參數就會報錯
EXEC sp_configure 'xp_cmdshell', 1 --此選項開啟有風險，操作完一定要關閉
RECONFIGURE WITH override

DECLARE @DBName VARCHAR(100)
DECLARE CurDBName CURSOR FOR
??? SELECT name FROM sys.databases WHERE name? not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') --要備份的數據庫文件

OPEN CurDBName
??? FETCH NEXT FROM CurDBName INTO @DBName
?? ?
??? WHILE @@FETCH_STATUS = 0
??? BEGIN
??????? --臨時表用于存錯誤信息
??????? if (OBJECT_ID('tempdb..#tb01') is not null) ?
?? ??? ?drop table #tb01? --drop table tempdb..#tb01 --ok too ?
?? ??? ?create table #tb01([dosCMDResult] varchar(4000)) --save DOS cmd result ?
?? ????? ?
?? ????? ?
?? ??? ?--//3,檢查指定的路徑是否存在，如果不存在則建立之，存在則提示 ?
?? ??? ?declare @strPath varchar(4000) ?
?? ??? ?declare @dosCMD varchar(50) ?
?? ??? ?declare @cmdLine varchar(4000) ?
?? ??? ?set @strPath = 'E:\BACKUP_'? + @DBName--指定路徑? ,可以用'E:\BACKUP\111'這種方式指定二級或更多級目錄，但是下面的找不到要改為 where dosCMDResult? like '%找不到文件%'
?? ??? ?set @dosCMD = 'dir ' --dos cmd ?
?? ??? ??? ?select @strPath
?? ??? ?set @cmdLine = @dosCMD+@strPath?? ?
?? ??? ?insert into #tb01 exec master..xp_cmdshell @cmdLine ?
?? ??? ?--select * from #tb01 ?
?? ??? ?if exists(select 1 from #tb01 where dosCMDResult = '找不到文件') --路徑不存在 ?
?? ??? ?begin??? ?
?? ??? ??? ?set @dosCMD = 'md '? --若上面置頂的路徑是二級或者多級目錄，也能實現創建
?? ??? ??? ?set @cmdLine = @dosCMD + @strPath ?
?? ??? ??? ?exec master..xp_cmdshell @cmdLine ?
?? ??? ?end ?
?? ??? ?drop table #tb01 ?
?? ??? ?set @cmdLine = null ?
?? ??? ?set @dosCMD = null ?
?? ??? ?set @strPath = null? ?
?
??????? FETCH NEXT FROM CurDBName INTO @DBName
??? END
CLOSE CurDBName
DEALLOCATE CurDBName

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE?? WITH override
?
EXEC sp_configure 'show advanced options', 0
RECONFIGURE?? WITH override

四、批量備份數據庫，并將數據庫備份到指定的文件夾下面

DECLARE @DBName VARCHAR(100)
DECLARE @CurrentTime VARCHAR(50)
DECLARE @FileName VARCHAR(200)
DECLARE @WithType CHAR(20)
DECLARE @Backup_Date VARCHAR(50)
DECLARE @Backup_Time VARCHAR(50)
DECLARE @Backup_Duration VARCHAR(50)
DECLARE @Backup_Start DATETIME
DECLARE @Backup_End DATETIME
DECLARE @BackupType CHAR(4)
DECLARE @SQL VARCHAR(MAX)

--防止作業遺漏備份
INSERT INTO [msdb].[dbo].[JobLog]([DB_Name],[Backup_Date],[Backup_Time],[Backup_Duration],[Backup_Type])
SELECT name,0,0,0,NULL FROM sys.databases WHERE name? not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
AND name NOT IN (SELECT DISTINCT [DB_Name] FROM [msdb].[dbo].[JobLog])
ORDER BY name

DECLARE CurDBName CURSOR FOR
??? SELECT name FROM sys.databases WHERE name? not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') ORDER BY name

OPEN CurDBName
??? FETCH NEXT FROM CurDBName INTO @DBName

??? WHILE @@FETCH_STATUS = 0
??? BEGIN
??????? --Execute Backup
??????? --捕獲異常
??????? BEGIN TRY
??????????? PRINT @DBName
??????????? SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')
??????????? IF(DATEPART(DW, GETDATE()) = 2)--星期一全備份
??????????? BEGIN
??????????????? SET @FileName = 'E:\BACKUP_'+@DBName+'\'+@DBName+'_Full_' + @CurrentTime+'.bak'
??????????????? SET @WithType = ' FORMAT'
??????????????? SET @BackupType = 'Full'
??????????? END
??????????? ELSE
??????????? BEGIN
??????????????? SET @FileName = 'E:\BACKUP_'+@DBName+'\'+@DBName+'_Diff_' + @CurrentTime+'.bak'
??????????????? SET @WithType = ' DIFFERENTIAL,FORMAT'
??????????????? SET @BackupType = 'Diff'
??????????? END

??????????? SET @Backup_Start = GETDATE()
??????????? SET @SQL = '
??????????? --1設置完整模式
??????????? ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH NO_WAIT;
??????????? --2備份主分區
??????????? BACKUP DATABASE ['+@DBName+']? /*FILEGROUP=''PRIMARY''*/ TO DISK='''+@FileName+''' WITH '+@WithType+',COMPRESSION; --新加,COMPRESSION 壓縮備份

??????????? --3設置簡單模式
??????????? ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT;
??????????? '
??????????? EXEC(@SQL)

??????????? SET @Backup_End = GETDATE()
??????????? SET @Backup_Date = CONVERT(VARCHAR, GETDATE(),112)
??????????? SET @Backup_Time = REPLACE(CONVERT(VARCHAR, GETDATE(),24),':','')
??????????? SET @Backup_Duration = CONVERT(VARCHAR,DATEDIFF(ss,@Backup_Start,@Backup_End))
??????????? PRINT @Backup_Date +@Backup_Time +@Backup_Duration
??????????? SET @SQL = '
??????????? INSERT INTO [msdb].[dbo].[JobLog]([DB_Name],[Backup_Date],[Backup_Time],[Backup_Duration],[Backup_Type])
??????????????? VALUES('''+@DBName+''','+@Backup_Date+','+@Backup_Time+','+@Backup_Duration+','''+@BackupType+''');
??????????? '
??????????? EXEC(@SQL)
??????? END TRY
??????? BEGIN CATCH
??????????? INSERT INTO [msdb].[dbo].[ErrorLog]([DB_Name],[Messages])
??????????? VALUES(@DBName,ERROR_MESSAGE())
??????????? --ROLLBACK TRANSACTION
??????? END CATCH

??????? --Get Next DataBase
??????? FETCH NEXT FROM CurDBName INTO @DBName
??? END
CLOSE CurDBName
DEALLOCATE CurDBName

針對這一段備份，又要再次膜拜http://www.cnblogs.com/gaizai/p/3535567.html這篇博客的博主了，太牛叉了，就是按照他的思路稍微修改了一點點，博主很威武

當時操作完這段的時候，瞬間覺得人生圓滿，畢竟之前我只是一個只會操作可視化界面的菜鳥，讀懂那篇博客再修改了一丟丟，好激動。。。

?

五、刪除數據庫

先解釋下為什么要刪除數據庫，我只是想在試試我備份的東西有沒有問題，所以我就把數據庫刪除了，然后再用上面的備份文件恢復的

切記切記這步有風險，最好先在本地實踐了確定Ok了再操作或者不刪除。刪除有風險，請謹慎

/*可以用sql語句使刪除數據庫時不會彈出“無法刪除數據庫? ,因為該數據庫當前正在使用”的提示嗎？*/

--找出要刪除的數據庫的進程
?SELECT 'kill?? ' + CAST(spid AS VARCHAR) ?
?FROM?? MASTER..sysprocesses ?
?WHERE? dbid = DB_ID('new_web') ?
?-- 刪除進程
?Use master
?kill?? 54
?GO
?
?--刪除數據庫 ?
drop database new_web




--------方法二（方法二我并沒有實踐，是從網上找的方法，有興趣的可以自己實踐）------------------------
USE MASTER?? ?
??? GO?? ?
??????? ?
??? DECLARE @dbname SYSNAME?? ?
??? SET @dbname = 'webcenter' --這個是要刪除的數據庫庫名?? ?
??????? ?
??? DECLARE @s NVARCHAR(1000)?? ?
??? DECLARE tb CURSOR LOCAL? ?
??? FOR ?
??????? SELECT s = 'kill?? ' + CAST(spid AS VARCHAR) ?
??????? FROM?? MASTER..sysprocesses ?
??????? WHERE? dbid = DB_ID(@dbname)?? ?
??????? ?
??? OPEN?? tb???? ?
??? FETCH?? NEXT?? FROM?? tb?? INTO?? @s?? ?
??? WHILE @@fetch_status = 0 ?
??? BEGIN ?
??????? EXEC (@s)? ?
??????? FETCH NEXT FROM tb INTO @s ?
??? END?? ?
??? CLOSE?? tb?? ?
??? DEALLOCATE?? tb?? ?
???? ?
??? EXEC ('drop?? database?? [' + @dbname + ']')???

?

六、還原數據庫

RESTORE? FILELISTONLY FROM DISK = N'E:\DBBackup\tttt_1\tttt_1_Full_2016_06_04_155332.bak' --備份文件存放路徑? 先用這段找到名稱

?

RESTORE DATABASE [new_ttttt] --新庫名字
FROM? DISK = N'E:\DBBackup\tttt_1\tttt_1_Full_2016_06_04_155332.bak' --備份文件
WITH? FILE = 1, ?
MOVE N'tttt_1' TO N'E:\test\new_tttt.mdf',? --原始數據庫名字 新的附加位置
MOVE N'tttt_1_log' TO N'E:\test\new_tttt_1.LDF', --原始數據庫名字 新的附加位置
NORECOVERY, ?
NOUNLOAD,? STATS = 10
GO --完整備份還原


RESTORE DATABASE [new_ttttt] --新庫名字
FROM? DISK = N'E:\DBBackup\tttt_1\tttt_1_Diff_2016_06_04_160529.bak'? --備份文件
WITH? FILE = 1,
MOVE N'tttt_1' TO N'E:\test\new_tttt.mdf',? --原始數據庫名字 新的附加位置
MOVE N'tttt_1_log' TO N'E:\test\new_tttt_1.LDF', --原始數據庫名字 新的附加位置
NOUNLOAD,? STATS = 10
GO--差異備份還原



/*

如果代碼還原數據庫，明明操作界面已經提示完成了，但是數據庫的地方一直顯示“正在還原”,
這個是因為恢復進程被掛起了。這個時候假設你要恢復并且回到可訪問狀態，要執行：
RESTORE database?? dbname with recovery
如果你要不斷恢復后面的日志文件，的確需要使數據庫處于“正在還原狀態”
RESTORE database dbname with norecovery
*/

還原數據庫這步，有可能會報錯誤，主要是紅色框那部分的名字是之前數據庫的名字，

?

這個怎么說喃，我目前就是各種蒙的，呃呃呃，找到解決方法了，就是加前面一句，不過也不要擔心，即使真的蒙不對，還可以用可視化界面操作。這樣會自動生成的

?

七、刪除備份文件

將上面的操作完以后，就把備份的這些加入到作業里面，讓它每天自動備份，關于備份計劃，本寶以前的公司就是每天差異備份，每周完全備份。然后現在本寶在游戲公司，有些運營數據比較及時，

所以本寶采用的是6小時一差異備份，每周一完整備份，并將備份的數據發到SVN上面去（不過這個是游戲上線后的策略哈，目前其實就是備份一次）。

因為本寶很怕數據出問題了然后恢復不了。反正就考慮盡量周全一點。

備份文件還是比較多，所以還是要定期清理備份文件的，但是每天自己點也是麻煩，所以又找了一些方法來自動刪除。

EXEC sp_configure 'show advanced options', 1? --不加這個會提示不能對系統時間進行及時更新
RECONFIGURE?? WITH override
EXEC sp_configure 'xp_cmdshell', 1 --此選項開啟有風險，操作完一定要關閉
RECONFIGURE?? WITH override

DECLARE @DBName VARCHAR(100)
declare @time varchar(100)=REPLACE(REPLACE(CONVERT(varchar(100), dateadd(day,-15,GETDATE()), 23 ),'-','_'),' ','_')+'_4' --4固定的時間小時 刪除前十五天的差異備份，因為我現在目前是每天定時備份一次的，所以時間就寫死了。后期還要修改

declare CurDBname cursor for--不加參數默認為Forward_Only
select name from sys.sysdatabases where name not? in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

open CurDBname
? FETCH NEXT FROM CurDBName INTO @DBName
while @@FETCH_STATUS=0
begin
?? ?declare @strPath varchar(4000) ?
?? ?declare @dosCMD varchar(50) ?
?? ?declare @cmdLine varchar(4000)
?? ?---------------1 -----------判斷要刪除的文件是否存在---------------
?? ?--創建臨時表，記錄查詢文件的結果
?? ?if (OBJECT_ID('tempdb..#tb01') is not null) ?
?? ?drop table #tb01? --drop table tempdb..#tb01 --ok too ?
?? ?create table #tb01([dosCMDResult] varchar(4000))

?? ?set @dosCMD='dir '
?? ?set @strPath='E:\BACKUP_'? + @DBName+'\'+@DBName+'_Diff_'+@time+'.bak' --構建差異備份的路徑
?? ?select @strPath
?? ?set @cmdLine = @dosCMD+@strPath
?? ?insert into #tb01 exec master..xp_cmdshell @cmdLine

?? ?if not exists(select 1 from #tb01 where dosCMDResult = '找不到文件')
?? ?begin
?? ? set @dosCMD='del '
?? ? set @cmdLine= @dosCMD+@strPath
?? ? exec master..xp_cmdshell @cmdLine
?? ?end
?? ?
?? ?drop table #tb01
?? ?FETCH NEXT FROM CurDBName INTO @DBName
end
Close? CurDBName
DEALLOCATE CurDBName
?
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE?? WITH override
?
EXEC sp_configure 'show advanced options', 0
RECONFIGURE?? WITH override

需要說明一下，我只寫了差異備份的刪除，因為完全備份很重要，我還是放棄了自動刪除的方式，還是決定定期手動清理，本寶怕剛好出問題了，然后完全備份又被刪除了，這樣就蒙圈了。

所以還是選擇麻煩一點，手動刪除完全備份。

?

八、就是這些，歡迎轉載，但是請注明出處。若是有問題神馬的，歡迎留言交流哦。。。。。。

?

轉載于:https://www.cnblogs.com/liu-shiliu/p/5566863.html

總結

以上是生活随笔為你收集整理的从创建数据库到备份恢复还原详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。