March 17, 2009 at 8:16 pm
Ok, what you could do is to declare two variables:
Declare @s-2 nvarchar(4000)
Declare @s1 nvarchar(4000)
After this line:
set @s-2 = @s-2 + '"' + @dfname + '" to "' + @newdatapath + @dbname + '_' + @dfname + '_data' + @ext + '"'', @WITH = ''move '
put this condition:
if len(@s) >=3900
begin
set @s1= @s-2
set @s-2= ''
end
in the end put
print @s1
print @s-2
you would need to change this line:
exec master..sp_executesql @s-2
in order to run it or you can just concatenate the two printouts together and run it manually.
Alternatively you can use declare @s-2 varchar(8000) and comment out
the exec string.
I am not sure if nvarchar(max) will work with sp_executesql, but you may try this as well.
March 17, 2009 at 8:36 pm
instead doing this can we do with a cursor or some CTE's ?? just in case.
March 17, 2009 at 8:42 pm
Yes you can. You can use a larger datatype to fit your script and then just print it out. However 8000 character should work for your immidiate needs as your original script is only 5000 characters.
March 18, 2009 at 5:57 am
Mike Levan (3/17/2009)
guess I don't entirely understand why you would need to worry about the file list. I suppose if you wanted the ability to restore a single file from the list it would be handy or if you wanted the ability to move those files to a different physical location. Personally, I spent a long time determining where my files would exist on my drives in order to get the best performance I could. That's starting on a different topic though. 😉
Kent
do you mean that i need not wory about filelist when restoring a backup having multiple ndf files?? that means i dont need to do a restore using " @WITH= " option.
I do not use the @WITH= option in my restores. SQL puts the files where the existing files are located. As I said, I've not used partitioned tables before so I do know how it deals with multiple files. I would suspect it handles them the same way it handles a DB that has only one data and one log file though.
March 18, 2009 at 5:20 pm
I understand if you restore from one server to another with different drive and folder names, you would need to specify "with move" option. If the folder and drive structure is the same, you can restore without this option.
March 19, 2009 at 5:37 am
Good call. I just went out and purchased a few TB drives for my dev box and partitioned them to mimic my production box. Couple hundred dollars in hardware was worth more than the time I'd have spent trying to write a restore script that would work on dissimilar hardware. Now, if you have a set up where you have dozens of drives I could see the desire to not try to mimic it on a dev box.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply