Errors upgrading SQL Server 2005 to SQL Server 2008
Posted by scmay on June 26, 2009
I have an existing SQL Server 2005 database which I would like to use in SQL Server 2008 database.
Everyone knows there are 2 ways to do this. Attach a database ( you need mdf and ldf file ) or Restore from a backup file (thus you need .bak file)
As much as I tried attaching the database, I could not update my login ID in the database (using the conventional sp_change_users_login ‘Auto_Fix’, ‘MyLoginName’)
So, I tried restoring the database. Steps followed are from here
I hit an error at –Restore Database step
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘MyDatabaseName’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Which brings me to this link
So the fix for me is
USE master
ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyDatabaseName
FROM DISK = ‘C:\MyDatabaseName.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:\DataYourMDFFile.mdf’,
REPLACE
