May 14, 2011 at 5:21 pm
Setup:
Server on a Laptop
Server name MyLaptop (local)
MyDB is my main database created in MSSQL 2005
MSSQL versions installed:
2005 Developer Edition (enterprise)
2008 R2 Developer Edition (enterprise) - Just added today
When I installed R2 I did the upgrade process for the server of MyLaptop and all went well. I can connect R2 via management studio to MyLaptop and the version number is 10.50.1600. All good there.
Its the upgrade of the MSSQL 2005 mdfs (databases) to MSSQL 2008 R2 mdfs, that I dont understand. (ie MyDB as above)
I tried the attached and detach in each management studio:
When I detach or attached myDB to MSSQL 2005 it also appears in R2. When I detach or attached myDB to R2 is also appearsd in MSSQL 2005.
QUESTION : I have no idea if myDB has been upgraded to R2, I need myDB to be a pure R2 version database? How can I ensure that this is so ??
I have a .bak file of myDB created via MSSQL 2005 version.
What do I do to get myDB mdf into a pure R2 version??
Any ideas, what have I missed??
May 14, 2011 at 6:52 pm
Two ways of checking this out. First run SELECT @@VERSION and it will give you the version being used.
Second, if you upgrade your developer edition directly from the 2005, the databases should be shown in the 2008 SSMS. You don't need to detach and reattach from 2005. If the instalation was doe as stand alone, then you will need to detach 2005 and attach to 2008 and the run the command to update the compatibility option from 90 to 100. If that is the route you took, then run update statistics and reindex to make sure all are in 2008.
You can also use the folloqing command to check the edition and version being used:
SELECT SERVERPROPERTY('servername') AS ServerName, SERVERPROPERTY('productversion') AS SQLServerVersion,
SERVERPROPERTY ('productlevel') AS SQLServer_SP,
SERVERPROPERTY ('edition') AS SQLServer_Edition
Good luck
May 14, 2011 at 9:26 pm
Thanks, but in my first post I clearly state that the server is fine, it is 10.50.1600.1 or MSSQL 2008 R2.
So the code you posted confirms the server, thanks.
So the distinction is between the server and databases.
The server is 2008 R2..
BUt how do I know that the mdf (ie database) are converted to MSSQL 2008 R2 from MSSQL 2005???
May 15, 2011 at 2:20 am
If the database it attached to the 2008 R2 server, it is a 2008 R2 database. Always.
p.s. Developer edition or Enterprise?
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
May 15, 2011 at 7:04 am
Database does not have any edition,Database have compatibility option in the advanced option of database
like 80 for 2000 , 90 for 2005 ,10 for 2008
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 15, 2011 at 12:32 pm
Thanks:-)
May 15, 2011 at 3:34 pm
Digs now that you have upgraded you can leave the DB's in 90 compatability mode (2005) or change the compatability to 10 (2008 r2). I would recommend doing a DBCC CHECKDB, DBCC UPDATE USAGE & update the statistics of each of your upgraded DB's
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 15, 2011 at 3:40 pm
Syed Jahanzaib Bin hassan (5/15/2011)
Database does not have any edition,Database have compatibility option in the advanced option of databaselike 80 for 2000 , 90 for 2005 ,10 for 2008
Not completely true. The system tables get upgraded to the version of the engine. That is why you can NOT backup / restore to a lower version.
That's also why even in compatibility level 80 you can use some of the level 90 tsql (assuming sql 2k5 data engine).
May 15, 2011 at 4:28 pm
,,"That is why you can NOT backup / restore to a lower version"..
This is exactly why this thread exists, I learned that the hard way !
Runng DBCC code now !
May 15, 2011 at 4:38 pm
I ran DBCC CHECKDB (myDB)
Result:A after a long list of stuff, I get CHECKDB found 0 allocation errors and 0 consistency errors in database 'myDB'.
I guess all is ok
THIS IS HOW I Transfered myDB from MSSQL 2005 to MSSQL 2008 R2
1) Ran a full .bak (back up) file in MSSQL 2005 for myDB.
2) In MSSQL 2008 R2, completed a restore from the .bak file in (1), in doing so this created the database as part of the process.
SO I assume myDB is NOW FULLY 2008 R2??
Do you conur ??
May 15, 2011 at 5:41 pm
Yes it's fully R2 (system table wise). IIRC, the db will still be in 90 mode. You need to manually change it to 100.
May 15, 2011 at 7:15 pm
Are you responding to my last post? I am confused by your statement, please expand.
If so how to I go from 90 to 100, how does one run code to do that for a database named myDB??
May 15, 2011 at 7:22 pm
found the solution here, once I knew what to google for
http://www.mssqltips.com/tip.asp?tip=1436
SELECT * FROM sys.databases
sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]
--to change to level 80
dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=80
--to change to level 90
dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=90
--or
sp_dbcmptlevel 'test', '80'
sp_dbcmptlevel 'test', '90'
May 15, 2011 at 9:30 pm
Digs, If the CheckDB did not return any issues that is what you are wanting to see. As for the Compatability level there are 2 ways (like everything) to change. Using the sp as you have found as well as using the SSMS GUI.
In SSMS if you expand your Database Tree, then select the particular DB you are wanting to change\check. Right Click and select properties. Go to the options TAB and this will show you the level your Database is currently in. Change it to 100. As I said just above this can also be done using the SP you have found.
After you have changed the level, as per my previous post run DBCC Update Usage and Update the statistics of your database as well.
I hope this helps.
Thx
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 15, 2011 at 10:06 pm
Thanks:-):-D:-)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply