Welcome to SQLDBcontrol Forums Sign in | Join | Help

Re: Moving a database

  •  11-09-2009, 9:34

    Re: Moving a database

    Attachment: MoveDatabase.txt

    The attached file contains a script that creates a stored procedure called MoveDatabase, that will enable you to logically move a database and its objects to another server.  Create the stored procedure in the source control database.

    Naturally, the destination server needs to exist first so you'll need to add the server to the project if it isn't already part of it.  Once that's done you can execute the following script, remembering to enter the correct values for the 4 parameters near the top.

    Although I doubt its necessary as the procedure is pretty straight forward it's always a good idea to backup the source control database if you don't already do it regularly.

    declare @current_server_db_id int;
    declare @destination_server_id int;
    declare @current_server varchar(128);
    declare @database varchar(128);
    declare @project varchar(128);
    declare @destination_server varchar(128);

    select @current_server = '<enter current server name>'
      , @database = '<enter database name>'
      , @project = '<enter project name>'
      , @destination_server = '<enter destination server>'

    select @current_server_db_id = ServerDbId
    from ProjectServerDatabase psd
     join ProjectServer ps on ps.ProjectServerId = psd.ProjectServerId
    where ServerName = @current_server
    and DatabaseName = @database
    and ProjectName = @project

    select @destination_server_id = ProjectServerId
    from ProjectServer
    where ServerName = @destination_server
    and ProjectName = @project

    exec MoveDatabase @current_server_db_id, @destination_server_id


    SQLDBcontrol Support

    support@sqldbcontrol.com
View Complete Thread
Powered by Community Server, by Telligent Systems