May 31, 2007 at 8:37 pm
If anybody is still watching this topic, here's another $0.02 worth.
I believe that there are far more secret little versions of Sql 2005 than simply RTM, SP1, SP2, and SP2a.
When I take a production 2000 db backup and restore it to my 2005 dev machine, this is what I see in the messages window:
Processed 44152 pages for database 'par_pos', file 'QSDB_Data' on file 1.
Processed 1 pages for database 'par_pos', file 'QSDB_Log' on file 1.
Converting database 'par_pos' from version 539 to the current version 611.
Database 'par_pos' running the upgrade step from version 539 to version 551.
Database 'par_pos' running the upgrade step from version 551 to version 552.
Database 'par_pos' running the upgrade step from version 552 to version 553.
Database 'par_pos' running the upgrade step from version 553 to version 554.
Database 'par_pos' running the upgrade step from version 554 to version 589.
Database 'par_pos' running the upgrade step from version 589 to version 590.
Database 'par_pos' running the upgrade step from version 590 to version 593.
Database 'par_pos' running the upgrade step from version 593 to version 597.
Database 'par_pos' running the upgrade step from version 597 to version 604.
Database 'par_pos' running the upgrade step from version 604 to version 605.
Database 'par_pos' running the upgrade step from version 605 to version 606.
Database 'par_pos' running the upgrade step from version 606 to version 607.
Database 'par_pos' running the upgrade step from version 607 to version 608.
Database 'par_pos' running the upgrade step from version 608 to version 609.
Database 'par_pos' running the upgrade step from version 609 to version 610.
Database 'par_pos' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 44153 pages in 24.824 seconds (14.570 MB/sec).
May 31, 2007 at 8:42 pm
That is very interesting. I'll have to try that out.
June 1, 2007 at 5:22 am
I've seen similar behavior. I never saw that in 2000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2007 at 5:26 am
Could it be that they had that many problems in beta testing that they had to keep increasing the versions? I know there were a lot of rewrites going on, even before beta.
June 1, 2007 at 9:28 am
If you install the original Sql 2000, then apply SP3 you will see a series of dialogs like "running script x of 11" so perhaps Sql 2005 is doing a similar set of tasks as it upgrades the db version from 2000 to 2005. Which suggests that behind the scenes, each SP or version upgrade actually consists of a series of intermediate steps. Just a thought.
June 2, 2007 at 1:15 pm
It might be time for the 2005 engine to stand still and stabilise as a solid platform for a bit while older systems are upgraded (making a larger 2005 user base). The Ancillary products should March forward in line with business and marketing demands (stimulating demand for more installations).
I dont want Analysis services, reporting services and SSIS veiwed as seperate products though I acknowledge that is the risk.
2005 presents a steap learning curve for both DBA's and developers. we are already seeing recruitment ads like DBA(SSIS),DBA(RS),DBA(AS) if it was a more stable release schedule the recruitment market would not split this way and the DBA role would maintain its high pay high status. As steve pointed out in last weeks thread how would you sell yourself doing the SQL(Engine) Role ?
Colin
June 2, 2007 at 3:01 pm
Actually Microsoft split the product, I have just found things they changed that is above annoyance so I will write articles for it. They changed how a database can be moved from one end of the World to the other without a valid replacement and that is bad. I had to follow those cryptic errors to code I just decided to convert and try and it worked, that is not good because developers will be confused. I now know why Asp.net developers that uses Access have problems understanding something I think it is simple. And yes I tried a version of the code below and it did not work, I also used restore options. I wrote and executed four sets of code to get it done and I will not call that simple.
RESTORE DATABASE AdventureWorks_2
FROM DISK = 'C:\Backups\Adventureworks.bak'
WITH FILE = 2,
MOVE 'AdventureWorks_Data' TO 'C:\backups\aw2_log.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\backups\aw2_log.ldf';
Kind regards,
Gift Peddie
June 2, 2007 at 4:51 pm
Maybe this will help. I am using a dumpdevice named par_pos_1, instead of using the file name method, but here are my two versions:
(both versions start out like this)
-- Create a logical backup device for the full backup.
EXEC sp_dropdevice 'par_pos_1'
EXEC sp_addumpdevice 'disk', 'par_pos_1', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\wm_par_pos.bak'
(to restore to my 2000 dev machine I use this)
RESTORE DATABASE
par_pos
FROM
par_pos_1
WITH RECOVERY,
MOVE
'QSDB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\PAR_POS_Data.MDF'
(but to restore to my 2005 dev machine I use this)
RESTORE DATABASE
par_pos
FROM
par_pos_1
WITH RECOVERY,
REPLACE,
MOVE
'QSDB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\PAR_POS_Data.MDF'
June 2, 2007 at 5:04 pm
Just go to http://wvmitchell.blogspot.com/ and in the SEARCH BLOG box type
backup restore
and you'll see both versions explained a little better. I just woke up from my Saturday nap 😮 still groggy
June 2, 2007 at 5:47 pm
That code did not work the database includes a log file so SQL Sever will not restore it, I created two dumpdevices one for the MDF and one for the LDF and first change to restore with no recovery which puts the database in a restoring mode at which time it is not accessible. Then I had to run one more code to restore with recovery before SQL Server recreates the database. This database is without indexes so I am assuming it will take five sets of code to restore one with indexes because of the NDF file. The point I am making in 2000 this task was a few seconds to minutes depending on the size of the database. I could not even restore a .bak stored in VSS, that is not good.
Kind regards,
Gift Peddie
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply