Need a Script to Automate DB Restore RRS feed

  • Question

  • Hi all,

    Good day!

    Let me take a moment to explain what I am looking for. 

    I am purely into DBA management activities and never written a script in my tenure. I have been assigned to production database migration. We have about 5K to 6K small, medium and large sized databases which needs to backed up from SQL 2008 R2 / 2012 and then restored on SQL 2016 servers.

    The process which I am following right now is manual - take a backup and restore it on the new server. Then change the compatibility mode, remove orphaned users and give permissions on the new DBs and finally complete DBCC CHECKDB ().

    This process may look simpler and tidy, however, when there are endless number of databases in question, then this process may not seem feasible. No matter how fast we try to do this, it will end up eating a lot of time (weeks) since we only have 2 DBAs to perform this activity.

    I searched on google to find out if there are any scripts which I can use and I tried the below one:

    The only limitation with this script is that, it can only restore the DB, if we define the Disk Drive on target server. In our environment, the space on the Disks keep on changing and I want to find a script that can query the Server's Disk space first and then move the data files in those drives, which have sufficient space. 

    I am able to create SQL agent job to take backups. But I am wondering if anyone could help me in figuring out a way to Automate the RESTORE process as below:

    1. Query the disk space on Target server to find which disk has enough space to accommodate .mdf', .ndf & .ldf
    2. Calculate/Verify if the current DB backup can fit into any of the disks - without defining the disk name/letter. If yes, then restore the database.
    3. Create a SSIS job to include all the DBs which need to be backed up/migrated.

    Let me know if you need any other information from my side.

    Thanks in advance!

    Justin L

    Wednesday, November 13, 2019 6:53 AM

All replies

  • No solution, but perhaps a useful pointer: Powershell seems like the obvious solution for this. Use for instance Copy-DbaDatabase cmdlet, found in There might, or might now, be functionality to handle disk space, but then you are in for some PS coding. However, if you create (find on the internet) a skeleton code that would work for you, it is likely that you will get help for that part in a suitable powershell forum.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 13, 2019 7:57 AM
  • Hi Justin_DBA,

    Please do not post duplicate thread in same forum. I find the same thread that you posted. By the way, for Power shell Script, I suggest you open a thread in Power shell forums, People there will help you more effectively. 
    Hope this could help you.

    Best regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact

    Thursday, November 14, 2019 9:14 AM