locked
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:



    https://www.mssqltips.com/sqlservertip/5465/automating-a-sql-server-database-refresh/



    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

    Tuesday, November 5, 2019 8:08 AM

All replies

  • Hi Justin_DBA,

     

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

     

    I don't think this kind of function can be achieved through scripting. Maybe you can  use some three-party restore tools, but most of them are charged, maybe someone else can provide better suggestions.

     

    Best regards,

    Dedmon Dai


    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 MSDNFSF@microsoft.com

    Wednesday, November 6, 2019 6:44 AM