RESTORE JOB FAILS

Question

Post reply

RESTORE JOB FAILS

Tushar_IDFC

SSC Veteran

Points: 287
More actions
June 17, 2009 at 3:29 am

#206030

Hi,
I'm trying to restore few Databases from Production to Test using Restore Database command in a Proc over UNC path which is scheduled as a job.
The Job fails, and the following error is logged in the error Log
Thread creation failed. Running BACKUP/RESTORE cleanup.,0
Any help on this would be much appreciated.
I'm able to run the same procedure from Query Analyzer without any issues and completes the restoration successfully.
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi,

Can you post the code here.

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

Tushar_IDFC SSC Veteran Points: 287 More actions · Answer 2

Hi,

Here is the Code for the Proc:

CREATE procedure restoreDB_prodTest

as

begin

DECLARE @@FLAG CURSOR

DECLARE @@LOC INT

DECLARE @@FILENAME VARCHAR(500)

DECLARE @@DBNAME VARCHAR(50)

DECLARE @@path VARCHAR(500)

DECLARE @@fullpath VARCHAR(500)

DECLARE @@DBFILE VARCHAR(50)

DECLARE @@Restorepath VARCHAR(500)

DECLARE @@DBLogicalName VARCHAR(500)

DECLARE @@LogLogicalName VARCHAR(500)

DECLARE @@DBName_PATH VARCHAR(500)

DECLARE @@LogName_PATH VARCHAR(500)

DECLARE @CommandLine Varchar(500)

create table #temp_table1(

filename1 varchar(200),

filename2 varchar(200),

filename3 varchar(200),

filename4 varchar(200),

filename5 numeric(18,2),

filename6 numeric(18,2)

)

create table #temp_dir(

filename varchar(200)

)

set @@path = '\\10.182.1.60\sql_daily_backup\'

set @@RestorePAth = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\'

insert into #temp_dir

exec master.dbo.xp_cmdshell "dir \\10.182.1.60\sql_daily_backup\*.bak /B /A:A"

delete from #temp_dir where filename is null

SET @@FLAG = CURSOR FOR

select CHARINDEX('_',FILENAME) AS INDEXTAG, * from #temp_dir

OPEN @@FLAG

FETCH NEXT FROM @@FLAG INTO @@LOC, @@FILENAME

WHILE @@FETCH_STATUS =0

BEGIN

SELECT @@DBNAME = LEFT(@@FILENAME,@@LOC-1) FROM #TEMP_DIR where filename = @@filename

set @@fullpath = char(39) + @@PATH + @@FILENAME + char(39)

insert into #temp_table1

execute('restore filelistonly FROM DISK = '+ @@FULLPATH )

select @@DBLogicalName = filename1 from #temp_table1 where filename3 = 'D'

select @@LogLogicalName = filename1 from #temp_table1 where filename3 = 'L'

set @@DBName_PATH = @@RESTOREPATH + @@DBNAME + '_Data.MDF'

set @@LogName_PATH = @@RESTOREPATH + @@DBNAME + '_Log.LDF'

Set @@FULLPATH = replace(@@FULLPATH,char(39),'')

Set @CommandLine = 'RESTORE DATABASE [' + @@DBNAME + '] FROM DISK = ''' + @@FULLPATH + ''' WITH '

Set @CommandLine = @CommandLine + 'MOVE ''' + @@DBLogicalName + ''' TO ''' + @@DBName_PATH + ''','

Set @CommandLine = @CommandLine + 'MOVE ''' + @@LogLogicalName + ''' TO ''' + @@LogName_PATH + ''', RECOVERY'

EXEC(@CommandLine)

truncate table #temp_table1

FETCH NEXT FROM @@FLAG INTO @@LOC, @@FILENAME

END

CLOSE @@FLAG

DEALLOCATE @@FLAG

DROP TABLE #TEMP_DIR

end

Chand00 SSCertifiable Points: 6466 More actions · Answer 3

Give a try with sp_executesql instead exec. Meanwhile I will check.

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

ps. One Orange Chip Points: 29422 More actions · Answer 4

What is the max number of threads on ur server?

Pradeep Singh

Tushar_IDFC SSC Veteran Points: 287 More actions · Answer 5

Hi,

255 Threads on the server.

max worker threads,32,32767,255,255

Have tired using sp_executesql but no luck.

Thanks

Tushar_IDFC SSC Veteran Points: 287 More actions · Answer 6

Hi,

After a longtime I see there is no reply from the Gurus here, to help me tide over this issue. But just to inform all here since the last post the proc had been running smooth and today ..... same old error.

Thread creation failed. Running BACKUP/RESTORE cleanup.,0

Any input will be great. Thanks for your replies.

Regards,

Tushar