Restore script is not working

Question

Post reply

Restore script is not working

adisql

SSCrazy

Points: 2893
More actions
June 15, 2023 at 4:46 pm

#4212068

Below script is not working .Not sure what is wrong in it. Any suggessions pls.

--get the last backup file name and path
Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)
Declare @DT datetime
set @FileName = null
set @cmdText = null
set @BKFolder = '\\xxxxx\xxxxx\'
set @DT = getdate()
create table #FileList (
FileName varchar(255),
DepthFlag int,
FileFlag int
)
--get all the files and folders in the backup folder and put them in temporary table
insert into #FileList exec xp_dirtree @BKFolder,0,1
--select * from #filelist
--get the latest backup file name
select top 1 @FileName = @BKFolder + FileName from #FileList where Filename like '%.bak' order by filename desc
select @filename

--execute the restore
exec('
RESTORE DATABASE [xxxxx] FROM DISK = ''' + @filename + '''
WITH MOVE N''xxxx'' TO N''F:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\''' + @DT + '''.mdf'',
MOVE N''xxxx_Log'' TO N''L:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\''' + @DT + '''.ldf'', NOUNLOAD, REPLACE, STATS = 10')

drop table #FileList

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

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

ratbak Default port Points: 1418 More actions · Answer 1

Can you be more specific about what specifically is not working, and what you mean by "not working"?

Does it return an error? If so, at what point and what error?

You probably need to explicitly convert/format your @dt variable from datetime to desired string format.

Ken McKelvey SSCoach Points: 18930 More actions · Answer 2

It is a lot easier to get recent backups from the tables in msdb:

Can't Restore DB – SQLServerCentral Forums

adisql SSCrazy Points: 2893 More actions · Answer 3

Yes, I tried changing the @dt variable from datetime to another string format, but the script doesn't seem to be able to restore.

To add the datetime to mdf and ldf files is what I'm aiming for.

DNA_DBA SSCrazy Eights Points: 9163 More actions · Answer 4

You're trying to concatenate a date with a varchar which doesn't end up well unless you specify the format of the date that you want.

Also you have too many quotes I think.

Change the restore part to something like this:

--execute the restore
declare @dt2 varchar(100)=CONVERT(varchar, @dt, 112)

EXEC ('
RESTORE DATABASE [xxxxx] FROM DISK = ''' + @filename + '''
WITH MOVE N''xxxx'' TO N''F:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @dt2  + '.mdf'',
MOVE N''xxxx_Log'' TO N''L:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\'+ @dt2 + '.ldf'', NOUNLOAD, REPLACE, STATS = 10'
)

That should hopefully get you working but as mentioned previously, there are better ways of scripting it

adisql SSCrazy Points: 2893 More actions · Answer 5

adisql

SSCrazy

Points: 2893

June 16, 2023 at 7:07 pm

#4212864

Thank you! It works.

Restore script is not working

Cookies on SQLServerCentral