Backup SQL database from one server to another

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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/

Answer 6

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

Answer 7

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

Answer 8

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/

Answer 9

@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

Answer 10

@ Uri Dimant...the first link is not working

---

Satish Menon

Answer 11

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/

Answer 12

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

Answer 13

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

Answer 14

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/

Answer 15

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.

Answer 16

Any progress?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 17

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