几条有用的Sql语句

上都海奶茶馆

　　1. Sql 延时候执行
　　a. 三十秒后执行 WAITFOR DELAY 后面的语句
WAITFOR DELAY '00:00:30'
　　b. 10:00分开始执行后面的代码
WAITFOR TIME '10:00';
　　2. 收缩数据库，使其空余空间为0%
DBCC SHRINKDATABASE (DataBaseName,0)
　　3. 取得数据库文件所在的盘符
declare @dirverName varchar(50)
declare @bakUpPath varchar(255)
select top 1 @dirverName = filename from sysfiles
set @dirverName = substring(@dirverName,0,4)
set @bakUpPath = @dirverName + 'DataBaseName.bak'
　　4.将数据备份到指定路径，若遇备份文件已经存在则覆盖
BACKUP DATABASE DataBaseName TO DISK=@bakUpPath with INIT,Skip

　　BackUpPortal.sql
　　-- =============================================
-- Create basic stored procedure template
-- =============================================
　　-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'SP_BackUpPortal'
)
DROP PROCEDURE dbo.SP_BackUpPortal
GO
　　CREATE PROCEDURE dbo.SP_BackUpPortal
@backFolderPath varchar(256)='D:/MossDBFile/BackUp/Bak'
as
declare @today datetime
declare @todayString varchar(50)
declare @bakfilePath varchar(256)
declare @datenameString varchar(50)
set @today=getDate()
set @todayString=convert(varchar(11),@today,120)
select @datenameString= datename(dw,getdate())
-----If today is Sunday then do a full backup
if(@datenameString='Sunday')
begin
set @bakfilePath=@backFolderPath+'/Portal'+@todayString+'Full.bak';
backup database WSS_Content
to disk=@bakfilePath
end
------Else do a increment backup
else
begin
set @bakfilePath=@backFolderPath+'/Portal'+@todayString+'Increment.bak';
backup database WSS_Content
to disk=@bakfilePath
with DIFFERENTIAL
end
GO
　　
BackUpPortal.bat:
　　
cd D:/MossDBFile/BackUp/Bak
echo Backup database daily, if the day is sunday do a full back up else do a Increment backup
SQLCMD.EXE -S WS-USO-SITE/OFFICESERVERS -d WSS_Content -Q "exec dbo.SP_BackUpPortal "
　　Echo delte the backfile which generated before 30 days
　　FORFILES /P D:/MossDBFile/BackUp/Bak /D -7 /c "cmd /c del @path"
　　if %date:~0,3%==Sun goto BackByMossCmd
Exit
:BackByMossCmd
echo Backup by the mosscmd
cd C:/Program Files/Common Files/Microsoft Shared/web server extensions/12/BIN
C:
STSADM.EXE -o backup -url http://xxx/ -filename D:/BackUp/Portal/MossCmdPortalBack%date:~10,4%-%date:~4,2%-%date:~7,2%.bak
Exit