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

• sql_er

  SSCarpal Tunnel

  Points: 4135

  More actions

  February 23, 2007 at 10:52 am

  #61108

  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

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  February 23, 2007 at 4:38 pm

  #691585

  Microsoft has a stored procedure that copies logins. Go to their SQL Server website and query move logins.

  -SQLBill

• MohammedU

  SSCoach

  Points: 19644

  More actions

  February 24, 2007 at 1:10 am

  #691613

  For Q 1 and 2...

  You can script all objects including database using EM without any problem..

  Q3 and 4:

  Restore msdb on to the new server which will copy all jobs, dts packages alterts etc... but you need to modify the database connections inside the dts packages..

  Q6: you can sp_help_revlogin script from MS to move logins...Check the following article for sp_help_revlogin script...

  http://support.microsoft.com/kb/246133

  Note: There are some third party tools out there which will make your life easier but you may have spend money..

  Another option... is restore the database and truncate all the tables...

  MohammedU
  Microsoft SQL Server MVP

• Al Cook

  SSCertifiable

  Points: 6748

  More actions

  February 26, 2007 at 8:11 am

  #691775

  Run this script to create the output you need to copy security.

  SET NOCOUNT ON

  SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''

  ,', @defdb = ''' + dbname + ''''

  ,', @deflanguage = ''' + language + ''''

  ,', @encryptopt = ''skip_encryption'''

  ,', @passwd ='

  , cast(password AS varbinary(256))

  ,', @sid ='

  , sid

  FROM syslogins

  WHERE name NOT IN ('sa')

  AND isntname = 0

  Then run the output of this script on the destination server and that should cover you.