Post reply

Restore script is not working

  • 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

  • June 15, 2023 at 5:07 pm

    #4212107

    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.

  • June 15, 2023 at 6:41 pm

    #4212151

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

    Can't Restore DB – SQLServerCentral Forums

  • June 16, 2023 at 2:02 pm

    #4212710

    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.

     

  • June 16, 2023 at 4:19 pm

    #4212777

    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

  • June 16, 2023 at 7:07 pm

    #4212864

    Thank you! It works.

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

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