1

This question already has an answer here:

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).

marked as duplicate by hot2use, Max Vernon sql-server Nov 13 '18 at 17:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    Check out the dbatools PowerShell module: docs.dbatools.io/#Restore-DbaDatabase – AMtwo Nov 8 '18 at 16:11
  • @AMtwo That is a lovely module that I've used for things like finding orphaned (detached) database files, but ideally I'd like something I can easily use from a T-SQL script here. – db2 Nov 8 '18 at 16:39
  • Using T-SQL to reach out to the OS to to identify the file list, put them in the right order, and generate the script is certainly doable, but it's not straightforward. Based on your example file paths, you're using Ola Hallengren's backup solution, which dbatools Restore-DbaDatabase supports natively. You can write a few hundred lines of dynamic T-SQL, or one line of PowerShell. – AMtwo Nov 8 '18 at 19:09
  • @AMtwo Yup, we're using the Ola Hallengren scripts. I'm working on a script/procedure that will handle this - with the help of a lot of table variables, xp_dirtree, RESTORE HEADERONLY, and RESTORE FILELISTONLY - and I'll post it as an answer if nobody comes up with something more mature. – db2 Nov 8 '18 at 21:07
1

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='+\\oldserver\Backup\oldserver\DYNAMICS', @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.

1

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.

  • Neat! But as best I can tell, sp_RestoreGene requires the backup history in msdb, i.e. it doesn't scour a given path looking for any backup files it can find. That would limit its usefulness restoring across servers, which is what I'm doing here (though it would be considerably faster than my approach of looping through files with RESTORE HEADERONLY). – db2 Nov 12 '18 at 18:11
  • Not really, if you check the SP, it has @FromFileFullUNC - for full, @FromFileDiffUNC for diff and @FromFileLogUNC for log. This allows you to use UNC paths just like you want. See doc for more details - paulbrewer.wordpress.com/2016/08/05/restoregeneversion6 – Kin Shah Nov 12 '18 at 18:32
  • Okay, I see, you have to generate a restore script on the original server, then run it on the target server. I have to intervene with various post-processing steps between certain database restores, so it probably wouldn't be all that smooth for this situation. Looks like something I might be able to find a use for eventually, though. – db2 Nov 12 '18 at 19:13
  • Then Powershell is the way to go. It has PS version as well or just go with dbatools. You can invest time in modifying the SP so that it can execute as well instead of just printing it. Good luck ! – Kin Shah Nov 12 '18 at 19:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.