Sql-server – use a script to restore multiple SQL databases from .bak files

restoresql serversql-server-2012

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.

Best Answer

You can use

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

Related Solutions

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Sql-server – media family on device is incorrectly formed. SQL Server cannot process this media family

If you are attempting to restore newer database to older SQL version you can always simply manually create the database in the older SQL version and utilize the task - generate scripts and include the data.

Save to File --> Advanced--> Types of data to script --> Schema and data.

Also make sure to Script for Server Version.

This might be the only supported option if you're trying to go from newer SQL Server to older version.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Restore

Sql-server – media family on device is incorrectly formed. SQL Server cannot process this media family

Related Question