Planning for migration from 2000 SBS to Express 2005 on a different server

[David W. Fenton]

I posted this in one of the microsoft.public.sqlserver newsgroups and
have gotten no responses. So, I think I'll post it here -- it may be
SQL Server-specific, but it *is* the back end for an Access app.

Here's the post:

I've got a small client who will be installing a new server and I'll
need to migrate their SQL Server 2000 database to the new server
(not SBS) running SQL Server Express (there are only 3 users, so
it's not an issue).

Is it better to upgrade the whole database to SQL Server Express on
the old server and then copy the database over to the new server?

I know I can't upgrade in place (different versions), and the two
machines are probably not going to be up and running simultaneously,
so I can't import from one to the other. It's going to have to be a
file-based upgrade.

Is this possible?

What should I do in what order?

 

[Albert D. Kallal]

Don't upgrade the old box to a new version of sql. I would just leave it
alone.

Simply make a backup using the sql 2000 options that results
in a SINGLE ".bak" file.

You don't want to "copy" any actual sql server files here, that is messy and
likely will not work.

Once you have a backup, then sql 2005 can consume that backup (and will
restore to the new version of sql for you).

It been quite a awhile since I done a backup with the 2000 enterprise
manager, but recall it is much the same as when using the new sql2005
management tools.

You simply browse through the tree to get to your database of choice. Now,
right click and choose "backup" database. Next choose a complete backup.

To restore, just do the above in reverse. Simply browse to your server in
the tree with the 2005 manager and right click on the "databases" at the top
of the tree and then choose "restore" from backup.

Is it better to upgrade the whole database to SQL Server Express on
the old server and then copy the database over to the new server?

Much better to simply use a "complete backup" and move the single file.

So simply create a full "complete" backup on the old system. This will
result in ONE easy file that you simply place on the new box for import into
sql 2005. After you copy this .bak file to the new box then you can use the
studio management tools and browse to the database in the management studio,
then right click on the "databases" and choose "restore database"....

The above is much the process in a nutshell.

It's going to have to be a
file-based upgrade.

Yes, just don't try and copy all the single files that make up a complete
database, you need to use the backup option. Also, you don't have to use the
GUI, but you can use the query builder (command prompt) and go:

backup database zoo to disk = N'c:\sqlback\zoo2bak'

However, I don't know if above syntax works for sql 2000 ...(but, just use
the enterprise manager anyway and you not have to know the syntax).

You can restore via:

restore database zoo2 from disk = N'c:\sqlback\zoo2.bak'

Again, it easiwer to do this in the studio tools then use the command
prompt to restore (either way results in the same).

You should/can be able to do this whole process using the GUI tools
for each versiion of sql server and not have to type any sql commands at the
line prompt at all.

 

[Arvin Meyer [MVP]]

David,

You will want verification from a SQL-Server newsgroup. I'll try to get
someone to give me a definitive answer. My best guess would be that you
should back up your old database, move the file to the new server, then
restore to the new version. I'm not absolutely positive that this will work,
but I'll try to get an answer for you.

 

[Arvin Meyer [MVP]]

Yes, that is the way. The answer from a SQL-Server MVP:
++++++++++++++++++++++++++++++++++++++++++++
It is possible, as long as the database fits within the constraints of SQL
Express, which is it is under 4GB in size. The best method is a
backup/restore of the database since the backup can then be restored to SQL
2000 in the event of a problem. Transfering the logins can be accomplished
by script to maintain the same SID on both instances which would keep him
from having to update the database user to the new logins SID if they were
created manually. The script to do this can be found on the following link:

http://support.microsoft.com/kb/246133

And then in a second post:

My statement was to backup the SQL 2000 database and then restore that onto
SQL Express. If there is a problem, you can still restore the original
backup file onto SQL 2000 since it was created there. However, if you
attach the only copy of the database mdf/ldf files that you have to a SQL
Express instance, you can't then detach the files and attach them to SQL
2000, which is why using a backup would be safer. Maybe my wording wasn't
clear on that.
+++++++++++++++++++++++++++++++++++++++++++++
HTH
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

David,

You will want verification from a SQL-Server newsgroup. I'll try to get
someone to give me a definitive answer. My best guess would be that you
should back up your old database, move the file to the new server, then
restore to the new version. I'm not absolutely positive that this will
work, but I'll try to get an answer for you.

 

[David W. Fenton]

Much better to simply use a "complete backup" and move the single
file.

That should have been obvious -- that's how I deal with all my MySQL
databases, so it should have been the first thought I had.

 

[David W. Fenton]

Yes, that is the way. The answer from a SQL-Server MVP:

Thanks, Arvin.

The security is going to be rebuilt from scratch on the new box,
rather than migrated (I don't know why, but that's what I've been
told), so I'll have to recreate it. That's not all that hard, as I
don't have that many user groups to deal with.

 

[Arvin Meyer [MVP]]

Actually, the security in SQL-Server can be easier to build because you can
also use integration with Windows security. The only thing that you really
need to watch out for is that when creating objects in SQL-Server, it likes
to limit permissions to the creator. Easily changed, but sometimes
frustrating when you roll out something new and are faced with permissions
issues, that don't seem logical.

 

[David W. Fenton]

Actually, the security in SQL-Server can be easier to build
because you can also use integration with Windows security.

That's what I used on the existing server, but they aren't migrating
it to the new server (there are fewer than 10 active users).

The only thing that you really
need to watch out for is that when creating objects in SQL-Server,
it likes to limit permissions to the creator. Easily changed, but
sometimes frustrating when you roll out something new and are
faced with permissions issues, that don't seem logical.

I'm looking into the security scripting options, but haven't had
time to test them. I'm hoping they are name-based and not SID-based.