You have backed up your database on your remote server into .bak file. You moved the .bak file to your local system and want to restore the database and it does not work!
Well here are some of the things you need to be aware of.
- You can back up a database and restored as a different database name on one computer
- You can not back a database on one computer and restore it on another using a different name.
The second was the scenario that I was trying to do as I did not want to overrwite my existing database. This is probably a security issue when you import from one machine to the other. You can not change the database name.
You are working on a development machine. You made some changes to the database. Meantime, the production machine got new data. You want to import the new data into the existing one.
If you simply import the database, you will loose all your changes (stored procedures, new column etc). What you really want to do is import just one or two tables which has the new data.
To make things complex, you can not import individual table because the data in those table is somewhat corrupt (this was the case with me).
As earlier pointed out, it would be nice if we can import the .bak file into a new database on local system but we can’t. The only way to do it is update the current database by right clicking on the database->Task->Restore->Database. You can not change the name of the database, if you do you will get some error.
What you can do is backup you existing database and you can restored it with different database name. This is what you should do first. Back it up and restore it with a different name. That way the changes you made are their in a new database which you can always refer too.
Note that the database permission do not play a role when you import/export from remote computer. Also make sure you give everyone the permission to read/write and execute for this to work.
This tutorial is designed for Microsoft SQL Server 2005. It should be valid for SQL Server 2008.
How to backup and restore database
How to restored database across servers
How to backup database across different machines