September 14, 2016 at 12:10 pm
HI,
I used to have a script that would do this but can't fine it anywhere.
Using A\G groups and need to manually replay several transaction files (all files in a directory).
Anyone know of a script that will restore (with no recovery) every trn file in a folder?
September 14, 2016 at 12:32 pm
How about this?
http://www.sqlservercentral.com/articles/Restore+Genie/145380/"> http://www.sqlservercentral.com/articles/Restore+Genie/145380/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 14, 2016 at 12:37 pm
Thanks, but I'm looking for somthing similar to this..(but I can't this working - nothing prints)
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'pcm_epic'
SET @backupPath = 'H:\tlogs\'
-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
--SELECT @lastFullBackup = MAX(backupFile)
--FROM @fileList
--WHERE backupFile LIKE '%.BAK'
-- AND backupFile LIKE @dbName + '%'
--SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
-- + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
--PRINT @cmd
-- 4 - Find latest diff backup
--SELECT @lastDiffBackup = MAX(backupFile)
--FROM @fileList
--WHERE backupFile LIKE '%.DIF'
-- AND backupFile LIKE @dbName + '%'
-- AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
--IF @lastDiffBackup IS NOT NULL
--BEGIN
-- SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
-- + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
-- PRINT @cmd
-- SET @lastFullBackup = @lastDiffBackup
--END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
September 14, 2016 at 1:01 pm
The script looks like it works - I was just messing with it.
Try changing your PRINTs to SELECTs - i.e. SELECT @cmd
Sue
September 14, 2016 at 1:17 pm
Concat null yields null.
One of the variables you are concatenating is null. So, you get nothing.
Add some print statements after you initialize each variable, and see what is in them.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 14, 2016 at 1:49 pm
Michael L John (9/14/2016)
Concat null yields null.One of the variables you are concatenating is null. So, you get nothing.
Add some print statements after you initialize each variable, and see what is in them.
Looked like the buffering issue with PRINTs to me. Remove the commented out sections and nothing is returned.
Change the Prints to Selects and all of the original Print statements that are now selects showed the commands. Nothing was null and all of the correct commands were displayed.
Sue
September 15, 2016 at 8:38 am
Thanks everyone..that worked!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply