February 18, 2009 at 4:59 am
Hi,
I have a backup file and i want to restore it. I have writen the script but when i executed it i am getting error. Please let me know why i am getting error is there any problem in script, please correct and revert back to me.
Thanks in advance.
Script for restoring :-
declare @path nvarchar(200)
declare @LocationCode nvarchar(10)
Declare @LatestFileName nvarchar(max), @sqlstr nvarchar(max)
set @path='E:\MSSQL\Backups\'
set @LocationCode='Hosp'
--- Restoring database from backup file
set @LatestFileName='Hosp_backup_full.bak'
set @sqlstr='exec master.dbo.xp_restore_database
@database = N''Rollout_'+ @LocationCode +''',
@filename = N'''+ @Path + @LatestFileName +''',
@filenumber = 1,
@with = N''RECOVERY'',
@with = N''NOUNLOAD'',
@with = N''STATS = 10'',
@with = N''REPLACE'',
@with = N''MOVE N''''Tran'+ @LocationCode +''''' TO N''''E:\MSSQL\Data\Rollout_'+ @LocationCode +'.mdf'''''',
@with = N''MOVE N''''Tran'+ @LocationCode +'_Data'''' TO N''''E:\MSSQL\Data\Rollout_'+ @LocationCode +'_Data.ndf'''''',
@with = N''MOVE N''''Tran'+ @LocationCode +'_Index'''' TO N''''E:\MSSQL\Data\Rollout_'+ @LocationCode +'_Index.ndf'''''',
@with = N''MOVE N''''Tran'+@LocationCode +'_log'''' TO N''''E:\MSSQL\Logs\Rollout_'+ @LocationCode +'_log.ldf'''''''
exec sp_ExecuteSql @sqlstr
Error :
SQL Server has returned a failure message to LiteSpeed which has prevented the operation from succeeding.
The following message is not a LiteSpeed message. Please refer to SQL Server books online or Microsoft technical support for a solution:
RESTORE DATABASE is terminating abnormally.
Logical file 'Hosp' is not part of database 'Rollout_Hosp'. Use RESTORE FILELISTONLY to list the logical file names.
February 18, 2009 at 5:19 am
Print the sql string before you execute it and check it for any errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 5:22 am
i am getting error after when i am executing this script
February 18, 2009 at 6:03 am
Logical file 'Hosp' is not part of database 'Rollout_Hosp'. Use RESTORE FILELISTONLY to list the logical file names.
this looks like your problem. does the database you took the backup from have a file with the logical name Hosp? you need to restore the database with the same logical names. you can change them after the restore has completed.
February 18, 2009 at 7:03 am
prakash414 (2/18/2009)
i am getting error after when i am executing this script
I saw that. I'm asking what the sql statement looks like before it's executed. Use PRINT to see the string's value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 7:05 am
dude
substitute
exec sp_ExecuteSql @sqlstr
for
Print @sqlstr
and post the output
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 18, 2009 at 7:13 am
What Perry and Gail are looking for is the actual command that might be failing. What you want to do is get the command working, and then work backwards, looking at how the string (which is the command) gets built and fix that.
Get the actual SQL you want to execute working first, then you can make it dynamic with the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply