SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

----------------------------------------------------------------------------------------------------
-- OBJECT NAME	        : isp_Restore
-- AUTHOR               : Tara Duggan
-- DATE			: November 3, 2005
-- INPUTS		: @dbName - name to use for the restored database
--			  @bkpDir - full path to the directory where the BAK files exist
--			  @newDataLocation - new location and file name for the MDF, can be NULL
--			  @newLogLocation - new location and file name for the LDF, can be NULL
-- OUTPUTS		: None
-- RETURN CODES		: 0 - success
--			  1 - invalid path
--			  2 - no BAK files exist in @bkpDir
--			  3 - backup contains more than 2 D files which isn't supported by this sproc
--			  4 - restore error
-- DEPENDENCIES	        : None
-- DESCRIPTION	        : This stored procedure performs a restore of the newest BAK file in a specified directory.
-- EXAMPLES (optional)  : 
/*
		DECLARE @rc tinyint
		EXEC @rc = dbo.isp_Restore @dbName = 'SomeDB', @bkpDir = 'H:\MSSQL\BACKUP\SomeDB\, @newDataLocation = 'F:\MSSQL\DATA\SomeDB_Data.MDF', @newLogLocation = 'G:\MSSQL\DATA\SomeDB_Log.LDF'
		PRINT @rc
*/
----------------------------------------------------------------------------------------------------
ALTER  PROC dbo.isp_Restore
(@dbName sysname, @bkpDir nvarchar(1000), @newDataLocation nvarchar(1000), @newLogLocation nvarchar(1000))
AS

SET NOCOUNT ON

DECLARE @cmd sysname, @bkpFile nvarchar(1000), @sql nvarchar(4000), @logicalDataName nvarchar(128), @logicalLogName nvarchar(128)

-- Add a backslash to the end of the path if one doesn't exist
IF REVERSE(SUBSTRING(@bkpDir, 1, 1)) <> '\'
	SET @bkpDir = @bkpDir + '\'

-- Get files sorted by date
SET @cmd = 'dir ' + @bkpDir + '*.BAK /OD'

CREATE TABLE #Dir(DirInfo VARCHAR(7000)) -- Stores the dir results
CREATE TABLE #BackupFiles(BackupDate varchar(10), BackupFileName nvarchar(1000)) -- Stores only the data we want from the dir

INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd

IF EXISTS (SELECT * FROM #Dir WHERE DirInfo = 'The system cannot find the path specified.')
	RETURN 1

INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'

IF @@ROWCOUNT = 0
	RETURN 2

-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC

DROP TABLE #Dir, #BackupFiles

SET @cmd = @bkpDir + @bkpFile

-- Stores RESTORE FILELISTONLY result set
CREATE TABLE #RestoreFileListOnly
(
	LogicalName nvarchar(128), 
	PhysicalName nvarchar(260), 
	Type char(1), 
	FileGroupName nvarchar(128), 
	[Size] numeric(20,0), 
	[MaxSize] numeric(20,0) 
)

INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')

IF @@ROWCOUNT <> 2
	RETURN 3

SELECT @logicalDataName = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'

SELECT @logicalLogName = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'

DROP TABLE #RestoreFileListOnly

SET @sql = ''
SET @sql = @sql + 'RESTORE DATABASE ' + @dbName + CHAR(10)
SET @sql = @sql + 'FROM DISK = ''' + @cmd + '''' + CHAR(10)
SET @sql = @sql + 'WITH' + CHAR(10)
SET @sql = @sql + CHAR(9) + 'REPLACE'

IF @newDataLocation IS NOT NULL
BEGIN
	SET @sql = @sql + ',' + CHAR(10)
	SET @sql = @sql + CHAR(9) + 'MOVE ''' + @logicalDataName + ''' TO ''' + @newDataLocation + ''''
END

IF @newLogLocation IS NOT NULL
BEGIN
	SET @sql = @sql + ',' + CHAR(10)
	SET @sql = @sql + CHAR(9) + 'MOVE ''' + @logicalLogName + ''' TO ''' + @newLogLocation + ''''
END

PRINT @sql
EXEC(@sql)

IF @@ERROR <> 0 
	RETURN 4
ELSE
	RETURN 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

quote:

---

Originally posted by jpotucek

not sure if I understand. I don't have enterprise edition.. don't I need enterprise edition to do log shipping?

---