常用的SQL语句备忘
作者:admin 发布于:2013-3-20 8:34 Wednesday 分类:MSSQL
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