Migration: SQL 7 to SQL 2K - Need to copy all DB's to SQL2K 1

[DSect]

..With a twist, of course.

The twist being that my SQL 7 server is running now and won't be around when SQL2K comes into shop. I need to:

1. Get all my SQL7 DB stuff backed up / mirrored.
2. Wipe out the OS of the SQL server, install W2K and SQL2K
3. Get all of my SQL 7 DB stuff back into SQL2K

Hmm.. I've never had to do this and I thought it would be easy, but I'm a little nervous.

Questions:

1. Can I just "backup" (using SQL's built-in backup) all of my current DB's to a network folder and then "restore" them from that folder when SQL2K is installed?
2. Along with q.1, do I need to backup the DB's first, and then transaction logs or doesn't matter what order.
3. How would you do it (migrate from 7 to 2K), knowing that you cannot have 2 SQL servers running at the same time.

Thanks!

I'm going to try the backup/restore thing before the move and I hope that works.

Any caveats, insight and opinions are GREATLY APPRECIATED!

Thanks as always!!!!

 

[MaffewW]

hi dsect
brief answers to a couple of your questions,

1) Yes, backing up to and restoring from a network share is a good way to do it. We did a server rebuid recently and thats how we did it. If you have chance (and time) I would recommend trying restoring the backup to another 2k server as a test before you wipe your current sql server 7, just to verify you have a restorable backup and also choose the 'verify backup on completion' option

2) If you do a full database backup that will include the transaction log. (anyway if its a good full backup you shouldnt need the transaction log to roll back any transactions etc)

3) Is it going to be a new machine , or are you just upgrading the software only on the current server?

also read Terry Broadbents faq faq183-1781

HTH

Matt

Brighton, UK

 

[DSect]

Thank you very much!

To answer your questions:

I will have a 2K test server to try everything out on (our website + it's DB backend).

2. There will be no "unreconciled" transactions when it's backedup. All DB activity will stop 1 day before the backup. Thanks for the heads-up on the trans. log.

3. It's a fresh install of W2K and SQL2K. We're on NT4 and SQL 7 now. Rather than upgrade, I'd prefer to install fresh and I'm in a good position to do it.

Thanks again. I'll be doing the restore to the test server tonight. I'm confident that all will go well as I'd like to do this upgrade this weekend.

Thanks!

 

[MaffewW]

great, it sounds like you are in a good position having a test server first to try it on.

theres something else you should be aware of, you cant restore system databases from sql7 to 2k so you can expect some log in problems, probably orphaned users. Mr Broadbent has written another faq - faq183-2153 to help you with this. Basically you have a SID created when you create a login on a server, and when you assign that login permissions to a database, the user on the database is allocated the same SID. When you restore to a new server and add the login/users the SID will probably not match between the new login on the new server and the database. hope that makes sense. Read the resource on the FAQ its quite easy to fix.

Have you got any DTS packages or SQL Server agent jobs to take into consideration?

Matt

Brighton, UK

 

[DSect]

Some good info here:

http://www.sqlteam.com/item.asp?ItemID=9066

 

[DSect]

Oh this really stinks.. It is NO easy chore to move databases from one place to another..

The Backup/Restore method does NOT work! - Why? Because when you want to restore the Db, it wants to know what DB you want to "restore to". How can you restore to a DB that doesn't exists? (you can't).

So I created a DB named TestingIt and tried to restore my backup of TestingIt from my old server. It says that "..cannot restore because TestingIt is not the same database as TestingIt on the backup.."

Ugh. If anyone has any clue besides the way mentioned in the link I posted, let me know. It's definately more complicated than copying files to and from.. That's no surprise. What is a surprise to me is that you can't make a "package" that puts all data/tables,objects EVERYTHING into a file that can be moved to another DB.

Ack.. Anyways.. Thanks for the help.. I'll need a little more! <3

 

[MaffewW]

Hi DSect,

you should be able to restore a database to a new server if the database doesnt already exist. Pls tell me how you are doing the restore, is it through enterprise manager or through query analyser. If its through query analyser pls post the code that you are using

A quick pointer if you are using Enterprise Manager, you should be able to expand the server name in EM in the left hand pane and right click the databases folder. Choose All Tasks, Restore Database. You need to specify the database name in the Restore As section. It can be a different name to what it was previously called if you like, but call it the same name if you dont have good reason so people can hopefully still connect without changes client side. Choose the default 'Database - complete' restore backup set option. Then clink the options tab at the top and in the logical filename section it will show all the files in your backup set. Then you can change the location for the files in the 'Move to physical file name' window. If the spec of your machine is the same as previously then you can leave this as it is.

Hope this helps, it should be fairly straightforward when you get your head round it. Let us know how you get on or need more info

Matt

Brighton, UK

 

[MaffewW]

DSect, dont want to complicate things, the above method definately does work. I could have mentioned this before but there is another easy way of copying/moving databases between servers using stored procedures sp_attach_db. in short, you stop the sql server service, copy the mdf and ldf files to the new machine and use the above stored proc to attach them. the example in BOL is

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'


It is a very quick and easy way of doing it, but once you get your head round the backup/restore option, IMO both methods are as easy as each other.

Good lick

Matt

Brighton, UK

 

[DSect]

Maff - I was trying the restore through the EM.

You brought up a good point:

You said in the &quot;Restore as Database&quot; thing, I'll need to specify the DB name. The problem I had there is that my DB wasn't in the list (I haven't tried typing in a new name that doesn't exist yet and will try that ASAP). As mentioned above, I'd made a database on my new server with the correct name and then tried to restore to it, using my new DB's name (which matches the old one) as the restore location. That's when I got the error &quot;This is not the same database as (my DB's name)&quot;. It would not allow me to restore.

** I JUST NOW tried typing in a new, non-existant DB name when restoring on my running SQL server and it did restore the database to the new name I'd specified, with all data. objects and relationships intact. Thank you!**

Let me re-iterate my backup / restore steps:

1. Backed-up the DB (in fact, backed up all DB's as separate .BAK files)

2. Went to new server, copied .BAK files over.

3. Started EM, did &quot;Restore a DB&quot;.

4. Ran into the problems listed above and in my previous post.

(see note above **)
--------------------------------------------------------

As for the Attach/Detach - That sounds like the best way of doing it, if possible - although I'd love to know what I'm doing wrong in my restore process (I think I have it, now**).

Let me ask a few direct questions about detach/attach:

1. Will all objects be brought over? (User Logins, Sp's, relationships, indicies, tables, views and data)?

Actually - that's about it.

Thanks very much for the help.. I will test the restore on my SQL test server and let you know what happens!

Thanks again! I appreciate the help much.. This is my first time having to do this.. Won't be the last I bet!

 

[DSect]

Thanks for the help. Everything worked.

It's very simple to do, but as a newbie, here's what hung me up:

1. You can restore using the Enterprise Manager and when asked what &quot;Database to restore to&quot;, use the name of your backed-up database. It will create it for you.

2. When restoring. Make sure the folders in the physical path of the restore exist. My backup was taken from E:\MSSQL7\DATA and it was trying to restore to that location and failed. I had to create the MSSQL7\DATA folders on E:. If I wanted to change the path, I'd have used RESTORE DATABASE's MOVE option.

Thanks! Was simple with your help!

 

[MaffewW]

Glad to here you got it sorted

&quot;Will all objects be brought over? (User Logins, Sp's, relationships, indicies, tables, views and data)? &quot;

Yes, it is exactly the same as the backup restore option only with this, you have to stop the SQL Server service in order to copy the .mdf and .ldf files to a new location. It is actually generally quicker.

Dont forget to look out for orphaned users as stated above, you may experience some logon problems so test your apps.

If you need anymore help let us know

Matt

Brighton, UK

 

[DSect]

Yeah - I also transferred the logins & SID's using the stored procedures from Microsoft's KB article on transferring SQL server DBs.

Again - Thanks! It was easy once I got past those things I'd posted.

Hopefully this will help out others in my situation.

Thanks 2 U and Tek-Tips!