sp_databaserestore: "stopat" gives error "no database backup found"

Question

Unfortunately I get:

“No backups for “database xxx” found in “\xxx\xxx$\…”

as soon as I add the @StopAt = ‘2019-03-27T04:05:20 Parameter.

When I comment this Parameter, everything works fine, except that only the 1st logfile after the full backup gets restored – and not all log Backups in the same backup Directory.

Also tried with the date Format in the scripts example: @StopAt = 20192703040520, but I get same error. What am I doing wrong ? Name of Backup files (trn) is like this: tools_backup_2019_03_27_110112_9746376.trn

added 28032019:

First of all thanks for your answers so far !!!

SQL Server version: 2016 standard (13.0.5216.0)

@StopAt = '20190328100000', (set to correct format ? server default language is: german !

this query on same server:

SELECT SYSDATETIME()  
    ,SYSDATETIMEOFFSET()  
    ,SYSUTCDATETIME()  
    ,CURRENT_TIMESTAMP  
    ,GETDATE()  
    ,GETUTCDATE();  

gives this result:

2019-03-28 16:10:22.8098949 
2019-03-28 16:10:22.8098949 +01:00  
2019-03-28 15:10:22.8098949 
2019-03-28 16:10:22.807 
2019-03-28 16:10:22.807 
2019-03-28 15:10:22.807

My goal is to restore "point in time" to march, 28th, 10 am

exact statement:

EXEC dbo.sp_DatabaseRestore 
        @Database = 'tools', 
        @BackupPathFull = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\', 
        @BackupPathDiff = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @BackupPathLog = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\', 
        @RestoreDiff = 0,
        @ContinueLogs = 1, 
        @RunRecovery = 1,
        @TestRestore = 0,
        @StopAt = '20190328100000',
        @RunCheckDB = 0,
        @Debug = 1,
        @help = 0,
        @Execute = 'N';

...changing setting for @ContinueLogs, @RunRecovery or @Execute doesn't change anything, error (still):

Nachricht 50000, Stufe 16, Status 1, Prozedur sp_DatabaseRestore, Zeile 486 [Batchstartzeile 0] No backups for "tools" found in "\dicvm.de\backupsql$\SV10RXVDB24\tools\"

files in @BackupPathFull directory are as follows (full- and log-Backups only, no diff Backups):

Verzeichnis von \\dicvm.de\backupsql$\SV10RXVDB24\tools

28.03.2019  11:01    <DIR>          .
28.03.2019  11:01    <DIR>          ..
27.03.2019  05:01            14.848 tools_backup_2019_03_27_050103_2371617.trn
27.03.2019  11:01            13.824 tools_backup_2019_03_27_110112_9746376.trn
27.03.2019  17:01             9.728 tools_backup_2019_03_27_170102_2790723.trn
27.03.2019  22:01         1.297.920 tools_backup_2019_03_27_220002_2539287.bak
27.03.2019  23:01            14.336 tools_backup_2019_03_27_230102_5656503.trn
28.03.2019  05:01            13.312 tools_backup_2019_03_28_050103_8892863.trn
28.03.2019  11:01            12.800 tools_backup_2019_03_28_110103_0664649.trn

@laughingVergil: So I assume there is a full backup - earlier than the @StopAt parameter's time as of 27.03.2019 22:01 - in the target directory (?) And: Yes: Your assumption is right: I am using Brent Ozar's First Responder Kit in the newest version.

Answer 1

I don't think the problem is with sp_DatabaseRestore but there are alternatives as you can see on this question (includes the script):

How do I create an automated SQL Server restore script for multiple backups?

Before you run your restore:

1) you must check that all your backups exist in the server

2) you must check the total disk space required by the restored-to-be-database is available on your drives

3) who is currently using the database - you will need exclusive access to it, if it already exists

4) I also save synonyms, triggers, indexes and permissions before I do the restore, so that I can re-apply these things later on if I need to.

5) questions for you: Are you using the WITH MOVE option to ensure the database files are allocated to specific drive(s) / mount-points which have enough free space?

Also, look into the use of WITH RESTART for your restores...

The backup or restore was aborted - what could it be?

Answer 2

I assume you are using the sp_DatabaseRestore procedure found in Brent Ozar's First Responder Kit (a great resource, BTW!), and will be answering based on that assumption.

This error happens when either of two things are true:

1. There is no full backup in the target directory at all
2. There is no full backup earlier than the @StopAt parameter's time in the target directory.

If you are restoring only log files with a @StopAt Parameter, omit the @BackupPathFull parameter (or set it to NULL) to prevent this error.

Edit: I also note that your time parameter in the backup file - 2019_03_27_110112 - is incorrectly formatted. The procedure uses an nvarchar(14) parameter, but your format is 17 characters long. You will need to either edit the proc to handle longer datetime literals, or change the date formats in your backups.