快捷導(dǎo)航

數(shù)據(jù)庫日常維護(hù)常用的腳本概述及部分測試

更新時間：2013年02月25日 09:25:43 作者：

今天花點時間在SQL SERVER 2008中運行試試常用的數(shù)據(jù)庫維護(hù)腳本,結(jié)果發(fā)現(xiàn)以前從書本上見過的backup語句和dump語句已經(jīng)在2008里面消失了，感興趣的你可不要錯過了哈，經(jīng)驗之談啊，希望可以幫助到你

有些數(shù)據(jù)庫語句可能平時用得不多，所以使用的時候總是難免上網(wǎng)查詢，略嫌麻煩，今日在CSDN論壇看到有貼收錄這些語句，就順手牽羊copy了一份，本想copy了就結(jié)束了，但和部分朋友一樣，真正用到它們的時候有時會發(fā)現(xiàn)難免會出錯，于是，擇日不如撞日，就在今天花點時間在SQL SERVER 2008中運行試試。之前并沒怎么注意SQL2008的語法與之前版本的不同，因為常用的select等語句都一樣，一直以為2008應(yīng)該與2005的版本幾乎無差。不料這一試，果然試出了問題，這才發(fā)現(xiàn)，以前從書本上見過的backup語句和dump語句已經(jīng)在2008里面消失了……

廢話不多說，把收錄的語句及備注貼上來，便于以后查詢使用~~

1、數(shù)據(jù)庫備份操作：

復(fù)制代碼代碼如下:

 
declare @sql varchar(8000) 
set @sql='backup database smallerp to disk=''d:\'+RTRIM(CONVERT(varchar,getdate(),112))+'.bak''' 
exec(@sql); 

（備注：引號不可缺少任意一個，否則會報錯）
2、刪除5天前的備份文件：

復(fù)制代碼代碼如下:

 
declare @sql varchar(8000) 
set @sql='del d:\'+RTRIM(CONVERT(varchar,getdate()-5,112))+'.bak''' 
exec master..xp_cmdshell @sql; 

（備注：xp_cndshell默認(rèn)該組件會被阻止，需要先啟用它才可正常操作：使用如下語句將其啟用EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;）
3、收縮數(shù)據(jù)庫語句：

復(fù)制代碼代碼如下:

dump transaction smallerp with no_log;

(備注：這句話中會提示transaction附近有語法錯誤，原來在SQL2008中已經(jīng)不再使用此法來收縮數(shù)據(jù)庫了。而使用：alter，使用方法如下：

復(fù)制代碼代碼如下:

 
alter database smallerp set recovery simple 
dbcc shrinkdatabase(smallerp,0);) 
backup log smallerp with no_log; 

(備注：這句話執(zhí)行的時候會報錯：此語句不支持一個或多個選項(no_log),原因也是因為SQL2008已經(jīng)不再支持這種寫法了。)
4、查看數(shù)據(jù)庫里活動用戶和進(jìn)程信息：

復(fù)制代碼代碼如下:

sp_who 'active';

(備注：如果只是這么寫的話會提示sp_who附近有語法錯誤，但執(zhí)行的時候可以查到數(shù)據(jù)，如果在前面加一個exec，則不再提示錯誤。且1—50的進(jìn)程號
為SQL SERVER系統(tǒng)內(nèi)部用的，只有大于50的進(jìn)程號才是用戶的連接進(jìn)程。spid是進(jìn)程號，dbid是數(shù)據(jù)庫編號，objid是數(shù)據(jù)對象編號)
5、查看數(shù)據(jù)庫里用戶和進(jìn)程信息：

復(fù)制代碼代碼如下:

Exec sp_who;

6、查看數(shù)據(jù)庫里的鎖情況：

復(fù)制代碼代碼如下:

Exec sp_lock;

7、分析SQL SERVER里sql語句的方法：

復(fù)制代碼代碼如下:

 
set statistics time {on | off}(寫語句的時候只需要寫明是on或者off即可) 
set statistics io {on | off} 

8、文本方式顯示查詢執(zhí)行計劃：

復(fù)制代碼代碼如下:

 
set showplan_all {on | off} 
set showplan_text { on | off } 
set statistics profile { on | off } 

9、差異備份：

復(fù)制代碼代碼如下:

 
declare @str varchar(100) 
set @str='D:\'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.diff' 
BACKUP DATABASE smallerp TO DISK=@str 
WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT, 
NAME=N'Demo差異備份',SKIP,NOREWIND, 
NOUNLOAD,STATS=10 
GO 

10、刪除過期的備份文件，并設(shè)定每天兩次：

復(fù)制代碼代碼如下:

 
declare @str varchar(100),@dir varchar(100),@fileName varchar(30) 
set @dir='del D:\ ' 
set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8) 
set @str=@dir+'fullbak'+@filename+'*.bak' 
exec xp_cmdshell @str 
set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8) 
set @str=@dir+'diffbak'+@filename+'*.diff' 
exec xp_cmdshell @str 
set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8) 
set @str=@dir+'logbak'+@filename+'*.trn' 
exec xp_cmdshell @str 

先這些吧，貌似一般不用又相對比較常用~