SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Move all, but data, from one sql server to another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/23/2007 :  12:51:00  Show Profile  Reply with Quote
Guys,

We are currently moving our environment. I was told that we need to copy all the stuff over. More specifically, we want to move everything except the data, as our data is dynamic and will fill up in a few days by itself.

What is the best way to move everything over from one server instance to another?

My current approach is the following:

1. Create the file groups we have on our current server on the new server
2. Script out all databases with stored procedures, functions, views, priviliges, indexes ...
3. Script out all the jobs
4. Script out all the dts packages (or rather save each in a file)
5. Load all scripts into the new sql server
6. Re-create user accounts (can these be scripted out also and then loaded?)


Am I missing something or is there a wiser alternative?

Thanks a lot

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/24/2007 :  04:45:00  Show Profile  Reply with Quote
One approach to moving to a new server is to install SQL with the exact same locations for everything, then stop the SQL Service and copy the physical MDF/LDF files from the old system.

This would obviously copy over the data too ... but it would mean that all the Jobs, Logins, DTS, etc. would be preserved. The database files would be a sensible preallocated size too.

You could then "empty" the databases. Either script them and run that (i.e. a "DROP & CREATE" approach), or systematically TRUNCATE/DELETE the data (bit more of a hassle, best approach I guess would be to script the FKs, drop all FKs, TRUNCATE all tables, then recreate all FKs.)

Note that using this route you will need to reset any Identity back to it original seed, if that is important to you.

Kristen
Go to Top of Page

willieb
Starting Member

5 Posts

Posted - 03/01/2007 :  14:20:24  Show Profile  Reply with Quote
Did I miss something? I am in the midst of setting up log shipping, so I took my live database ofline, copied the mdf and ldf files over to the secondary database and attached them. The logins are there with no permissions, the DTS packages are not there and there are no jobs either. Is there away to move the login permissions over? I don't ahve that many packages, so I can copy them manually if I have to (though an automated approach would be much cleaner). Thanks for the help!

Willie
Go to Top of Page

rlaubert
Yak Posting Veteran

USA
96 Posts

Posted - 03/01/2007 :  15:09:28  Show Profile  Visit rlaubert's Homepage  Send rlaubert a Yahoo! Message  Reply with Quote
sql-er,
You could just as easily script out the database and all objects. Then run the script on the new server. This should do what you want.

When you go to script, make sure you select the items from the options tab.

Raymond Laubert
MCDBA, MCITP:Administration, MCT
Go to Top of Page

rlaubert
Yak Posting Veteran

USA
96 Posts

Posted - 03/01/2007 :  15:13:45  Show Profile  Visit rlaubert's Homepage  Send rlaubert a Yahoo! Message  Reply with Quote
WillieB,
You can do the same thing, generate sql scripts of the database and all objects. Run the script at the secondary server then restore a backup to that secondary server. Unfortunately, the DTS package will have to be done seperately. They reside in the MSDB database.
Also, when you set up log shipping it will copy the database and data over as part of the set up of log shipping unless you choose to use the manual backup/restore. Again that will NOT do the DTS as they are in the MSDB database.

User accounts also have to be restored from the Master Database as that is where they are created, then linked to the user databases.

Raymond Laubert
MCDBA, MCITP:Administration, MCT
Go to Top of Page

willieb
Starting Member

5 Posts

Posted - 03/01/2007 :  15:35:09  Show Profile  Reply with Quote
OK, so the user -> db linking still has to be done manually, right?
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 03/08/2007 :  11:28:23  Show Profile  Reply with Quote
Kristen and rlaubert: Thanks for the suggestions!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000