Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert a existing SQL Server 2000 database to 2005 Expand / Collapse
Author
Message
Posted Tuesday, March 18, 2008 12:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 3:12 AM
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
Post #470654
Posted Tuesday, March 18, 2008 4:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 3, 2009 3:55 PM
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
Post #470712
Posted Tuesday, August 12, 2008 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2008 7:18 PM
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
Post #550759
Posted Tuesday, August 12, 2008 4:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:57 AM
Points: 928, Visits: 1,116
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
Post #550850
Posted Tuesday, August 12, 2008 6:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:53 PM
Points: 6,191, Visits: 13,341
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"
Post #550895
Posted Tuesday, August 12, 2008 6:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:57 AM
Points: 928, Visits: 1,116
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
Post #550914
Posted Thursday, August 14, 2008 9:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 3, 2009 3:55 PM
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.
Post #552835
Posted Tuesday, March 31, 2009 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 3, 2009 5:09 AM
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
Post #687053
Posted Tuesday, March 31, 2009 9:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #687181
Posted Wednesday, April 1, 2009 3:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 3, 2009 3:55 PM
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 ?
Post #687711
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse