Post reply

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

  • 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

  • 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

  • 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

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply