Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-02-23 : 12:51:00
|
| 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 server2. Script out all databases with stored procedures, functions, views, priviliges, indexes ...3. Script out all the jobs4. Script out all the dts packages (or rather save each in a file)5. Load all scripts into the new sql server6. 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
22859 Posts |
Posted - 2007-02-24 : 04:45:00
|
| 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 |
 |
|
|
willieb
Starting Member
5 Posts |
Posted - 2007-03-01 : 14:20:24
|
| 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 |
|
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-01 : 15:09:28
|
| 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 LaubertMCDBA, MCITP:Administration, MCT |
|
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-01 : 15:13:45
|
| 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 LaubertMCDBA, MCITP:Administration, MCT |
|
|
|
willieb
Starting Member
5 Posts |
Posted - 2007-03-01 : 15:35:09
|
| OK, so the user -> db linking still has to be done manually, right? |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-03-08 : 11:28:23
|
| Kristen and rlaubert: Thanks for the suggestions! |
|
|
|
|
|
|
|
|
Move all, but data, from one sql server to another