September 4, 2003 at 12:40 pm
I'm a Software Developer in a small shop also serving as the DBA.
Can anyone see a problem with migrating 7.0 databases to 2000 using the SQL Server Backup and Restore features? The databases are rather simple and it seems to work fine.
Both 7.0 and 2000 are on separate Servers.
Here is my procedure:
1) Backup the 7.0 db
2) Create a db, with the same name in 2000
3) Restore the 7.0 backup to the 2000 db
Thanks
September 4, 2003 at 12:53 pm
With that approach you will also need to create all the accounts on the 2000 server.
In SQL 2000 you get a copy database wizard which I seem to remember works okay.
Steven
September 4, 2003 at 2:41 pm
You might run into problems if you have different collation setting between SQL Server 2000 and 7.0. This normally happens when doing cross-database joins/compares. Basically the SQL 7.0 collation is retrained when restoring a 7.0 backup file onto a SQL 2000 Server.
Here is some additional information regarding the collation issues that can occur.
http://qa.sqlservercentral.com/columnists/glarsen/collate_part1.asp
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 4, 2003 at 4:05 pm
Replication will be lost with backup restore if using.
Some old implicit datatype conversions no longer work so you may run into code that has varchar field and and int value which was implcitily type to varchar in 7 but in 2000 that will not happen (sorry I forget which ones exactly changed and may not be the the example I gave).
Some default connections settings are changed from what they are in SQL 7 so things like SET ANSI NULL may be on in 2000 that were not in 7 and will sometimes cause you issues.
Some of the above may still happen even with and upgrade except first I do believe can be done thru upgrade and not lost.
September 5, 2003 at 1:02 am
Hm, what about simply using an sp_detach - copy to the other server - sp_attach operation, if your db's 'are rather simple'?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
September 8, 2003 at 12:42 pm
Hi Frank,
I'm getting the following error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'J:\MSSQL\Data\Equifax_Data.MDF' may be incorrect.
I thought it might be a problem with NTFS permissions, but I have write access to this folder and the files are there. I detached them before I copied them.
Any further suggestions?
September 8, 2003 at 1:14 pm
Does the file 'J:\MSSQL\Data\Equifax_Data.MDF' exist
you can test this with
xp_cmdshell 'dir 'J:\MSSQL\Data\Equifax_Data.MDF'
Steven
September 9, 2003 at 5:05 am
Why wouldnt you just use the upgrade wizard? The 7 to 2k migration is one of the easier ones you'll do.
Andy
September 12, 2003 at 8:34 am
Due to our new network being under construction, I was unable to get the Upgrade Wizard to work. It looked like a Network Permission issue.
I was able to detach the databases in 7.0 using QA and then attach them in 2000 using EM.
I'm all set, thanks for all of the suggestions.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply