Migrating A SQL 7 DB to SQL 2005

We are planning on upgrading a SQL 7 Server to 2005 SP1. I ran the Update Advisor and one of the issues listed was that the Database Maintenance Plan would not work in Server 2005. The Advisor mentions a tool to use but not which one, which is my first question.

I went online and found some documentation regarding copying the database etc., however, when attempting to copy from the SQL 2000 (DB in SQL 7) to 2005 (I have SQL 2000 and 2005 on my machine) when I try and connect to the 2005 box, its listed as one of the instances but will not connect.

For grins and kicks I backed the database up in the 2000 environment and restored it on a test machine which is in 2005. The Advisor was correct, I am unable to create a Database Maintenance Plan, which I find odd. The Database shows up under the databases but will not show up in the list of db's to create a plan for. So, I attempted to create the jobs and alerts manually.

There has to be an easier way to do this, can anyone help?

Carolyn,
Try the Setup and Upgrade forum instead. You'll have better luck there.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1|||Thread moved from SSIS forum...|||I figured out how to get around this issue. I copied the databases using the wizard in 2000 between sql 7 and 2000, than I migrated the sql 7 db's into 2000. Once the db's are in 2000, open the server management suite in 2005 create a move package and move the dbs into 2005. If the package is created in 2005, you can create a db maintenance plan. I had test servers set up in both the 2000 and 2005 environment so switching wasn't a hassle.

Migrate to new SQL Server

Hi
I'm currently setting up a new SQL Server 2000, and I'm in the process of
planning our migration. What is the best way to migrate databases and
logins?
Should I use the "Copy database wizard" for migration, or should I start by
enabling all logins manually on the new server, manually copy all dts
packages, and finally use "Copy Database Wizard" to only copy over the
database?
I have a small enviroment with only 8 small databases, and aprox 20 users
who typically all have access to the databases.
/A.Avoid the copy database wizard. To move the databases use
Backup/Restore, or Detach/Attach.
To move logins, see http://support.microsoft.com/?id=246133 "How to
transfer logins and passwords between instances of SQL Server".
When I have moved DTS packages I simply opened them on the source
server, then did a Save As and specified the other server.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Sep 2007 11:40:21 +0200, "Andy.I" <Andy.I@.community.nospam>
wrote:
>Hi
>I'm currently setting up a new SQL Server 2000, and I'm in the process of
>planning our migration. What is the best way to migrate databases and
>logins?
>Should I use the "Copy database wizard" for migration, or should I start by
>enabling all logins manually on the new server, manually copy all dts
>packages, and finally use "Copy Database Wizard" to only copy over the
>database?
>I have a small enviroment with only 8 small databases, and aprox 20 users
>who typically all have access to the databases.
>/A.|||You may also use the following tool for tranferrng DTS packages..
http://www.sqldts.com/242.aspx
Thank you,
Saleem Hakani
WWW.SQLCOMMUNITY.COM
SQL Server Tips, SQL Server Scripts, SQL Server Forums, SQL Server Blogs,
SQL Server Radio, SQL Server Events..
"Roy Harvey (SQL Server MVP)" wrote:
> Avoid the copy database wizard. To move the databases use
> Backup/Restore, or Detach/Attach.
> To move logins, see http://support.microsoft.com/?id=246133 "How to
> transfer logins and passwords between instances of SQL Server".
> When I have moved DTS packages I simply opened them on the source
> server, then did a Save As and specified the other server.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 28 Sep 2007 11:40:21 +0200, "Andy.I" <Andy.I@.community.nospam>
> wrote:
> >Hi
> >I'm currently setting up a new SQL Server 2000, and I'm in the process of
> >planning our migration. What is the best way to migrate databases and
> >logins?
> >Should I use the "Copy database wizard" for migration, or should I start by
> >enabling all logins manually on the new server, manually copy all dts
> >packages, and finally use "Copy Database Wizard" to only copy over the
> >database?
> >I have a small enviroment with only 8 small databases, and aprox 20 users
> >who typically all have access to the databases.
> >
> >/A.
>|||> To move logins, see http://support.microsoft.com/?id=246133 "How to
> transfer logins and passwords between instances of SQL Server".
> When I have moved DTS packages I simply opened them on the source
> server, then did a Save As and specified the other server.
> Roy Harvey
> Beacon Falls, CT
Are there any reason doing a dedicated move of the logins if I use Windows
Login on both the new and the old server? All logins are stored in A.D so
shouldn't it be sufficient to just add the apropriate A.D groups on the new
SQL Server and grant those the nececary rights on the databases?|||That would do the same thing. However, it would cause administrative
overhead.
That's why it's recommended moving logins using SSIS.
--
Ekrem Önsoy
"Andy.I" <Andy.I@.community.nospam> wrote in message
news:8883146D-F374-4C29-A347-C0214B0D5E6C@.microsoft.com...
>> To move logins, see http://support.microsoft.com/?id=246133 "How to
>> transfer logins and passwords between instances of SQL Server".
>> When I have moved DTS packages I simply opened them on the source
>> server, then did a Save As and specified the other server.
>> Roy Harvey
>> Beacon Falls, CT
> Are there any reason doing a dedicated move of the logins if I use Windows
> Login on both the new and the old server? All logins are stored in A.D so
> shouldn't it be sufficient to just add the apropriate A.D groups on the
> new SQL Server and grant those the nececary rights on the databases?
>

Migrate from 2000 to 2005

I am planning to migrate sql server 2000 databases to sql server 2005(sql server 2005 in a separate box) .I have few questions regarding the migration:

1. Do I have to run Upgrade wizard on 2000 even though I am not upgrading my sql server?

2.Which other databases do I need to move in addition to the user databases. (i.e. do i have to move any system databases?).

3. What do I need to do to move sql server logins with the password to the new machine?

Thanks.

Ks

YOu don′t run the Wizard on 2005, you can run the Upgrade Advisory wizard in SQL Server 2000. If you just want to migrate the user databases you can only migrate those. There is no need to move over additional databases.
For more information about migrating users, take a look at Google, perhaps this links which provides the way to do this:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/d86b49a5fb086861/d936b4da230b7b05?lnk=st&q=logins+migrate+SQL+Server+2005+2000&rnum=4&hl=de#d936b4da230b7b05
http://groups.google.de/group/microsoft.public.sqlserver.setup/browse_frm/thread/a30917d64f6b9d24/f875221d21c8f71f?lnk=st&q=logins+migrate+SQL+Server+2005+2000&rnum=5&hl=de#f875221d21c8f71f
HTH, Jens Suessmeyer.

http://www.sqlserver2005.de