Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Restore script not working Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 9:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
Also, if that insert statement is commented out, you'll never enter the WHILE loop.
Post #1234115
Posted Wednesday, January 11, 2012 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 15, Visits: 220
I feel really stupid :)

I have been looking at it all day and never saw the insert statement commented out.

Thanks!!

There are some more problems now but I will look at it tomorrow,
Looks like the variable substitution is failing.


Thanks all so far!!

Msg 5105, Level 16, State 2, Line 102
A file activation error occurred. The physical file name '@data_file_path@restore_as@mdf_extension' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 3156, Level 16, State 3, Line 102
File 'U4S33_Data' cannot be restored to '@data_file_path@restore_as@mdf_extension'. Use WITH MOVE to identify a valid location for the file.
Msg 5105, Level 16, State 2, Line 102
A file activation error occurred. The physical file name '@data_file_1_path@restore_as@ndf_extension' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 3156, Level 16, State 3, Line 102
File 'U4S33_Data1' cannot be restored to '@data_file_1_path@restore_as@ndf_extension'. Use WITH MOVE to identify a valid location for the file.
Msg 5105, Level 16, State 2, Line 102
A file activation error occurred. The physical file name '@log_file_path@restore_as@ldf_extension' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 3156, Level 16, State 3, Line 102
File 'U4S33_Log' cannot be restored to '@log_file_path@restore_as@ldf_extension'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 102
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 102
RESTORE DATABASE is terminating abnormally.
Post #1234123
Posted Wednesday, January 11, 2012 9:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
No worries, done it plenty of times myself :)

Your set statements are just setting your paths to e.g. '@data_file_path@restore_as@mdf_extension'
Drop all the ' from them and you should be ok.
Post #1234140
Posted Thursday, January 12, 2012 12:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 15, Visits: 220
Indeed that does seem to solve the problem except for 1 thing:

Msg 8114, Level 16, State 12, Line 103
Error converting data type varchar to nvarchar.

it seems to be here:

RESTORE DATABASE @restore_as
FROM DISK = @full_backup_path WITH FILE = 1,
MOVE 'U4S33_Data' TO @data_file_full_path,
MOVE 'U4S33_Data1' TO @data_file_1_full_path,
MOVE 'U4S33_Log' TO @log_file_full_path


I tried setting @restore_as to NVARCHAR but that didn't work.

I then added these satements to the script

print @data_file_full_path
print @data_file_1_full_path
print @log_file_full_path


Results:

D:\Data\25000994.ndf
D:\Logs\25000994.ldf

The first SET statement is not shown.(.mdf file) So I think the problem is there.

Cheers,

Cor
Post #1234545
Posted Thursday, January 12, 2012 12:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 15, Visits: 220
It turned out there was a typo in the

SET @ndf_extension = '.ndf' statement.

everything works now

Thanks to all for your help!

Much appreciated!

Cheers,

Cor
Post #1234549
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse