April 26, 2011 at 2:59 am
Is it possible to restore a .bak file from sql 2005 to a new database if the original db is no longer available?
April 26, 2011 at 5:03 am
jroberts-758537 (4/26/2011)
Is it possible to restore a .bak file from sql 2005 to a new database if the original db is no longer available?
Yes, it is possible.
It not necessary for the original database to exist in the server to restore it from it's backup.
April 26, 2011 at 5:56 am
Absolutely possible. Pretty common task really. It's an easy way to get a database from one system to another. When you run the restore operation you'll probably have to use the MOVE option to put the database files into the appropriate place on your server, but that's about the hardest part of the operation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2011 at 6:48 pm
Thanks for your replies. To restore it, is the easiest way to open management studio, connect to server, right click and choose 'Restore' and follow the prompts?
Are there any traps to look out for?
April 26, 2011 at 8:28 pm
should be pretty straight forward if you are not changing from 2005 to 2008. I usually create a blank database (no tables or anything). Then right click it and follow the restore wizard. I always choose to overwrite the existing database... i think that's the only thing that isn't checked by default.
Here is one pitfall. If you are moving this to a different server, your logins and usernames can get messed up a bit. So if you are backing up DB1 on ServerA... you probably have some logins on Server A... ie. Login1 and a matching user in the database. Then if you try to restore to Server B and server login Login1 already exist on the server you can get some issues. Even though you'll see the username Login1 on your newly restored database and a matching login on the new server, they aren't linked to each other. Here's MS explanation and a very easy fix.
basically run the report to see if you have any orphaned users and then fix them as the article says
http://msdn.microsoft.com/en-us/library/ms175475(v=SQL.90).aspx
April 26, 2011 at 9:31 pm
jroberts-758537 (4/26/2011)
Thanks for your replies. To restore it, is the easiest way to open management studio, connect to server, right click and choose 'Restore' and follow the prompts?Are there any traps to look out for?
Yes, using management studio UI is the easiest way to restore a database from backup.
As others have mentioned, restoring a database is common task for a DBA.
If you are not familiar/comfortable with restroing, practice it on a test machine until you feel comfortable and confident.
There should not be any trap. Be familiear with REPLACE and MOVE options.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply