Migrating everything BUT data from SQL2000 to SQL2005

[lmohr79]

I'm setting up a SQL2005 database on a test server (fairly small) and need to copy/migrate an existing SQL2000 db to the new server. But the SQL2K db is much to large for the test server.

Is there a way I can create/copy everything BUT the data (schema, permissions, etc.)? One of the things I'm testing is the import of data, so no problem to start with empty tables.

FYI - I've learned everything I need to know about SQL under fire, so if this is simple, please just pat me on the head and spoon feed me.

If I screw up the test server, no worries, it's a virtual machine, so back up SHOULD BE easy.

Thank you!

 

[gmmastros]

Use Enterprise manager on the the SQL 2000 box to generate scripts for you. Make sure you script everything. There is no option to script data, so there's no need to worry about that. What you will end up with is a script that you can run on your sql 2k5 box that creates everything from the DB to the tables, views, indexes, procedures, etc...




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lalitha1]

I tried this and it worked for me. To transfer logins and roles i used the following script. Go here

http://support.microsoft.com/kb/246133/en-us

and then use Method 2. It works.

 

[lmohr79]

Thanks for the help - question - should I run the "Method 2" script first? I was having problems with the initial script referenced by gmmastros, so created a second script on the SQL2K box w/o the "Script SQL Server logins", but now getting errors about valid logins.

 

[lalitha1]

You have to tweak the script by making changes so that it points to your logins on your server,and this script is only to transfer your Logins/Roles from 2k to 2005.
But to transfer stps, views etc except for data whatever
gmmastros mentioned should help you, if thats not working check your scripts.