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]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET 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:\backup_data\db_test', 'E:\sqlserver_data\db_test'
/***************************************************************************************/

CREATE proc sp_RestoreDir
@restoreFromDir varchar(255),
@restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null
as

--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir

set nocount on

declare @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=0
insert into #dirList(filename) exec master..xp_cmdshell @cmd

-- 找到備份目錄下次新的2個文件名, 不處理最新的, 避免最新備份好的物理文件沒有完全復制成功
select filename from #dirList where id>1 and id<8 order by id desc

begin
declare BakFile_csr cursor for
select filename from #dirList where id>1 and id<8 order by id desc
end

open BakFile_csr

fetch BakFile_csr into @filename

while @@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, MaxSize
from #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 loop

close DataFileCursor
deallocate DataFileCursor

select @cmd = @cmd + ' REPLACE'
print @cmd
print ''

select @cmd

EXEC (@cmd)

IF @@ERROR=0
BEGIN
-- 如果恢復成功,記恢復操作日志
delete from restore_log where dt<getdate()-2
insert into restore_log (filename) values (@filename)
END

truncate table #filelist

end -- @i_exist=0

fetch BakFile_csr into @filename

end -- BakFile_csr loop

close BakFile_csr
deallocate BakFile_csr

drop table #dirList

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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]
GO

create PROCEDURE dbo.p_delete_db_test_backup
AS
DECLARE
@year1 varchar(4),
@month1 varchar(2),
@day1 varchar(2),
@sqlstr varchar(2000)
begin

--Get year & month &day fromat of the day before yesterday
SET @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' + @month1
if len(@day1)<2 set @day1 = '0' + @day1

set @sqlstr='del F:\backup_data\db_test\db_test_db_'+@year1+@month1+@day1+'*.bak'

select @sqlstr

exec master..xp_cmdshell @sqlstr

set @sqlstr='del F:\backup_data\db_test\db_test_tlog_'+@year1+@month1+@day1+'*.trn'

select @sqlstr

exec master..xp_cmdshell @sqlstr

end