Moving a SQL Database to 2005

I had a client call today asking how to restore a database from a backup made with SQL 2000 to a SQL 2005 server.  Turns out, he was asking the wrong question.  Most questions are like that.

What he really wanted to know was, how can I move my database from SQL 2000 to SQL 2005.

Here’s a little known fact.  At least it seems to be a little known fact (because I’ve run into several people who don’t know it is true.)

All you have to do to move a database from SQL 2000 to SQL 2005 is detach the database from 2000, copy the files to the directory you want your 2005 databases in, and then, reattach them in 2005.  "It’s just that simple."

I first discovered this when I was teaching Visual Studio 2005 and wanted to use the Pubs database, which comes with 2000 but didn’t come with SQL Server Express (2005 engine).  It worked then, and it just worked for my client.

Related Post

  • DotNetNuke Modules – Creating Base ModulesDotNetNuke Modules – Creating Base Modules
    Now that we have DotNetNuke installed into Visual Studio we can go ahead and create our first modules. Actually, creating the modules is pretty simple. But it is even easier to do it wrong, which...
  • SQL Express Mixed Mode AuthenticationSQL Express Mixed Mode Authentication
    So, I'm getting ready to do some data binding demos and I realized that when I installed Visual Studio, I forgot to install SQL Server Express first so that I could control the install. You see,...
  • Three Rules for Database WorkThree Rules for Database Work
    K. Scott Allen over at has these three rules for developing with a database: 1.  Never use a shared database server for development. I've been preaching this one for years and h...
  • Unique SQL Login w/ SqlMembership ProviderUnique SQL Login w/ SqlMembership Provider
    Yesterday, we looked at how to setup the web.config file and modify the business logic layer to make use of Profile properties to give us unique SQL logins when we use the SqlMembership provider fo...
  • SQL – TransactionsSQL – Transactions
    Ruth ask, How do I do transaction tracking within a stored procedure? Hopefully, if you’ve been programming for any length of time against databases, you know that if you are updating mult...