Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Posts
    56

    Unanswered: Move all, but data, from one sql server to another

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That looks fine to me.
    Or....copy over your entire database(s) and then delete the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Posts
    56
    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.

    Thanks a lot

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

    http://www.elsasoft.org/tools.htm
    Last edited by jezemine; 02-26-07 at 00:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •