SQL LanguageSQL Language discussions not specific to a particular RDBMS program or vendor.

Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .

Hello everyone
How are you? I have a problem. I have two database in two pc's. One is central database and another is local database. Under the local database every day some transaction have been proceed. At the end of the day i want to shift the local pc's database to central database. How can i do that? Is there any third party software? Is there any middle layer software that done my job? Can any one help me to provide expertise answer or give me some idea? Any idea will be apriciated. Thanks all again

If you want to transfer the new transactions from the local db to the central one you can use replication between the two databases or you can use SSIS to perform the data transfer and get a scheduled sql server job run this operation at the end of the day.
If you want the central database clients to connect to the local database instead of the central database at the end of the day you can get the middle tier (or a web service) do that.

Thanks subportal. Its good idea. I have some more info for you. I think it will help you to understand my scenario. I have two pc where SQL server is running. I have also a Central database station. Suppose every day in the local site handled 100 transaction in its own mechine at the end of the day i want to transfer this 100 transaction in central database. It is mention that i dont want to transfer the previous days transaction. I only transfer the current days transaction. How can i do this? Are there any s/w, service or agent who can do it for me? Thanks again. Keep well.

The best solution to such a scenario is to use Transaction Log Shipping.
Log shipping works as follows:
•Suppose we have two servers (one is called PrimServer and the other is called SecServer).The SecServer has a database called CentralDB and PrimServer has a database called LocalDB.
•The PrimServer is configured this way:
-A shared folder Called Backup on the D: drive
-A SQL Server job called BackupJob. This job is scheduled to run at 11:45 PM every day to backup the transaction log file of the LocalDB to and save the backup file in the shared folder.
•The SecServer is configured this way:
-A folder called Restore on the E: drive
-A SQL Server job Called CopyJob. This job is scheduled to run every day at 11:50 PM to copy the backup file made by the BackupJob on the PrimServer to the Restore folder
-A SQL Server job called RestoreJob. This job is scheduled to run every day at 12:00 AM to restore the log file copied by the previous job to the CentralDB

That’s it. I will explain how to configure through the following few lines.
•Right click the LocalDB node in the Object Explorer and choose Tasks, and click Ship Transaction Logs…
•Click Enable this as a primary database in a log shipping configuration check box
•Click the Backup Settings… button
•In the text box Network path enter the path of the shared folder (e.g \\PrimServer\Backup)
•Under the Backup job option click the Schedule… button.
•In the up dialog and under the Daily frequency option, beside Occurs at once option specify 11:45 PM (as an example). This is the moment you want the backup job to run
•Click Ok on the opened dialogs back to the Database properties dialog
•On this dialog click Add… button under the Secondary databases option
•On the new dialog click the connect button and connect to the SecServer and then choose the database name CentralDB
•If you have made a previous Full backup to the LocalDB and the CentralDB is already created(restore that full backup to the CentralDB and leave it in the NoRecovery mode.), then choose the option ‘No, the secondary database is initialized’. Otherwise choose the option ‘Yes generate a Full backup of the primary database……’ and in the dialog that appears after clicking the Restore Settings specify the folder of the full back file and then click ok
•Click the Copy Files tab
•In the Destination folder text box enter E:\Restore (as an example for the folder on the SecServer to get the backup files copied in.)
•Under the Copy job option configure the job schedule (Occurs at once option specify 11:50 PM)
•Under the Restore Transaction Log tab and under the Restore Job option schedule the job (Occurs at once option specify 12:00 AM)
•On the Secondary Database Settings dialog click Ok.

Make sure that the SQL Server Agent Service is running on both servers.

Subportal
At first i am sorry for late response. I was in vacation. Thank you for your cordial reply. After my vacation i try to use transaction log shiping. At first i apply the mechanism for two server where i try to transfer data from one database of primserver to seconserver. It successfully back up the database but in the time of restore the database the following error occurs in the log file

Error: 14421, Severity: 16, State: 1.

The log shipping secondary database GALAXY\GALAXYSQL.DataTransferTo has restore threshold of 45 minutes and is out of sync. No restore was performed for 137 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information

Where is the problem? Can you help me to write more detail please? I just wait for your replay.