+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

how to synchronize local and remote sql server databases

  1. how to synchronize local and remote sql server databases

    I want to synchronize local version of SQL Server Database 2005 with that of
    remote version of SQL Server database 2005.
    Are there any tools available which would help me in doing this so that all
    the records in all the tables available in the remote SQL Server database is
    also available in the local version of the SQL Server database.

    Please advise.



  2. Re: how to synchronize local and remote sql server databases

    S N (uandme72atinvaliddotcom) writes:
    > I want to synchronize local version of SQL Server Database 2005 with
    > that of remote version of SQL Server database 2005. Are there any tools
    > available which would help me in doing this so that all the records in
    > all the tables available in the remote SQL Server database is also
    > available in the local version of the SQL Server database.


    The most obvious answer that comes to mind is replication, but replication
    is a fairly heavy-handed business, and sometimes other solutions like
    rolling your own is better.

    Another alternative is to use BACKUP/RESTORE, possibly in combination
    with log shipping, particularly if you only require that sync:ing
    happens only a few times a day.


    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  3. Re: how to synchronize local and remote sql server databases

    I would like to sync the remote and local databases once every day.
    In this situation, kindly advise on how to achieve the task.
    What are the tools available. I currently have only the SQL Server
    Management studio Express which I am using to connect to the SQL Server and
    create/delete tables etc.





    "Erland Sommarskog" wrote in message
    news:Xns9DA43BAE148BYazormanat127dot0.0.1...
    >S N (uandme72atinvaliddotcom) writes:
    >> I want to synchronize local version of SQL Server Database 2005 with
    >> that of remote version of SQL Server database 2005. Are there any tools
    >> available which would help me in doing this so that all the records in
    >> all the tables available in the remote SQL Server database is also
    >> available in the local version of the SQL Server database.

    >
    > The most obvious answer that comes to mind is replication, but replication
    > is a fairly heavy-handed business, and sometimes other solutions like
    > rolling your own is better.
    >
    > Another alternative is to use BACKUP/RESTORE, possibly in combination
    > with log shipping, particularly if you only require that sync:ing
    > happens only a few times a day.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000:
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >




  4. Re: how to synchronize local and remote sql server databases

    S N (uandme72atinvaliddotcom) writes:
    > I would like to sync the remote and local databases once every day.
    > In this situation, kindly advise on how to achieve the task.


    BACKUP/RESTORE is absolutely the best way. You need to backup the database,
    and by restoring the backup, you test that the backup actually works.

    To backup a database:

    BACKUP DATABASE db TO DISK = 'somepath.bak'

    To restore the database on another server (note that you may also
    have to copy the database from one disk to another, unless there is
    a path that both servers can use).

    RESTORE DATABASE db FROM DISK = 'somepath'
    WITH MOVE 'name1' TO 'path1',
    MOVE 'name2' TO 'path2',
    REPLACE

    To find name1 and name2 do "sp_helpdb db" on the source database; you
    find the names in the first column in the second result set. 'path1'
    and 'path2' are the location you want for the database files on the
    receiving servers.

    You can schedule this as a job in SQL Server Agent. If you only have
    Express, you can instead use Windows Task Scheduler. Use SQLCMD to
    run the SQL commands.

    Do you use SQL Server logins? They will not match after the restore
    on the local server.

    Note also that if you have not backed up the other database yet, this
    means that the log the for database is auto-truncated. But once you
    start to backup the database, the log will continue to grow if the
    database is set to full recovery. If you are not interested in doing
    up-to-the-point-recovery, set the database in simple recovery:

    ALTER DATABASE db SET RECOVERY SIMPLE


    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  5. Re: how to synchronize local and remote sql server databases


    "Erland Sommarskog" wrote in message
    news:Xns9DA4E67CE1236Yazormanat127dot0.0.1...
    > S N (uandme72atinvaliddotcom) writes:
    >> I would like to sync the remote and local databases once every day.
    >> In this situation, kindly advise on how to achieve the task.

    >
    > BACKUP/RESTORE is absolutely the best way. You need to backup the
    > database,
    > and by restoring the backup, you test that the backup actually works.
    >
    > To backup a database:
    >
    > BACKUP DATABASE db TO DISK = 'somepath.bak'
    >
    > To restore the database on another server (note that you may also
    > have to copy the database from one disk to another, unless there is
    > a path that both servers can use).
    >
    > RESTORE DATABASE db FROM DISK = 'somepath'
    > WITH MOVE 'name1' TO 'path1',
    > MOVE 'name2' TO 'path2',
    > REPLACE
    >
    > To find name1 and name2 do "sp_helpdb db" on the source database; you
    > find the names in the first column in the second result set. 'path1'
    > and 'path2' are the location you want for the database files on the
    > receiving servers.


    Are path1 and path2 physical paths (of the form c:\database\..... ) or
    something else?

    >
    > You can schedule this as a job in SQL Server Agent. If you only have
    > Express, you can instead use Windows Task Scheduler. Use SQLCMD to
    > run the SQL commands.
    >
    > Do you use SQL Server logins? They will not match after the restore
    > on the local server.



    I use SQL server logins on both the remote server as well as local server.
    How to ensure that the logins match when we restore the database from remote
    to the local server and vice versa.


    >
    > Note also that if you have not backed up the other database yet, this
    > means that the log the for database is auto-truncated. But once you
    > start to backup the database, the log will continue to grow if the
    > database is set to full recovery. If you are not interested in doing
    > up-to-the-point-recovery, set the database in simple recovery:
    >
    > ALTER DATABASE db SET RECOVERY SIMPLE
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000:
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >



  6. Re: how to synchronize local and remote sql server databases

    S N (uandme72atinvaliddotcom) writes:
    > Are path1 and path2 physical paths (of the form c:\database\..... ) or
    > something else?


    Physical paths.

    > I use SQL server logins on both the remote server as well as local
    > server. How to ensure that the logins match when we restore the database
    > from remote to the local server and vice versa.


    Then you will need a script that you run as part of the restore job. Here
    is such a script:

    declare @sql nvarchar(MAX)
    declare usercur cursor static local for
    select 'ALTER USER ' + quotename(name) +
    ' WITH LOGIN = ' + quotename(name)
    from sys.database_principals
    where principal_id >= 5
    and type_desc = 'SQL_USER'

    open usercur

    while 1 = 1
    begin
    fetch usercur into @sql
    if @@fetch_status <> 0
    break
    exec(@sql)
    end

    deallocate usercur

    The script assumes that the logins already exist on the target server.
    If any user is missing on the target there will be an error. (But the
    rest of the logins are remapped.)



    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  7. Re: how to synchronize local and remote sql server databases

    All the methodologies indicated involve scripts etc to be done manually.
    Is there any tool available for free, which can take backup/restore the
    database from remote to local and vice versa?
    Further, is there any tool which can help edit the data inside a table, just
    like it is possible in MS Access.

    "Erland Sommarskog" wrote in message
    news:Xns9DA5EA90D9137Yazormanat127dot0.0.1...
    >S N (uandme72atinvaliddotcom) writes:
    >> Are path1 and path2 physical paths (of the form c:\database\..... ) or
    >> something else?

    >
    > Physical paths.
    >
    >> I use SQL server logins on both the remote server as well as local
    >> server. How to ensure that the logins match when we restore the database
    >> from remote to the local server and vice versa.

    >
    > Then you will need a script that you run as part of the restore job. Here
    > is such a script:
    >
    > declare @sql nvarchar(MAX)
    > declare usercur cursor static local for
    > select 'ALTER USER ' + quotename(name) +
    > ' WITH LOGIN = ' + quotename(name)
    > from sys.database_principals
    > where principal_id >= 5
    > and type_desc = 'SQL_USER'
    >
    > open usercur
    >
    > while 1 = 1
    > begin
    > fetch usercur into @sql
    > if @@fetch_status <> 0
    > break
    > exec(@sql)
    > end
    >
    > deallocate usercur
    >
    > The script assumes that the logins already exist on the target server.
    > If any user is missing on the target there will be an error. (But the
    > rest of the logins are remapped.)
    >
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000:
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >




  8. Re: how to synchronize local and remote sql server databases

    S N (uandme72atinvaliddotcom) writes:
    > All the methodologies indicated involve scripts etc to be done manually.


    Who says that?

    In fact, the whole points with scripts is that they are the foundation
    for automation.

    > Is there any tool available for free, which can take backup/restore the
    > database from remote to local and vice versa?


    The normal procedure would be to run them from SQL Server Agent. But
    if I recall directly you are using Express Edition which does not
    ship with Agent.

    No big deal, you can use the Windows Task Scheduler instead and run
    the scripts from SQLCMD.

    > Further, is there any tool which can help edit the data inside a table,
    > just like it is possible in MS Access.


    The preferable way to change data is to use INSERT, UPDATE, DELETE. Again,
    scripts can be automated. Pointing and clicking can't. But in Mgmt
    Studio you can right-click a table and there is an Edit option as I
    recall.

    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  9. Re: how to synchronize local and remote sql server databases

    I have tried logging onto the remote SQL server and run the command

    BACKUP DATABASE db TO DISK = 'somepath.bak'

    However, it did run and take backup of the remote database on the remote
    server itself and it did not take the backup of remote database on the local
    computer.
    What i actually want is to take backup of the remote database on the local
    computer.
    please advise on how to achieve it.

    "Erland Sommarskog" wrote:

    > S N (uandme72atinvaliddotcom) writes:
    > > All the methodologies indicated involve scripts etc to be done manually.

    >
    > Who says that?
    >
    > In fact, the whole points with scripts is that they are the foundation
    > for automation.
    >
    > > Is there any tool available for free, which can take backup/restore the
    > > database from remote to local and vice versa?

    >
    > The normal procedure would be to run them from SQL Server Agent. But
    > if I recall directly you are using Express Edition which does not
    > ship with Agent.
    >
    > No big deal, you can use the Windows Task Scheduler instead and run
    > the scripts from SQLCMD.
    >
    > > Further, is there any tool which can help edit the data inside a table,
    > > just like it is possible in MS Access.

    >
    > The preferable way to change data is to use INSERT, UPDATE, DELETE. Again,
    > scripts can be automated. Pointing and clicking can't. But in Mgmt
    > Studio you can right-click a table and there is an Edit option as I
    > recall.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >
    > .
    >


  10. Re: how to synchronize local and remote sql server databases

    S N (SNatdiscussionsdotmicrosoft.com) writes:
    > I have tried logging onto the remote SQL server and run the command
    >
    > BACKUP DATABASE db TO DISK = 'somepath.bak'
    >
    > However, it did run and take backup of the remote database on the remote
    > server itself and it did not take the backup of remote database on the
    > local computer.
    > What i actually want is to take backup of the remote database on the local
    > computer.
    > please advise on how to achieve it.


    So you want to be connected to server A and backup a database on server B?

    You can only backup a datbase on a server if you are connected to it.

    You can be connected to to server A and from server A run commands on
    server B, using EXEC() AT, but it may get you into more trouble with
    setting up linked servers than it pays back.


    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


+ Reply to Thread
Page 1 of 2 1 2 LastLast