April 26, 2012 at 12:36 pm
I've been asked to create a 2008 R2 database from a SQL Server 2000 .bak file (different box).
I'm familiar with copying a database from one server to another. This a different question.
Can you create a database from a .bak file? If not - what are my alternatives?
Thanks.
April 26, 2012 at 12:38 pm
Sure, it's a backup file, you need to restore it onto the new server. You can do that through the SSMS GUI or you can use the RESTORE DATABASE T-SQL command
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2012 at 12:39 pm
Have you tried doing a RESTORE DATABASE?
A *.BAK file is usually a full backup (not always, but usually).
April 26, 2012 at 2:44 pm
Note that just restoring the database will not make it fully operational on the 2008R2 instance. You also need to make sure you transfer the Server Logins used within that database from the SQL 2000 instance to the SQL 2008R2 instance.
See Method 2 in this article, do not worry it says 2000 to 2005, it works from 2000 to 2008R2 as well:
How to transfer logins and passwords between instances of SQL Server
To find the logins that need to be transferred from your SQL 2000 database, while in the context of the 2000 database join sysusers to syslogins, like this:
SELECT l.loginname
FROM sysusers u
JOIN syslogins l ON u.sid = l.sid
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 12:16 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply