Restore existing database in SQL 2005

1)Open Microsoft SQL Server Studio, Login with your SQL Credentials or using Windows Authentication mode.
2)Create New Database with name “DatabaseName”, Set path for MDF and LDF files on your local computer.
3) Right click on the created databse("DatabaseName") in Object Browser Window and select Task->Restore->Database option. You will get restore database wizard opened.
4) On General Menu, select radio option of “From Device”, then select backup file from browse file option.
5) After successfully selection of Backup file, Goto Options menu from left navigation pane and Mark “Overwrite Existing Database” to true.
6) Click OK

If you want to create database diagrams for restored database, you will get the following error.

Error: Database Diagram Support Objects cann't be installed because this database doesn't have a valid owner.



Solution: By default restored database consider system login ID as database owner. We need to change the database ownership manually, back to the owner that originally created the database when diagrams created. To accomplish this run the following query in SQL query analyzer

--Replace "DatabaseName" with your database name
USE DatabaseName--(change this Database name)
GO

--Following query will change owner name
EXEC sp_changedbowner 'sa';
GO

You can look at the current wner to the database by running the following query

EXEC sp_helpdb;
GO