Unanswered: Move all, but data, from one sql server to another
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?
I thought about that as well. However, the db has lots of data, so might take a while. Also, a simple copy will not copy over everything (e.g. jobs, dts), as these are server specific and reside in msdb db.
don't you have all your scripts in source control? if not you ought to. In our org all scripts are checked in, and we have a bat file that executes them all to build a db on any target server.
Here's a tool I wrote that can help you get them in source control if they are not already (only user objects though, not stuff like jobs/dts). Also there's a bat file that executes them all (I use it as a bvt for checked in scripts). finally, source is available so you can modify it if it doesn't meet your needs.