Restoring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring

Author

Topic

honey_191
Starting Member

49 Posts

Posted - 2008-02-04 : 19:28:01

while iam restoring my back up i got this error message

Msg 3117, Level 16, State 1, Procedure res, Line 26
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Procedure res, Line 26
RESTORE DATABASE is terminating abnormally.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-04 : 20:12:12

Restore with move option to different location.

honey_191
Starting Member

49 Posts

Posted - 2008-02-04 : 22:03:11

Still im getting the error message

Msg 512, Level 16, State 1, Procedure res, Line 22
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 3234, Level 16, State 2, Procedure res, Line 28
Logical file '@lname' is not part of database 'AdventureWorks'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure res, Line 28
RESTORE DATABASE is terminating abnormally.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-04 : 22:24:00

What's your restore statement look like?

honey_191
Starting Member

49 Posts

Posted - 2008-02-04 : 22:58:29

restore database @db
from disk=@path
with stats=10,replace,
move '@lname' to 'd:\restore\'

honey_191
Starting Member

49 Posts

Posted - 2008-02-04 : 23:00:27

my program is :

create procedure res
as

declare @db as varchar(100)
declare @path as varchar(100)
declare @mid as int
declare @mid1 as int
declare @type as char(5)
declare @lname as varchar(100)

set @mid=(select media=max(media_set_id) from resto)

set @path=(select physical_device_name from resto where media_set_id=@mid)

set @db=(select database_name from resto where media_set_id=@mid)

set @type=(select typ from resto where media_set_id=@mid)

set @mid1=(select media=max(media_set_id) from resto1)

set @lname=(select logical_name from resto1 where media_set_id=@mid1)

if @type='D'
begin
restore database @db
from disk=@path
with stats=10,replace,
move '@lname' to 'd:\restore\'
end

else if @type='I'
begin
restore database @db
from disk=@path
with stats=10,norecovery,replace,
move '@lname' to 'd:\restore\'

end

else if @type='L'
begin
restore log @db
from disk=@path
with stats=10,norecovery,replace,
move '@lname' to 'd:\restore\'
end

else
print'operation failed'

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-04 : 23:42:35

Ensure you get correct value for @lname, and need file name after d:\restore\.

honey_191
Starting Member

49 Posts

Posted - 2008-02-05 : 00:01:37

can you tell me any changes in the program, @lname has to take from the system tables automatically

honey_191
Starting Member

49 Posts

Posted - 2008-02-05 : 00:08:34

can you tell me any changes in the program, @lname has to take from the system tables automatically

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-05 : 01:31:31

You shouldn't grab @lname from the system tables. You should grab the information directly from the backup file via RESTORE FILELISTONLY.

Here's a restore sproc that I wrote. It should help you out:
http://weblogs.sqlteam.com/tarad/archive/2005/11/08/8262.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

honey_191
Starting Member

49 Posts

Posted - 2008-02-05 : 02:49:18

In that stored procedure we have to give the i/p parameters.

My problem is.I need to create 2 jobs
one job is to take the backup of a specific database & type(full,differential,log),2nd step of the 1st job is to call the 2nd job,which is going to restore the latest backup(we r not giving the databasename,it has to take automatically from the system tables) at another instance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-05 : 11:07:37

I'm not saying to use my stored procedure. I'm saying to look at the code to see where you've gone wrong.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

honey_191
Starting Member

49 Posts

Posted - 2008-02-05 : 18:30:38

Still im getting this error messages

Msg 512, Level 16, State 1, Procedure res, Line 22
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 3117, Level 16, State 1, Procedure res, Line 45
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Procedure res, Line 45
RESTORE LOG is terminating abnormally.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-05 : 19:29:07

You are getting that error due to one of your set statements returning more than one value. That is not allowed. Fix your code so that this is not the case or change the format to:

SELECT @var1 = MAX(SomeColumn)
FROM YourTable

or similar.

But I'll repeat that you shouldn't be getting this information from the system tables, but rather get the data from RESTORE FILELISTONLY as shown in my stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources