Sql-server – sp_databaserestore: “stopat” gives error “no database backup found”

backupsql server

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:


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

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.

Best Answer

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?