title: Dynamic restore multiple databases from a folder
body: <p>I am tasked with restoring several SQL Server databases from <code>.bak</code> files generated from another server in another organization. I need to be able to add automation to this process as it will have to occur daily. </p>
  
  <p>The challenge I am having is that the <code>.bak</code> files have different names every day, like: <code>DatabaseName_Full_Backup_Date_Time_xxxxxx.bak</code>. Also the data and log files need to be moved (<code>MOVE</code>) during the restore to my server's data and log file locations.</p>
  
  <p>I decided to use T-SQL, hoping to find a robust solution that could be implemented from a SQL Server Agent scheduled job that restores the databases, applies the original permissions, and moves\drops the old <code>.bak</code> files to an archive location.</p>
  
  <p>I tried solving this with PowerShell but the <code>-relocate</code> file did not work. Also tried this stored procedure (<code>dbo.spRestore_All_Backups_From_Folder</code>) proposed <a href="https://stackoverflow.com/questions/19428189/best-script-to-restore-multiple-databases-with-sql-server-2012">on this website here</a> which is very close to what I need but in my SQL Server 2016 machine, I get an error when restoring backups generated from another server. </p>
  
  <p>The script works with <code>.bak</code> files from databases that have been backed up on the same server and no MOVE of files is needed.</p>
  
  <p>The error I get when I attempt a restore of a <code>.bak</code> file from another server is:</p>
  
  <blockquote>
    <p>Msg 213, Level 16, State 7, Line 300<br>
    Column name or number of supplied values does not match table definition. </p>
    
    <p>Msg 3013, Level 16, State 1, Line 300<br>
    RESTORE FILELIST is terminating abnormally.</p>
  </blockquote>
  
  <p>Thank you for all input and advice!</p>