February 25, 2003 at 7:51 am
I have a db running on a sql7 server and would like to move it to a sql2000 server. Is there a simple way to move it? Will backup and restore work. Both servers are production, so I want to minimize down time.
February 25, 2003 at 8:22 am
You could in another server upgrade the SQL 7 to SQL 2000, then backup DB and restore it in your SQL 2000 production server.
February 28, 2003 at 9:34 am
quote:
You could in another server upgrade the SQL 7 to SQL 2000, then backup DB and restore it in your SQL 2000 production server.
I'm currently in the process of migrating from SQL 7 to SQL 2K. I restored a FULL backup from my SQL 7 to SQL 2K. It worked fine. When I restored the FULL backup I moved the physical location of the MDF and LDF with no problems. Now that I want to restore a differential backup it tells me that I can't move the physical location of the MDF and LDF files. Why would this be? Any ideas?
Jeroocko
February 28, 2003 at 9:57 am
Can you post your restore statements for both full and differential restoration?
February 28, 2003 at 10:06 am
Using EM, in the restore dialog box, under the Options tab, check to make sure the mdf and ldf file locations/names match what's on the new server.
Experience: the comb life hands you just after you've lost your hair
February 28, 2003 at 10:16 am
quote:
Can you post your restore statements for both full and differential restoration?
I'm using EM to do the restores so I don't have the actual SQL Statements that were used.
February 28, 2003 at 10:18 am
Which option did you choose on 'Recovery completion state' when you restored the full backup?
Did you check 'force restore over existing database'?
Edited by - Allen_Cui on 02/28/2003 10:18:52 AM
February 28, 2003 at 10:21 am
quote:
Using EM, in the restore dialog box, under the Options tab, check to make sure the mdf and ldf file locations/names match what's on the new server.Experience: the comb life hands you just after you've lost your hair
That is where I'm changing the physical location of the files. I'm sure that they are spelled correcly.
February 28, 2003 at 10:22 am
quote:
Which option did you choose on 'Recovery completion state' when you restored the full backup?Did you check 'force restore over existing database'?
Edited by - Allen_Cui on 02/28/2003 10:18:52 AM
Yeah I tried that also but it gave me the same error. I used "Leave Database nonoperational but able to restore additional transaction logs."
Edited by - jeroocko on 02/28/2003 10:26:03 AM
March 3, 2003 at 5:48 am
i think also there is another way:
1- deAttach DB on SQL 7
2- copy 2 files (.mdf and .ldf) to the SQL 2000 machine
2- attach DB ... by right click on "Databases" >> then "All Tasks" then " Attach DB" .. and browse to mdf file .. and write name of DB and who is the owner ?
hope this help u.
Amir
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
March 3, 2003 at 8:25 am
quote:
i think also there is another way:1- deAttach DB on SQL 7
2- copy 2 files (.mdf and .ldf) to the SQL 2000 machine
2- attach DB ... by right click on "Databases" >> then "All Tasks" then " Attach DB" .. and browse to mdf file .. and write name of DB and who is the owner ?
hope this help u.
Amir
Alamir Mohamed
Thanks Alamir,
Yes I know that this option exsits but I'm trying to resotre a differential backup to minimize downtime as much as I can.
I tried somthing else and it seems to have worked. In the 'General' tab when you choose to restore 'From Device', you have options as too what type of backup you are trying to restore whether it is a differential, a file group, transaction log, etc.. The last option is to 'Read backup set information and add to backup history'. I chose that option and ran the proccess. When it was complete you get a comfirmation message. Now in the 'General' tab when you choose to restore 'Database', you will see the backups you can restore. Now it appears that you can choose which backup you want to restore. But I guess it assumes that you have not restored any backup so if you want restore a differential it will have to restore the full backup first and then the differential, or so it seems.
I was able to restore the Full backup and the differential using this process. But I'm still not completely convinced so since I have the unmesureable oppurtunity to give this a few runs. I will and I'll let you guys know how it turns out.
Thanks for all your help to all you.
Jeroocko
March 3, 2003 at 11:06 am
In order to restore differential or transaction log backup on top of a restore from a full backup you should select either "leave database nonoperational but able to restore additional transaction logs" or "leave database read-only and able to restore additional transaction logs" on the Options tab of the Restore database window. Choose "leave database operational..." on the last restore.
March 3, 2003 at 1:58 pm
quote:
In order to restore differential or transaction log backup on top of a restore from a full backup you should select either "leave database nonoperational but able to restore additional transaction logs" or "leave database read-only and able to restore additional transaction logs" on the Options tab of the Restore database window. Choose "leave database operational..." on the last restore.
Thanks,
I took note of that on one of the earlier threads. I actually tested both options and it didn't work.
Jeroocko
March 4, 2003 at 4:28 pm
Dear,
If nothing works take long breath and realx. then do your reatoration on the old server I mean with all differential backups. Then you have the full database. Just upgrade and apply the final transation log.
March 6, 2003 at 7:15 am
jimmead - as per your original post, I'd like to advise that if you do get your restore to work (and, btw, every one that I have done has worked), make sure to update your statistics, as in BOL:
"Update Statistics
It is recommended that you update all SQL Server 7.0 statistics after upgrading to SQL Server 2000. Although this update may take a significant amount of time on large databases, using SQL Server 7.0 statistics with SQL Server 2000 may result in poor query performance."
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply