quote:

---

Go with the flow & have fun! Else fight the flow

---

quote:

---

Originally posted by drewsalem

Michael, that makes me feel a bit better, as that's what I'm doing. Though, I don't understand the need for the script in point 1, as Enterprise Manager lists all the transactional logs in order after the last complete backup (doesn't it?). What advantage does the scripts have?

---

quote:

---

Originally posted by drewsalem

Michael, that makes me feel a bit better, as that's what I'm doing. Though, I don't understand the need for the script in point 1, as Enterprise Manager lists all the transactional logs in order after the last complete backup (doesn't it?). What advantage does the scripts have?

---

SET NOCOUNT ON
DECLARE @BACKUPDIR AS NVARCHAR(255) 	-- THE DIRECTORY WHERE THE ACKUPS ARE PLACED
DECLARE @RESTORESET AS INTEGER		-- THE BACKUPSET YOU WANT TO RESTORE
DECLARE @NEWDBNAME AS VARCHAR(200)	-- THE NEW DATABASE NAME
DECLARE @ORIGDBNAME AS VARCHAR(200) 	-- THE ORIGINAL DATABASE NAME
DECLARE @ORIGLGNAME AS VARCHAR(200) 	-- THE ORIGINAL LOG FILE NAME
DECLARE @NEWDEST AS VARCHAR(255)		-- THE NEW DESTINATION DIRECTORY

SET @BACKUPDIR  = 'F:\MSSQL\MSSQL\BACKUP\NORTHWIND\'
SET @RESTORESET = 2			-- RESTORE THE SECOND BACKUPSET FOUND IN THE DIRECTORY
SET @NEWDBNAME  = 'NORTHWIND'
SET @ORIGDBNAME = 'NORTHWIND'
SET @ORIGLGNAME = 'NORTHWIND_LOG'
SET @NEWDEST    = 'F:\MSSQL\MSSQL\DATA\'

DECLARE @DB_FILENAME_PRFX AS VARCHAR(200)
DECLARE @LG_FILENAME_PRFX AS VARCHAR(200)

CREATE TABLE #DIRECTORYTABLE 
( ID INT IDENTITY,
  SUBDIRECTORY SYSNAME,
  DEPTH INTEGER,
  [FILE] INTEGER,
  SORTNAME VARCHAR(200),
  SORTTIMESTR VARCHAR(30),
  SORTTIME DATETIME)

-- GET THE DIRECTORY INFORMATION
INSERT 	#DIRECTORYTABLE (SUBDIRECTORY,DEPTH,[FILE]) 
EXECUTE 	MASTER.DBO.XP_DIRTREE @BACKUPDIR, 1, 1

-- GET THE FILE NAMES
SELECT TOP 1 @DB_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'') 
FROM #DIRECTORYTABLE 
WHERE	SUBDIRECTORY LIKE '%.BAK'
SELECT TOP 1 @LG_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'') 
FROM 	#DIRECTORYTABLE 
WHERE	SUBDIRECTORY LIKE '%.TRN'


-- GET THE TIMESTRING COINTAINED IN THE FILENAME AND UPDATE THE TABLE
UPDATE #DIRECTORYTABLE 
SET 	SORTNAME = @DB_FILENAME_PRFX ,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@DB_FILENAME_PRFX,''),'.BAK','')
WHERE SUBDIRECTORY LIKE @DB_FILENAME_PRFX+'%' 

UPDATE #DIRECTORYTABLE 
SET SORTNAME = @LG_FILENAME_PRFX	,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@LG_FILENAME_PRFX,''),'.TRN','')
WHERE SUBDIRECTORY LIKE @LG_FILENAME_PRFX+'%' 

-- NOW CREATE A MEMORYTABLE AND FORMAT THE DATA AS NEEDED
DECLARE @MEMDIR TABLE (ID INT IDENTITY,[FILENAME] VARCHAR(200),SORTNAME VARCHAR(200),SORTTIMESTR CHAR(12),BACKUPSET INTEGER)

-- INSERT THE NEEDED VALUES IN THE CORRECT ORDER
INSERT @MEMDIR ([FILENAME],SORTNAME,SORTTIMESTR) SELECT SUBDIRECTORY,SORTNAME,SORTTIMESTR FROM #DIRECTORYTABLE ORDER BY SORTTIMESTR

-- DROP THE TEMP TABLE
DROP TABLE #DIRECTORYTABLE

-- DECLARE SOME OTHER NEEDED VARIABLES
DECLARE @NAME VARCHAR(200)
DECLARE @CURRENT INTEGER
DECLARE @MAX INTEGER
DECLARE @BACKUPSET INTEGER
DECLARE @CURRENTSET INTEGER
DECLARE @BCKFNAME VARCHAR(200)
DECLARE @MAXID INTEGER


-- ENUMERATE THE BACKUP SET AND DETERMINE HOW MANY SETS THERE ARE AND NUMBER THEM
SET @CURRENT = 1
SET @BACKUPSET = 0
SELECT @MAX = MAX(ID) FROM @MEMDIR
WHILE @CURRENT <= @MAX
BEGIN
	SELECT @NAME=SORTNAME FROM @MEMDIR WHERE ID = @CURRENT
	IF @NAME = @DB_FILENAME_PRFX
   BEGIN
      SET @BACKUPSET = @BACKUPSET + 1
   END
	UPDATE @MEMDIR SET BACKUPSET = @BACKUPSET WHERE ID = @CURRENT
	SET @CURRENT = @CURRENT + 1
END


-- OK WE KNOW WHICH BACKUP TO RESTORE ENUMERATE THE BACKUPS AGAIN AND OUTPUT THE RESTORE STATEMENTS
SELECT @MAXID = MAX(ID) FROM @MEMDIR WHERE BACKUPSET = @RESTORESET
SET @CURRENT = 1
WHILE @CURRENT <= @MAX
	BEGIN
		SELECT @NAME=SORTNAME,@CURRENTSET=BACKUPSET,@BCKFNAME= [FILENAME] FROM @MEMDIR WHERE ID = @CURRENT
		IF @CURRENTSET = @RESTORESET
			BEGIN
				IF @NAME = @DB_FILENAME_PRFX 
					BEGIN
						PRINT 'RESTORE DATABASE [' + @NEWDBNAME + ']'
						PRINT 'FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
						PRINT 'WITH MOVE ''' + @ORIGDBNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '.MDF'','
						PRINT '     MOVE ''' + @ORIGLGNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '_LOG.LDF'','
						IF @CURRENT < @MAXID PRINT '     NORECOVERY' 
						IF @CURRENT = @MAXID PRINT '     RECOVERY' 
					END
				ELSE
					BEGIN
						PRINT 'RESTORE LOG [' + @NEWDBNAME + ']'
						PRINT '     FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
						IF @CURRENT < @MAXID PRINT '     WITH NORECOVERY' 
						IF @CURRENT = @MAXID PRINT '     WITH RECOVERY -- STOPAT = ''YYYY-MM-DD UU:MM:SS.000'''
					END
			END
		SET @CURRENT = @CURRENT + 1
	END

quote:

---

Originally posted by alexkreyn
... code omitted. ..

---