IF EXISTS (SELECT Name From SYS.OBJECTS WHERE Name = 'proc_MyDdRestore' AND type = 'P')
DROP PROCEDURE proc_MyDdRestore
GO
CREATE PROCEDURE dbo.proc_MyDdRestore
(
@path varchar(2000),
@Supress int = 0,
@DBName varchar(100),
@PhysicalDataName Varchar(100),
@PhysicalLogName Varchar(100),
@NewPathDataFile Varchar(1000),
@NewPathLogFile Varchar(1000)
)
AS
SET NOCOUNT ON
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
TRUNCATE TABLE Directory_Contents_Stage
TRUNCATE TABLE Directory_Contents
DECLARE @cmd varchar(4000)
IF Right(@path,7) <> '\*.bak'
BEGIN
Set @path = @path + '\*.bak'
END
SELECT @cmd = 'Dir "' + @path + '"'
--print @cmd
INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd
IF EXISTS (SELECT * FROM Directory_Contents_Stage
WHERE dir_output = 'The system cannot find the file specified.')
BEGIN
PRINT 'The system cannot find the file specified.'
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
RETURN
END
IF EXISTS (SELECT * FROM Directory_Contents_Stage
WHERE dir_output = 'File Not Found')
BEGIN
PRINT 'File Not Found'
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
RETURN
END
IF EXISTS (SELECT * FROM Directory_Contents_Stage
WHERE dir_output = 'The system cannot find the path specified.')
BEGIN
PRINT 'The system cannot find the path specified.'
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
RETURN
END
UPDATE Directory_Contents_Stage SET [dir] = @path
DELETE FROM Directory_Contents WHERE [dir] = @path
INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
SELECT CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
, CONVERT(bigint,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size]
, SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
, [dir]
, 'FILE'
FROM Directory_Contents_Stage
WHERE SUBSTRING(dir_output,1,1) <> ' '
AND (SUBSTRING(dir_output,1,1) <> ' '
AND SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62))
INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
SELECT CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
, SUBSTRING(dir_output,40,(LEN(dir_output)-39)) As [File_Name]
, [dir]
, CHAR(60)+'Dir'+CHAR(62) AS [Struct_Type]
FROM Directory_Contents_Stage
WHERE SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62)
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.
RECONFIGURE
IF @Supress = 0
BEGIN
Declare @FileName as Varchar(100)
Select @FileName = File_name From Directory_Contents Where Create_time In (
select max(create_Time)From Directory_Contents
where Struct_Type = 'FILE' And
SubString(File_Name,1,Len(@DBName)) = @DBName
Group by Substring(File_name,1,14))
DECLARE @cmdRestore Varchar(max)
DECLARE @sMovingData Varchar(max)
DECLARE @sMovingLog VARCHAR(Max)
if @NewPathLogFile <> ''
Set @sMovingLog = ', @with = ''move "'+ + @PhysicalLogName + '" to "' + @NewPathLogFile + '"'''
if @NewPathDataFile <> ''
Set @sMovingData = ', @with = ''move "' + @PhysicalDataName + '" to "' + @NewPathDataFile + '"'''
Set @cmdRestore = 'exec master.dbo.xp_restore_database @database = ''' + @DBName + ''', @filename = ''' + @FileName + ''',@with=''STATS = 10''' + @sMovingData + @sMovingLog + ',@affinity = 0,@logging = 0'
Print @cmdRestore
END