if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[sp_RestoreDir]GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO/***************************************************************************************/-- 目    的: 根據某個目錄下某個數據庫的備份文件(*.trn或*.bak都可以)還原數據庫.--           配合xcopy命令，可以在另一臺備份機器上實現standby SQL Server數據庫. ---- 輸入參數: @restoreFromDir - 存放*.trn或*.bak數據庫備份文件的目錄--     @restoreToDataDir - 數據庫數據文件將要還原的目錄--     @restoreToLogDir - 數據庫日志文件將要還原的目錄, 如果為空,--     日志文件和數據文件目錄相同---- Written By:    Chris Gallelli -- 8/22/2003-- Modified By:   Bruce Canaday -- 11/04/2003--   http://www.sqlservercentral.com/scripts/contributions/962.asp-- Modified By:   maggiefengyu@tom.com --- 02/23/2005---- 調用舉例: exec sp_RestoreDir 'F:ackup_datadb_test', 'E:sqlserver_datadb_test'/***************************************************************************************/CREATE   proc sp_RestoreDir        @restoreFromDir varchar(255),       @restoreToDataDir varchar(255)= null,       @restoreToLogDir varchar(255) = nullas--If a directory for the Log file is not supplied then use the data directoryIf @restoreToLogDir is nullset @restoreToLogDir = @restoreToDataDirset nocount ondeclare @filename         varchar(40),@cmd              varchar(500),         @DataName         varchar (255),@LogName          varchar (255),        @LogicalName      varchar(255), @PhysicalName     varchar(255), @Type             varchar(20), @FileGroupName    varchar(255), @Size             varchar(20), @MaxSize          varchar(20),@restoreToDir     varchar(255),@DBName           varchar(255),        @PhysicalFileName varchar(255),        @i_exist  int create table #dirList (id [int] IDENTITY (1, 1) NOT NULL , filename varchar(100))create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )--Get the list of database backups that are in the restoreFromDir directory order by date desc   select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'set @i_exist=0insert into #dirList(filename) exec master..xp_cmdshell @cmd  -- 找到備份目錄下次新的2個文件名, 不處理最新的, 避免最新備份好的物理文件沒有完全復制成功 select filename from #dirList where id>1 and id<8 order by id descbegin       declare BakFile_csr cursor for    select filename from #dirList where id>1 and id<8 order by id descendopen BakFile_csrfetch BakFile_csr into @filenamewhile @@fetch_status = 0   begin--  判斷恢復日志表restore_log存在否，不存在則創建表    if not exists (      select *       from dbo.sysobjects       where id = object_id('restore_log')       and OBJECTPROPERTY(id, N'IsUserTable') = 1)begin   create table restore_log (filename varchar(128),dt datetime default getdate())end--  判斷此備份文件有無在恢復日志表里記錄過?    select @i_exist=count(0) from restore_log where filename=@filename    if @i_exist=0    begin-- 根據備份目錄下的物理文件名, 找到對應的邏輯名等參數        select @cmd = "RESTORE FILELISTONLY FROM disk = '"         + @restoreFromDir + "" + @filename + "'"        insert #filelist exec ( @cmd )       --     select * from #filelist    if right(@filename,3)='TRN'    begin       select @dbName = left(@filename,datalength(@filename)        - patindex('%_golt_%',reverse(@filename))-5)       select @cmd = "RESTORE Log " + @dbName + " FROM DISK = '" + @restoreFromDir + "" + @filename + "' WITH STANDBY='"+@restoreToDataDir+"UNDO_"+@filename+".DAT ',"        print ''        print '--RESTORING Log  ' + @dbName    end        if right(@filename,3)='BAK'    begin          select @dbName = left(@filename,datalength(@filename)           - patindex('%_bd_%',reverse(@filename))-3)         select @cmd = "RESTORE DATABASE " + @dbName + " FROM DISK = '" + @restoreFromDir + "" + @filename + "' WITH NORECOVERY ,"          print ''           print '--RESTORING DATABASE ' + @dbName    end        -- 找到數據庫邏輯和物理文件名稱之間的對應關系       declare DataFileCursor cursor for  select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSizefrom #filelist       open DataFileCursor       fetch DataFileCursor into @LogicalName, @PhysicalName, @Type,        @FileGroupName, @Size, @MaxSize       while @@fetch_status = 0          begin                 -- RESTORE with MOVE option        select @PhysicalFileName = reverse(substring(       reverse(rtrim(@PhysicalName)),1,patindex('%\%',       reverse(rtrim(@PhysicalName)))-1 )) select @restoreToDir = @restoreToDataDir       select @cmd = @cmd +             " MOVE '" + @LogicalName + "' TO '" +               @restoreToDir + "" + @PhysicalFileName + "', "                                fetch DataFileCursor into @LogicalName, @PhysicalName,               @Type, @FileGroupName, @Size, @MaxSize          end  -- DataFileCursor loopclose DataFileCursor    deallocate DataFileCursor       select @cmd = @cmd + ' REPLACE'     print @cmd      print ''           select @cmd            EXEC (@cmd)IF @@ERROR=0BEGIN-- 如果恢復成功,記恢復操作日志   delete from restore_log where dt

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_delete_db_test_backup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_delete_db_test_backup]GOcreate PROCEDURE dbo.p_delete_db_test_backupASDECLARE@year1varchar(4),@month1varchar(2),@day1varchar(2),@sqlstrvarchar(2000)begin                     --Get year & month &day fromat of the day before yesterdaySET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),1,4)SET @month1 =substring(convert(varchar,datepart(mm,getdate()-2)),1,2)SET @day1=substring(convert(varchar,datepart(dd,getdate()-2)),1,2)if len(@month1)<2 set @month1 = '0' + @month1if len(@day1)<2 set @day1 = '0' + @day1set @sqlstr='del F:ackup_datadb_testdb_test_db_'+@year1+@month1+@day1+'*.bak'select @sqlstrexec master..xp_cmdshell @sqlstr  set @sqlstr='del F:ackup_datadb_testdb_test_tlog_'+@year1+@month1+@day1+'*.trn'select @sqlstrexec master..xp_cmdshell @sqlstr  end