June 1, 2010 at 9:41 am
hi,
I have a 2008 DB on a test server and a 2000 DB on a production server. Is it possible for me to copy the 2000 mdf and ldf to the test server and somehow get 2008 to use those files and create a 2008 version of the DB? I'm thinking not a chance but...there's a lot I don't know so thought I'd ask here.
How can I quickly get the most recent version of a 2000 DB onto the 2008 box? Hopefully we will be upgraded soon and this will be a thing of the past.
Thanks for your advice.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 1, 2010 at 9:48 am
you can restore a 2000 backup to a 2008 box, however you will not be able to do the reverse
June 1, 2010 at 9:57 am
I should also mention that the reason I'm doing this is because someone added columns to the 2000 production tables and the 2008 version doesn't have those columns. Sp when i tried to do a restore from the bkp The Restore wizard through an error "The backup set holds a backup of a database other than the exiting 'target' database".
So if I pull the mdf and ldf over to the test server is there something I can do there to get them in...it looks like what I'm actually wanting to do is create a new 2008 DB from a 200 mdf and ldf.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 1, 2010 at 10:11 am
MothInTheMachine (6/1/2010)
Sp when i tried to do a restore from the bkp The Restore wizard through an error "The backup set holds a backup of a database other than the exiting 'target' database".
RESTORE DATABASE ... WITH REPLACE
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
June 1, 2010 at 10:19 am
Thanks GilaMonster! I got er done!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 3, 2010 at 5:08 am
Hi Buddha,
Restore is the BEST option as SQL Server 2008 has lots of new system tables.
Happy Upgrading! 🙂
June 3, 2010 at 5:43 am
Tushar-355587 (6/3/2010)
Hi Buddha,Restore is the BEST option as SQL Server 2008 has lots of new system tables.
Huh?
No matter how the DB is migrated (detach/attach, backup/restore), SQL will run a series of upgrade scripts against the database to convert it into a SQL 2008 database. It doesn't matter which method you use, the end result is a SQL 2008 database running on SQL 2008.
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
June 3, 2010 at 5:51 am
Actually the person I sent the 2000 mdf and ldf too said that the db attached fine to their 2000 and 2005 instance but wouldn't attach to their 2008. The way I got the 200 onto the 2008 instance was I deleted the existing database and then attached the 2000 mdf. Apparently when done this way 2008 won't actually do any of the upgrades to make it a full 2008 file.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
June 3, 2010 at 5:58 am
MothInTheMachine (6/3/2010)
Actually the person I sent the 2000 mdf and ldf too said that the db attached fine to their 2000 and 2005 instance but wouldn't attach to their 2008.
Did they say why?
The way I got the 200 onto the 2008 instance was I deleted the existing database and then attached the 2000 mdf.
That's normal, attaches can't be done over existing databases, you will have to delete or attach as a different name.
Apparently when done this way 2008 won't actually do any of the upgrades to make it a full 2008 file.
SQL ALWAYS upgrades an attached or restored database to that server's version. There is no way to prevent that upgrade step from running, you cannot have a SQL 2000 or 2005 format database running on SQL 2008
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
June 3, 2010 at 6:59 pm
She didn't elaborate on what the issue was. I'm back in the Office on Monday and will ask her though. I'll get back to this thread when I find out more.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply