Merging 50 Databases Into One On A Nightly Basis
Sep 13, 2007
How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
Kev
View 1 Replies
ADVERTISEMENT
Dec 10, 2005
I need some assistence here.I have two servers. One with MS SQL 2000 Standard edition and MSDE(MSdesktop engine).I need to sync my databases nightly, as a backup.Is there a cheap software that will do this? I don't want to justbackup the entire databases files b/c they are large and I'mtransfering them over DSL.I was thinking transaction logging, but I'm not sure. Is there a easyway to do this?I don't know how to accomplish this task.PLEASE HELP.
View 9 Replies
View Related
Aug 17, 2006
I'm pretty new to SQL altogether. I kinda gave myself a crash-course when the software I was using, Papertrack, was having problems. It all started when I tried to move the database. Now there are two separate databases that I can see on SQL Enterprise manager. I picked up the book and figured some things out, but still don't know how to merge the databases. I called the support for the software and got to know them on a middle-name basis. Is there a way I can merge the two databases so that the software can pull queries from both and access the files?
To anyone that likes a challenge,
Thanks
View 6 Replies
View Related
Mar 15, 2007
Hello, As is known, when developing an application in VWDE that features membership as well the application's own database, you have a total of 2 databases. Seeing as a lot of hosters charge per database instance, to make the membership utility viable, it would be prudent to merge each database into one. Quite a few blogs/tutorials, including ScottGu's, recommend merging, but I'm as yet to find one that actually explains the merging process step by step. Advice such as, "run each script against the same database," simply isn't enough.If anyone could shed any light on this topic, it would be much appreciated. Of note, I have SQL Server Management Studio as the recommended deployment tool. If one thing's becoming increasing evident, it's that it would be well worthwhile in finding a host that supports SQL Server 2005 as opposed to 2000. Kind regards.
View 2 Replies
View Related
Apr 6, 2006
How do I merge three SQL Server databases into one. There will be some duplicated information in some of the tables. Any help much appreciated. Thanks.
P.S. Running SQL v7 on NT!!
View 1 Replies
View Related
Sep 14, 2007
i have two databases one has
firstname, lastname
and the othere
firstname, lastname, emailaddress
how do i get to merge the data from emailaddress to the 1st datbase?
Melvin Felicien
IT Manager
DCG Properties Limited
View 6 Replies
View Related
Oct 20, 2000
Hi
I have a problem out here. I have two databases with the same table structure
and I want to merge data from both the tables . Please can anyone let me know.
Iam using Sql Server 7.0.
Thanks
Bye
View 1 Replies
View Related
Jan 7, 2008
Hi,
I have two databases lets say DB1 and DB2.
Schemas for both databases is same.
In both database schemas there are tables which has identity columns as primary key.
Now i want to merge these two databases in a single database say DB3.
It may also possible that some master records in both databases are common so they should not repeat in DB3
Is there any way so that i can do it quickly and as soon as possible.
Thanks in advance
Rohit
View 1 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Apr 30, 2008
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
View 4 Replies
View Related
Sep 19, 2007
Please help.I have Database A (Archived data), Database B (Transactional data).Both A and B do not have any timestamps in any tables.I need to bring A (only once because it is archived data) and B withonly differentials into Database C for reporting purposes.Currently C is cleared before loading B with differentials every nightand A is not loaded since it takes 15 hours to load.Now I want to bring A once and leave it and then bring B every nightwith differentials without clearing C.Please suggest some solutions.Thank You
View 1 Replies
View Related
Jan 24, 2008
Do you guys do the DBCC CHECKDB on system tables TEMPDB, MASTER, MODEL, MSDB.
When do you run the DBCC UPDATEUSAGE
First i do the following at night
Check database integrity
Rebuild Indexes
Backup Full
Remove old bak
clean up history of all bak
clean up history logs
I was wondering when to put in dbcc updateusage
I look forward to your comments
View 11 Replies
View Related
Jul 3, 2007
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.
The step being executed by the Job is "Ren C:MyTestFile.csv C:MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.
I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."
As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.
I would appreciate any assistance anyone could provide. Thanks in advance!
View 1 Replies
View Related
Oct 22, 2004
I am using SQL Server 2000 and trying to create a disaster recovery strategy that would run nightly and backup the database or at least the changes and would ftp these to a secure ftp site. For smaller database it is easy, I just take a full backup, zip up the file and ftp it to the secure backup site. This strategy does not work so well when the zipped up database is still close to 3GB. I have a pretty big window for doing everything but 3GB is just too much to ftp overnight. The recovery model is simple so the only other option seemed to be do a full backup once a month and take differentials nightly. The problem is I am offsight and the client may need to take a full backup during the day and my nightly differential would get screwed up.
There is a fairly low volume of transactions so the idea of just doing nightly backups on the data that has changed is the obvious choice but differentials don't seem to fit. Any ideas?
Thanks,
TH
View 2 Replies
View Related
May 20, 2008
I have a production database that I would like to have copied over to a backup database on a separate server every evening. I don't want to mirror, I just want the databases synced up every evening.
The servers are physically attached through a gigabit switch and the database is relatively small, so I don't think that speed will be an issue.
Could someone point me to an article about the best way to accomplish this?
Thanks.
http://www.dynamicajax.com
View 2 Replies
View Related
Apr 22, 2008
Quick question..
If I'm log-shipping data from a database can I still perform a full backup of it each night without it affecting the log sequence?
View 7 Replies
View Related
Nov 6, 2006
I know this is probably an easy thing to do but I've been struggling with this on and off for the past month or so. All I want to do is copy two databases (our app db's) to two other databases (for app testing and training) on the same server every night. The databases aren't used during this time so they can be taken down if necessary.
I've tried using the Copy Database wizard and scheduling the resulting job but that isn't working...
Thanks in advance for any direction/help!
View 8 Replies
View Related
Feb 9, 1999
Does anyone see any negative consequences to stopping and starting SQL Server nightly?
View 2 Replies
View Related
Aug 23, 2002
I need some help. I'm running Win2000 w/SQL 2000 Active/Active cluster. I've started having problems where one node will fail over automatically every night. The times are different each night and the only thing that shows up in the log file is the SQL Server terminated due to stop request from the service control manager. I can't find any error to point me in any direction. Does anyone have any idea what could be causing this?
Thanks,
Jeff
View 1 Replies
View Related
Aug 29, 2006
where in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 Replies
View Related
Feb 4, 2008
I run a nightly full backup of the databases and then back up the transaction log. After completing the full database backup and the transaction log, I then update all existing statistics.
Is this a good practice to do on a nightly basis? I drop and re-create indexes on all the databases once a week. Does updating the statistics really help?
View 3 Replies
View Related
Feb 2, 2007
Hello, we are running MSSQL 2005 express on a windows 2003 machine. I have looked in SQL Server Management Studio for close to an hour and could not find a way to setup automatic backups of databases. Where is this feature, and short tutorial would be great. Thanks!
View 1 Replies
View Related
Apr 11, 2007
2:30:03 AM SQL Server Scheduled Job 'Our Job Name' (0x888B36086CCCC34FB353B408DEBE0432) - Status: Failed - Invoked on: 2007-04-11 02:30:00 - Message: The job failed. The Job was invoked by Schedule 30 (Our Job Name). The last step to run was step 1 (Our Job Name).
Where do you begin to discover the cause of the crash?
View 6 Replies
View Related
Mar 21, 2008
How do I format a date value in SQL 2000 sp? Tired the following none are returning the quivilent rates. What is the syntax please?
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008')
SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')
View 3 Replies
View Related
Apr 3, 2000
I have to import data into 2 tables on a daily basis.
The data is provided as a flatfile.
In order to fullfill this task the tables have to be truncated first.
Are there any possibilities to do this job automatically with dts, or do I
have to write an Interface
in VB or something like that?
Thanx
Michael F.
info@sunguard-explorers.de
View 2 Replies
View Related
Apr 12, 2007
hello guys !
i need a little help in an update statement.
i have two tables as
table1(id1,name)
table2(id2,date,val2)
what i want is to set val2=45 in table2 where date=4/4/2007 (say) and id2, now id is to be obtained from table1 based on the name;
like
table1
---------
(1,"akmal")
(2,"peter")
(3,"kamal")
I know the name as peter, but i want to used its id1 (2) in the update statement. You might have got the idea that id2 is a foreign key referring to id1.
How to do that ?
View 2 Replies
View Related
Apr 11, 2008
Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)
it gives me Result as,
Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2
i want count on weekly basis
Could any one help me?
View 8 Replies
View Related
Dec 4, 2006
Hello friends!
I want to set my job agent to schedule for second basis but in EM i found by right clicking job in EDIT Recuring Job Schedule i m getting option in Hr and MM but not in SS so where i can set this in seconds (SS) or through QA????
T.I.A
View 2 Replies
View Related
Nov 5, 2007
Currently database1 and database2 are planned to be synchronized with the interval of 5 seconds!! ( dont know exact, but i select run continoulsly on subscriber screen )
This could cause serious performance issues as both applications will be live on difference servers.
how can i Synchronization should be performed on day basis or it should be modification basis to avoid any performance issue on live application
i am using merge replication
Local distributor
and subscriber on another server .
plz help me
its urgent
View 3 Replies
View Related
May 26, 2006
Hi all of you,
I'm just a newbie with XML. Now we're moving all our DTS to SSIS packages. Most of them are simply processes that takes a plain file and loading these data into Sql table or in inverse way. So that I wonder what role will play XML here.
I mean, are there big differences between store data as XML format and nvarchar?
That sort of stuff.
Thanks in advance for any input,
View 5 Replies
View Related
Mar 20, 2008
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
View 2 Replies
View Related
May 18, 2004
hi guys.
I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..
any suggestions..??
Thanks in advance..
:)
View 1 Replies
View Related
Aug 16, 2006
Hi All,
I have recently upgraded one of our servers using the in place upgrade method to SQL Server 2005 from SQL Server 2000.
We are currently having an issue with stored procedures as they appear to be running slowly and the only way to correct this issue is to recompile them on a daily basis.
Has any one else had a similar issue, any ideas what could be causing this to occur.
Thanks.
View 1 Replies
View Related
