Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-06 : 07:36:23
|
| Venkata writes "We have a SQL Server database on two servers. We would like to schedule a job that will copy the database from server A to Server B each morning. Could you suggest how we can acheive this. Thanks,_Venkata" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-06 : 07:49:20
|
| Make a DTS Package and schedule it to run using Job Scheduling Wizard.--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-06 : 09:59:41
|
| Create a stored procedure on the source database which takes a backup.Create a stored procedure on the destination which looks for the backup file and when it appears copies it to the local server and then restores the backup.For the backup I would create it asdbname_full_tmp.bakthen rename it to <dbname>_full_yyyymmdd_hhmm.bakScedule the two jobs on there respective servers at asppropriate times. The retore SP can be scheduled to run at intervals to wait for the file. You could run the backup and restore all from the source server - or start a job on the destination server to do the restore but I prefer to keep these completely separate.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-02-06 : 17:32:47
|
| The backup approach will be faster, but it will rely on getting exclusive access to the database on the destination server, which could be a problem if you have lots of automated stuff hitting the DB. It's still doable, just more prone to failure, I would think.Still, it's the way I'd do it if I could ensure that the restore would go smoothly (that no user or automation would be constantly hitting the DB).Cheers-b |
|
|
|
Phong
Starting Member
3 Posts |
Posted - 2002-02-27 : 08:59:13
|
| 1) Create a Trusted Connection between the 2 servers2) Write a trigger(s) to update the second serverPlease make sure your network traffic and your database activities are not too heavy.Good Luck!Phong |
|
|
|
|
|
|
Job to synchronize databases on two servers