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='\\oldserver\Backup\oldserver\ERPSysDB\FULL,\\oldserver\Backup\oldserver\ERPSysDB\DIFF,\\oldserver\Backup\oldserver\ERPSysDB\LOG', @datadir='D:\SQLServer\DATA', @logdir='L:\SQLServer\DATA'; --Do various cleanup/post processing steps in ERPSysDB here EXEC master..RestoreDB @database='ERPCompanyDB', @source='\\oldserver\Backup\oldserver\ERPCompanyDB\FULL,\\oldserver\Backup\oldserver\ERPCompanyDB\DIFF,\\oldserver\Backup\oldserver\ERPCompanyDB\LOG', @datadir='D:\SQLServer\DATA', @logdir='L:\SQLServer\DATA'; --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='\\oldserver\Backup\oldserver\ERPCompanyDB\FULL,\\oldserver\Backup\oldserver\ERPCompanyDB\DIFF,\\oldserver\Backup\oldserver\ERPCompanyDB\LOG', @stopat='2018-11-01T08:00:00' @with='MOVE ''ERPDataFile'' TO ''D:\SQLServer\DATA\ERPCompanyTestDB.mdf'', MOVE ''ERPLogFile'' TO ''L:\SQLServer\DATA\ERPCompanyTestDB.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).