March 18, 2008 at 12:31 am
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
March 18, 2008 at 4:33 am
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
August 12, 2008 at 12:26 am
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
August 12, 2008 at 4:35 am
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
August 12, 2008 at 6:16 am
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" 😉
August 12, 2008 at 6:40 am
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
August 14, 2008 at 9:27 am
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.
March 31, 2009 at 7:51 am
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
March 31, 2009 at 9:51 am
jmtmwaniki (3/31/2009)
hi guysi 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
April 1, 2009 at 3:02 am
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 ?
April 2, 2009 at 6:15 am
Check out this script by michael vessey, it tests for "SQL Server 2005 readiness"
http://www.sqlservercentral.com/scripts/Miscellaneous/31542/
April 3, 2009 at 5:13 am
well it i s a short process
i installed sql 2k5 + sql 2k5 sp2. then logeed on to the sever and created a new database say x. then on x i restored the database and changed to ccompatility(90), now i want to update but still some serious problems..what commands do i run to update. my access files are on 2k3 but updating..
August 19, 2011 at 7:01 am
Wilfred van Dijk (8/12/2008)
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.
After completing the detach from SQL2000 and restoring to SQL2005, then changing compatibility level, and running the stored procedures above. How do you attempt to resolve the user logins that would be imported and also the schemas. The last concern I have is transfering DTS's into Integration Services. Any ideas?
Thanks,
Blake
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply