Restore a database by pointing to one or more directories containing backup files and specifying a STOPAT time [duplicate]

I’m working on prepping/testing migration of our ERP and other auxiliary systems to a new database server. This will involve restoring database backups from the old server onto the new one.

For the sake of convenience, I’d like to be able to use a script/stored procedure to restore a database by simply pointing to one or more directories containing the backup files (combination of full, differential, and log), and optionally specifying a STOPAT time, or omitting it and restoring to the most recent possible time given the backups present. I would also need to be able to specify new file locations (the MOVE option), either explicitly, or more generally “put all the data files here, and all the log files here”. The tool should look at all the files in the provided directories, and determine which ones need to be restored to reach the target time.

The GUI restore tool in Management Studio can do all of this, but that obviously can’t be included in a script. I’d like to be able to write a general migration script that would be conceptually similar to this:

EXEC master..RestoreDB
    @database='ERPSysDB',
    @source='\oldserverBackupoldserverERPSysDBFULL,\oldserverBackupoldserverERPSysDBDIFF,\oldserverBackupoldserverERPSysDBLOG',
    @datadir='D:SQLServerDATA',
    @logdir='L:SQLServerDATA';

--Do various cleanup/post processing steps in ERPSysDB here

EXEC master..RestoreDB
    @database='ERPCompanyDB',
    @source='\oldserverBackupoldserverERPCompanyDBFULL,\oldserverBackupoldserverERPCompanyDBDIFF,\oldserverBackupoldserverERPCompanyDBLOG',
    @datadir='D:SQLServerDATA',
    @logdir='L:SQLServerDATA';

--More post-processing in ERPCompanyDB here

--Now restore a test copy of the database that we don't back up directly on the production server,
--using production database backups instead:
EXEC master..RestoreDB
    @database='ERPCompanyDB',
    @restoreas='ERPCompanyTestDB',
    @source='\oldserverBackupoldserverERPCompanyDBFULL,\oldserverBackupoldserverERPCompanyDBDIFF,\oldserverBackupoldserverERPCompanyDBLOG',
    @stopat='2018-11-01T08:00:00'
    @with='MOVE ''ERPDataFile'' TO ''D:SQLServerDATAERPCompanyTestDB.mdf'', MOVE ''ERPLogFile'' TO ''L:SQLServerDATAERPCompanyTestDB.ldf''';

--Additional post-processing, database restores, etc...

Are there any existing tools that provide this functionality? I’m prepared to write my own, but it would be a fairly intricate chunk of code (particularly error/exception handling), so I don’t want to reinvent the wheel. I’d prefer this to be usable within T-SQL, i.e. not PowerShell cmdlets, but CLR stored procedures would be fine (and quite possibly the easier/better way to implement this).

Here’s what I put together last week, in lieu of finding anything else already in this niche.

https://github.com/dave-britten/RestoreDB

Usage is fairly simple (though there are a number of options to customize). For example, my test server reload script can just do something like this to restore the Dynamics GP system database:

EXEC master.dbo.RestoreDB @Source='+\oldserverBackupoldserverDYNAMICS', @Database='DYNAMICS', @AutoMove=1, @Replace=1

That will restore the database DYNAMICS to the latest available point in time, and automatically move all database files to the instance default data and log directories.

Two other useful options are @RestoreAs and @AutoRename, which, when used in combination, allow for easily restoring the database under a different name (e.g. a test copy), with the database files being automatically renamed (replace the old database name with the new database name).

Currently, RestoreDB requires at least SQL Server 2016, but I’d like to get it working with 2012 and later.

If you want to use tsql then use – sp_RestoreGene . Its an amazing SP that will do exactly what you are looking for.

This procedure queries msdb database backup history and database file details in master.
It builds and returns RESTORE DATABASE commands as its result set, it does not execute the commands.

Note: I agree with Andy that you should explore dbatools as it has some advanced restore functionality.