September 22, 2008 at 11:53 am
hello all ,
based on the advise here , i made the changes in the script. and than ran it
**********************************************
"-- declare variables
declare @dir varchar(1000), @rowid int, @sqlcmd varchar(1000), @filename varchar(1000)
declare @trn table (rowid int identity(1,1),file_name varchar(1000))
-- set variables
set @dir = 'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' -- Dir path for the trn folder
insert into @trn exec xp_cmdshell @dir -- this statement will dump all your .trn files into a memory table
-- do stuff
Declare @max_row_id Int
select @max_row_id = Max(rowid) From @trn
select @rowid = min(rowid) from @trn
while @rowid is not null
begin
If @rowid = @max_row_id
Begin
set @sqlcmd = @sqlcmd + ' With Recovery'
End
Else
Begin
set @sqlcmd = @sqlcmd + ' With No Recovery'
End
select @filename = file_name from @trn where rowid = @rowid
set @sqlcmd = 'Restore Log IT From Disk = ''Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' + @filename + ''''
raiserror(@sqlcmd,0,1) with nowait
select @rowid = min(rowid) from @trn where rowID > 0 and rowid > @rowid
end
**********************************************
when i execute it , i get this message , not an error a message
"
3 row(s) affected)
Restore Log IT From Disk = 'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' is not recognized as an internal or external command,'
Restore Log IT From Disk = 'Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IToperable program or batch file.'
*********************************************
i went in the restore history table in msdb to see weather the log files have been restoed , i dont see it in that table ,
am i doing it right, any help will be so great .
Thanks
September 22, 2008 at 11:58 am
Jack C
since u know the history what i am looking for , ur input
could be very helpful.
Thanks
Bobby
September 22, 2008 at 12:22 pm
1. The result you are getting is an error returned by xp_cmdshell because you are not passing it a DOS command.
2. To correct this you need to revisit Oberion's post and lookup the switches he provides for the dir dos command. dir /? in a command window.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 12:31 pm
What is the 'additional' cost of simply using NORECOVERY for all of the log files, and then adding a line at the end (similar to a musical coda) which sets RECOVERY so the database is usable? Would that simplify the scripting without adding a lot of overhead, or am I missing the boat here?
September 22, 2008 at 12:49 pm
Yeah that would work, once he actually gets the commands working correctly to enumerate the files.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 1:08 pm
Hello all
so in the original oberian script /i386/ is the path and the rest are the switches that i need to add in my folder path??
Thanks
Bobby
September 22, 2008 at 1:15 pm
Yes, DIR is the command C:\I386\ is the path and the rest are switches.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 1:35 pm
Thank you Jack
Bobby
September 22, 2008 at 2:44 pm
hello all ,
when i run the script after making correction , it is giving me the following message , which i dont get it
*********************************************
-- declare variables
declare @dir varchar(1000), @rowid int, @sqlcmd varchar(1000), @filename varchar(1000)
declare @trn table (rowid int identity(1,1),file_name varchar(1000))
-- set variables
set @dir = 'dir c:\i386\ /TW /OD /B ' -- Dir path for the trn folder
insert into @trn exec xp_cmdshell @dir -- this statement will dump all your .trn files into a memory table
-- do stuff
--Declare @max_row_id Int
--select @max_row_id = Max(rowid) From @trn
select @rowid = min(rowid) from @trn
while @rowid is not null
begin
/*If @rowid = @max_row_id
Begin
set @sqlcmd = @sqlcmd + ' With Recovery'
End
Else
Begin
set @sqlcmd = @sqlcmd + ' With No Recovery'
End"*/
select @filename = file_name from @trn where rowid = @rowid
set @sqlcmd = 'restore LOG IT From Disk =''Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\IT' + @filename + ''''
raiserror(@sqlcmd,0,1) with nowait
select @rowid = min(rowid) from @trn where rowID > 0 and rowid > @rowid
end
**********************************************
(2 row(s) affected)
restore LOG IT From Disk ='Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\ITThe system cannot find the file specified.'
*********************************************
now the problem is , i am cutting and pasting the path exactly as it is, i dont think i need to add the file name just the path to the folder where these files are sitting since the file names r getting saved in the m table.
Thanks in advance.
Bobby
September 22, 2008 at 3:42 pm
The executed command must have a fully qualified file name, either UNC or relative drive (e.g., C:\...) if a local file. You may not be formatting your command correctly to marry the file name to the directory, but at the point of execution you will need to specify explicitly the filename in a way that SQL Server can find it and perform the command.
The mind reading version of SQL Server is still in the design stage, unfortunately.
September 23, 2008 at 7:00 am
if you look at the code i posted , i am pinting it to the complete path (drive and all the way to the folder). can some body please help and let me know what i am doing wrong??
Thanks
Bobby
September 23, 2008 at 7:46 am
You need to replace "C:\i386" with the path to your log backups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 9:25 am
Hi,
Thanks, i will try it now
Bobby
September 23, 2008 at 9:51 am
Ok I'll bite, but please don't email me outside the forum.
I think I can see a few problems with the additions you have tried to make to this script. I'm prepared to be corrected.
First of all, as most other posters have pointed out, you need to change c:\i386\ to the folder name where your trn files live. The error message you posted shows that the script is not finding any trn files.
That is: restore LOG IT From Disk ='Y:\#ECM-ZIP\ECMDB03\UNZIP-TRN\ITThe system cannot find the file specified' shows there is no filename after the IT. Make sure the Y drive is not a mapped drive that the server knows nothing about - fully qualify it.
This needs to be done in both places that the path is mentioned and i think both need to end with
The error message should also tell you that the parts of the script that you added in about adding the no recovery are in the wrong place (just noticed they are commented out too). This should be added after you set @sqlcmd = 'restore....blah blah and before you do the raiserror (is this just to list out the sqlcmd string?)
If in doubt, just remove every thing from --do stuff downwards and add a line to select * from @trn and run that as a query.
You should get a list of your filenames to show you are on the right track.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply