October 19, 2007 at 6:06 am
Hi there,
I'm trying to migrate a SQL Server 2000 DB to SQL Server 2005.
Here's what I did:
1) Made a backup of exper DB in SQL Server 2000 Enterprise Manager.
The backup file was created in C:\Program Files\Microsoft SQL Server\MSSQL\Backup\exper.bak.
The backup options were:
Database - complete
Overwrite existing media
Verify backup upon completion
Remove inactive entries from transaction log
Check media set name and backup set expiration.
2) I copied the backup file from the above mentioned folder to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup, SQL Server 2005 backup folder
3) In MS SQL Server Management Studio Express I selected Databases, from the context menu Restore Database... In this window, when I specify in Form Device the file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\exper.bak and an error dialog appears:
An exception occurred while executing a Transacct-SQL statement or batch.
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\exper.bak'. Operating system error 5(error not found). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server,Error:3201)
I've tried also using the command
RESTORE VERIFYONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\exper.bak'
from SQL Server 2005 Management Studio Express. It produces the same error message:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\exper.bak'. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
If I try this command with a SQL server 2005 backup it works ok:
The backup set on file 1 is valid.
If I do these 2 tests, with a similar command in SQL Server 2000 Query Analyzer, the SQL Server 2000 backup is correctly verified but a SQL Server 2005 backup makes an error.
Apparently from these experiences it seems that making a SQL server 2000 backup and then a restore in SQL Server 2005 doesn't work. Is this true ? Am I missing something ? What are my options in migrating a SQL Server 2000 DB to 2005 ?
I'm using:
- windows xp sp2
- sql server 2005 express edition
- both my sql servers exist locally on my PC.
Thanks,
Miguel Goncalves
October 19, 2007 at 6:19 am
Try using DBPublishing wizard which will create a script alongwith (schema & data) of the existing server -2000. Keep a back up of this script.
Run the script into 2005 server. This will solve the issue.
Else using DTS wizard, try to pull/push the data by imp/exp methodology.
October 19, 2007 at 6:24 am
You can definitely restore a 2000 database backup on SQL Server 2005. While restoring go the options tab and check if you have defined the correct locations for creating mdf and ldf files and also select Overwrite existing database checkbox selected.
Prasad Bhogadi
www.inforaise.com
October 19, 2007 at 7:04 am
you have to take care : SQLserver may want to try to overwrite the original files at the original locations if you work on the same box. !
I must say I don't use SSMS4express.
You can restore SQL2000 backupfiles to sql2005.
I'd try and do it textwise - so not using the SSMS4Express GUI, typing the restore statement yourself.
restore filelistonly from disk='C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\Backup\exper.bak' ;
and work from there on to prepare your restore.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 27, 2007 at 9:27 am
Make sure you have right(i.e Read & Execute) privilege on the backup file for sql server 2005 startup account as it clearly saying that access is denied (in my view you have taken the backup in one server and copied it to another server) and then try restoring the db
Regards..Vidhya Sagar
SQL-Articles
October 29, 2007 at 7:16 am
Couple of things here. First I would make sure that you are not trying to restore to the same data directory as the SQL 2000 database files are in.
Second, why are you backing up the database and then doing a restore on SQL 2005, rather then a detach, copy database files, and then attach? Either way should work, but the backup and restore with SQL 2005 have some issues if you are not on 9.0.3054 (3159 is currently the latest version). Otherwise the restore may fail with several errors.
Also is this the default location for the SQL 2005 Express backups? If so is the db location the default location? If the answer is no to either of these you will need to give the \SQLServer2005MSSQLUser$MACHINENAME$SQLEXPRESS user modify access to these folders. There is a lot to look at and check here but this should allow you to restore the db properly on SQL 2005.
October 30, 2007 at 1:15 am
OS error 5 is for permissios. have you checked whether you have permissions to read the file. and also the restore path. do a restore verifyonly or header only to see if your permissios to the path aare successfull.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
October 31, 2007 at 5:48 am
I had the same problem.
What worked for me:
1. I first created an empty Database on the SQL2005 instance
2. Then Restored the SQL2000 Backup file overwriting the SQL2005 database I just created in step 1.
Steven
October 31, 2007 at 6:16 am
Thanks for your help guys.
I solved the problem when I changed the option in the DB backup in SQL Server 2000 from "Overwrite existing media" para "Append to media".
Thanks very much.
Miguel
December 18, 2008 at 2:01 am
Change login account for the sql server to local account
Did the trick for me in the same situation
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply