Restore script not working

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Restore script not working

15 posts, Page 2 of 2

««1 2

Restore script not working

Rate Topic

Display Mode

Topic Options

Author

Message

Gazareth

Posted Wednesday, January 11, 2012 9:26 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

Also, if that insert statement is commented out, you'll never enter the WHILE loop.

Post #1234115

cor_perlee

Posted Wednesday, January 11, 2012 9:32 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 5:44 AM
Points: 15, Visits: 179

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

Gazareth

Posted Wednesday, January 11, 2012 9:46 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

cor_perlee

Posted Thursday, January 12, 2012 12:32 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 5:44 AM
Points: 15, Visits: 179

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

cor_perlee

Posted Thursday, January 12, 2012 12:47 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 5:44 AM
Points: 15, Visits: 179

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 »

15 posts, Page 2 of 2

««1 2

Permissions