Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DaveGKeller
Starting Member
10 Posts |
 Posted - 2011-01-05 : 11:26:16
|
In a recent post I made here it was suggested I should upgrade all my database to R2 because of the 10 GB limit. I have a question on exactly how to do that. My existing is MSDE SP1. If I install 2008 R2, using a different InstanceName, can I create the same database name? Our current InstanceName is "DB2000". We are thinking about using a different InstanceName because "DB2000" shows up in a lot of examples out there. So my bosses said if we are switching databases we should use a new InstanceName. All of this is greek to me (not a d/b guy but I've been told to do it).Can I install 2008 R2 on the same machine with MSDE, use a different InstanceName, and then copy or move the existing database to 2008 R2? I've seen in some examples Attach/Detach. I'm using the free Microsoft SQL Server Management Studio. I'm not sure if that matters.I'm sorry it's such a noob question. As you can see I don't really know much about this but have no choice in being the one that has to do the job. I just really want it to go as smoothly as possible. Any help is appreciated and, if possible, please explain like I'm 4 years old  Thank you. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-05 : 12:07:28
|
| "I should upgrade all my database to R2 because of the 10 GB limit"If you have MSDE (i.e. SQL 2000) then I think your current limit is 2GB, not 10GB ?If you install SQL 2008 (n the same machine, or on a new machine) you can just restore (or ATTACH) a SQL 2000 database.I don't know if it is easy to install SQL 2008 alongside SQL 2000 on the same machine. We always do major version changes by migrating to a new machine as there is no chance of the new installation fouling up the existing one - and we can test on the new machine and then when happy take a fresh copy and "go live" on the new machine. |
 |
|
|
DaveGKeller
Starting Member
10 Posts |
Posted - 2011-01-05 : 22:05:27
|
quote:
Originally posted by Kristen
"I should upgrade all my database to R2 because of the 10 GB limit"If you have MSDE (i.e. SQL 2000) then I think your current limit is 2GB, not 10GB ?
Right. In my previous post the responder said I should upgrade so the new d/b would be a 10 GB limit. I mentioned that to my boss and that's when he said to make the move. I probably should have kept my mouth shut  quote:
Originally posted by KristenIf you install SQL 2008 (n the same machine, or on a new machine) you can just restore (or ATTACH) a SQL 2000 database.I don't know if it is easy to install SQL 2008 alongside SQL 2000 on the same machine. We always do major version changes by migrating to a new machine as there is no chance of the new installation fouling up the existing one - and we can test on the new machine and then when happy take a fresh copy and "go live" on the new machine.
So would it be "better" to copy the .mdf and .ldf files from MSDE to a jump drive, install SQL 2008 on a different machine, copy the .mdf & .ldf to that machine and "attach"? Then when it's all running well and we're happy, then do the same on the real machine (after uninstalling MSDE)?I'm going to be working on this tomorrow so I'm hoping to have a "plan" in place. Is SQL Server Management Studio Express capable of doing the Attach operation? I don't have it on this computer but it is on the machine that has the database.Thank you again for all help. It is appreciated very much. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-06 : 02:35:14
|
"I should upgrade so the new d/b would be a 10 GB limit"Ah, OK, I understand now. SQL 2008 Express limit is 4GB (if you install the full-blown version there will be no practical limit)Your plan of copy the MDF / LDF and Attach sounds fine (make sure that SQL Service on the current machine is STOPPED before you copy, or that you DETACH the databases before you copy - but then you will have to re-ATTACH them afterwards).If the database needs to be online and stopping/detach+re-attach would be a problem, then restoring the last full Backup file instead would be fine for a test on Machine-B.I imagine (but I don't know) that there is an UPGRADE route from MSDE to SQL2008 Express. So once you have done all your testing on Machine-B quite possibly you will be able to just "Upgrade" MSDE to SQL 2008 Express on Machine-AFor your test on Machine-B (and then again after the Upgrade on Machine-A) you ought to follow the steps for changing the compatibility level and rebuilding indexes etc (I'll post a link below). If changing the compatibility level gives you problems with your tests you can try putting the compatibly level back to "SQL 2000 / MSDE" (Compatibility Level = 80) and retesting - but, ideally, you would be better to be on the latest compatibility level because then you can take advantage of all the new features in SQL 2008  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
|
|
|
|
|
|
Upgrading from MSDE to 2008 R2 question?