none
Backup SQL database from one server to another

    Pregunta

  • I have two servers having  ADDS installed and SQL Server 2008 R2 installed.  I want to make data transfer from one database on Server A to the same database on Server B  on a daily basis.  Can anyone tell me how I can accomplish this task.
    Satish Menon
    sábado, 30 de abril de 2011 12:57

Todas las respuestas

  • I would use log shipping as only the changes will be transferred, however the database on the destination server (the secondary) will not be writeable (if in standby mode) or readable (if in recovery).

    If you need the destination database to be accessible, you will need to backup the database, and then copy it over the network, and then restore it.

    You might want to look at this script here to do the backup/copy and restore operation.

    http://ola.hallengren.com/Documentation.html#DatabaseBackup


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    sábado, 30 de abril de 2011 13:43
  • There are a few ways you can accomplish this. One is by doing log shipping. This would do a log backup-copy-restore process at regular intervals. Another option is by using database mirroring. Note that in both options, the database is in recovery mode (except for log shipping which can be configured for read-only mode while waiting for the next log backup to be restored) which means you cannot perform data modifications on the database in Server B

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    sábado, 30 de abril de 2011 14:50
    Moderador
  • If I opt for Log shipping, I need to know the following:

    • Will the source server be slow when this process is being done
    • If at all the source server fails in the middle of the day, I will not lose the transactions made during the day
    • If the Source server fails and I want to operate the Destination server (make it read/write mode) what changes should I make

    Satish Menon
    domingo, 01 de mayo de 2011 5:18
  • If at all I opt for Log shipping, how can I configure the source server A for the same.  If at all my source server fails, how can I change the state of the destination server B to be Read and write.  Log shipping will ensure that I will not lose any transaction prior to the crash i.e I can recover it from destination server B.
    Satish Menon
    domingo, 01 de mayo de 2011 5:29
  • Satish

    As others have already mentioned , log shipping means that secondary database will be in read only mode,

    Being on the destination server  you can schedule job that does restore from your local server

    RESTORE DATABASE dbname

    FROM DISK=N'\\SERVER\t$\Program Files\Microsoft SQL Server\MSSQL$SQL2\Backup\dbname.bak'
    WITH RECOVERY,
       MOVE 'Newdb_Data' TO 'e:\sql_data\dbname.mdf',
       MOVE 'Newdb_Log' TO 'e:\sql_data\dbname_1.ldf'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    domingo, 01 de mayo de 2011 6:44
  • I came across the following link

    http://dbcopytool.codeplex.com/


    how far is this tool useful, a layman can also execute it right? this also copies the entire database from one server to another.


    Satish Menon
    domingo, 01 de mayo de 2011 7:38
  • If you want to do it on regular basis then you can also look for snapshot replication (depends on database size)
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    domingo, 01 de mayo de 2011 12:18
    Moderador
  • I agree with Balmukund.

    http://www.swynk.com/friends/achigrik/SetupMR.asp ----- Setting Up Merge Replication: A Step by Step Guide

    http://www.mssqlcity.com/Articles/Replic/Replic.htm --------Setting All Replica (Step by step)

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    lunes, 02 de mayo de 2011 4:59
  • @Balmukund.... I would like to know if snapshot replication will slow down the system or it takes place in the background. When ever the user updates anything will that process take more time when replication is active.
    Satish Menon
    lunes, 02 de mayo de 2011 6:49
  • @ Uri Dimant...the first link is not working
    Satish Menon
    lunes, 02 de mayo de 2011 6:50
  • Yep, sorry about it,

    You can schedule SNAPSHOT replcation on nightly basic then there is no activities and SQL Server will copy over entire snapshot of the source database to the destination. I mean , no need to replicate online in that case.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    lunes, 02 de mayo de 2011 6:54
  • Its depend upon your database size, If it very large database snapshot replication would take ages to refresh every night. log-shipping (with standby mode) would be best choice for your requirement provided your SLA support mininum data lost - alternatively next choice would be transactional replication.


    http://uk.linkedin.com/in/ramjaddu
    lunes, 02 de mayo de 2011 12:17
  • I came across the following link

    http://dbcopytool.codeplex.com/


    how far is this tool useful, a layman can also execute it right? this also copies the entire database from one server to another.
    Satish Menon
    miércoles, 04 de mayo de 2011 4:49
  • Snapshot replication is just a snapshot of the database to be copied on the warm-secondary server.... As I pointed earlier,  you can schedule it...
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    miércoles, 04 de mayo de 2011 5:55
  • There are two option to use Copy one  server data to another server

    1) SSIS Package

    2) Snapshot Replication

    both are the best option to transfer data b/w server's.

    viernes, 01 de julio de 2011 11:55
  • Any progress?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    domingo, 03 de julio de 2011 7:54
  • Satish

    SSIS is best way if you are dealing with decent size of db, i use same feature for one of my DB. if you deal with multiple GB database use Sanpshot replication.

    here are links may be helpful

    DB Copy

    http://msdn.microsoft.com/en-us/library/ms188664.aspx

     

    Snapshot Replication

    http://technet.microsoft.com/en-us/sqlserver/ee848811


    -Giri Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    miércoles, 06 de julio de 2011 20:42