Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert a existing SQL Server 2000 database to 2005


Convert a existing SQL Server 2000 database to 2005

Author
Message
wmharshana
wmharshana
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 53
I have try to convert existing SQL Server 2000 database to SQL Server 2005 using backup. I'm Using SQL Server 2005 Express Edition. I got the error

RESTORE DATABASE [EDB] FROM DISK = N'E:\18-03-2008.bak' WITH FILE = 1, MOVE N'EDB_Data' TO N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EDB.MDF', MOVE N'EDB_Log' TO N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EDB_1.LDF', NOUNLOAD, REPLACE, STATS = 10
GO

Error
=====
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EBD.MDF'.
Msg 3156, Level 16, State 5, Line 1
File 'EBD_Data' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EBD.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EBD_1.LDF'.
Msg 3156, Level 16, State 5, Line 1
File 'EBD_Log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EBD_1.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.



Please Help me to solve this Case.

Harshana Weerasinghe
www.harshana.net
www.DevSmart.net
juliane26
juliane26
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 67
It appears that you have no access to the folder or file
'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\EBD.MDF'.
That is the default setting, which might not work for your particular install.

are you using the graphical tool?
the choose the options page and enter new file locations you have access to which can be used on the new server for data and logs.

After restore: in case you want to use SQL 2005 mode don't forget to change the database compatibility level to 90 (SQL 2005) in database properties -> options.

Juliane
kiran_84
kiran_84
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 7
Hi Julie,

I am in mids of upgrading from SQL server 2000 to 2005.

Wanted to get some advise from you guys, on what kind of way shall i use.
Let me inform you my current info.

- Having 2 servers to Upgrade from 2000 to 2005, 1 production server, 1 reporting server
- Both running on SQL 2000 now perfectly.
- just need to upgrade from SQL 2000 to 2005.
- Not a very complex and Large DB

What do you advise me to do?

Copy DB wizard? or Backup and Restore? or others
How to do, step-by-step would be great.

I would really appriciate all the help and support you can give me.
Sharing knowledge is one of the best deeds a person can do.
Maybe 1 day i can share this knowledge with others.
Thanks in advance,

Kiran
Wilfred van Dijk
Wilfred van Dijk
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 1352
Hi,

you can either:
- do a backup/restore or
- do a detach, copy, attach

In both cases, SQL will upgrade you internal databasestructure. Keep in mind that the compatibilitylevel is not changed. Thus after the upgrade, your 2000 databases still have a compatibilitylevel of 80.

Also, the statistics in SQL2000 are not as accurate as in 2005.
Therefore I do the following steps when upgrading:

- change compatiblilty level to 90 (make sure you've tested your application with this comp.level)
- execute a dbcc checkdb with data_purity (see BOL for exact statement)
- execute a dbcc updateusage('{databasename}')
- execute a sp_updatestats

tip: after migrating to 2005, put your old (2000) databases offline, or stop SQL service. Just to be sure no one is connecting to your old environment.

Wilfred
The best things in life are the simple things
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
when you attach\restore a db to SQL2005, i'm pretty sure it runs DBCC CHECKDB as part of the upgrade!

You would certainly be advised to update the stats to sql2005 stats

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Wilfred van Dijk
Wilfred van Dijk
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 1352
What I've heard is that you need to run CheckDB with DataPurity for migrated databases once. DataPurity is always used for newly created 2005 databases.

Wilfred
The best things in life are the simple things
juliane26
juliane26
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 67
The copyDB wizard is not perfect: for my taste too many things to consider (e.g. internal IDs) for it to work.

I used backup/restore switching the level afterwards if possible depending on the application. Some packaged software use still 80 (SQL2000).

detach/attach works too, but since I had to switch hardware completely too merging different servers into one big I found backups having the smallest number of files the easiest to handle - for that simpel reason I've chosen backups. I guess it does not really matter.
jmtmwaniki
jmtmwaniki
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
hi guys

i have tried to convert my databases from 2000 server to sql 2005 but i simply cannot update. when i make netries via this databses, seems thaey have not updated. what do i do.

James
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 3934
jmtmwaniki (3/31/2009)
hi guys

i have tried to convert my databases from 2000 server to sql 2005 but i simply cannot update. when i make netries via this databses, seems thaey have not updated. what do i do.

James


Hi James

How did you do your update? Do you get any errors?

Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
juliane26
juliane26
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 67
Error message and method or tool used for update would be nice to be able to help you.

When using Access be careful, if I remember correctly Access 2000 is not compatible with SQL Server 2005 - updates don't work

And as information: how did you upgrade: backup/restore or detach/copy/attach ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search