Sql-server – Restoring and upgrading multiple databases

restoresql serverupgrade

I have a SQL Server 2005 instance on my PROD server. I have been given a task to migrate databases from that instance to a different box (SQL Server 2008 R2 on Windows 2008 R2). The current instance has a maintenance plan set up, which backs up 8 different databases every night to a single .bak file.

How do I take that bak file and restore it onto the new SQL Server 2008 R2 instance? I have tried using SQL Server 2008 R2 SSMS, and set the restore from option to device and point it to that .bak file, but what should I target restore to since I have multiple databases to be restored in the new instance?

What is the best approach to migrate all databases in single shot to the new instance without doing one at a time?

Best Answer

First thing i would like to highlight is you should have the system backups in addition to user databases handy when you said you need to migrate from SQL2k5 to SQL2k8R2.

You can copy all the latest backups from the current server onto the Windows2K8 box you mentioned.

Once copied, the first thing you should be looking is restoring the system database backups as mentioned here Backup and Restore system databases

Doing the above will make sure all the logins, SSIS packages, jobs etc would be attached as well when migrating to new version.

Now, you should start restoring the user database backups copied from old server to this new box as guided in Restore database from existing full backups

In addition to above you can use Powershell to completely migrate all the databases in go as you mentioned , from here.

Also, you can refer Migrate SQL server from one machine to another

Related Solutions

Sql-server – How to restore multiple backups

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Sql-server – User Permissions Messed Up following a Backup -> Restore Operation

I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server.

The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions.

https://stackoverflow.com/questions/461385/restoring-a-backup-to-a-different-server-user-permissions

(Resource listed for that question http://support.microsoft.com/kb/918992)

It sounds like changing the server setting from Windows authentication to Mixed mode authentication fixed your problem, but just in case it didn't completely resolve the problem I thought this might be useful.

Best Answer

Related Solutions

Sql-server – How to restore multiple backups

Sql-server – User Permissions Messed Up following a Backup -> Restore Operation

Related Question