I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp directory. What I'm looking for is a way to mass restore those .bak files so that I don't have to spend all weekend doing it manually.

I found a decent stored procedure here : SQL Restore

I put it in place and tested and I'm getting the following error:

Msg 3118, Level 16, State 1, Line 1
The database "TF058" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I'm running SQL Server 2012 and the databases are in FULL recovery mode.

You can use

  1. sqlmigration - Start-SqlServerMigration.ps1

    This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or migrated. This script works with named instances, clusters and SQL Express.

  2. Restore Genie - it has both PowerShell and t-SQL version.

I have used both and they are life savers. Highly recommend to test them first. First one is the one that will help in your situation.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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