Recovery :: Copy Large Database To Another And Keep Them Synchronized?

Sep 20, 2015

We have a situation that requires converting all INT columns from source database to BIGINT in destination.

By ‘copying’ term, I consider any operation like insert-select, merge, asynchronous replication or any other that will get all data synchronized until some moment in time, i.e. 2015-12-31 10:00 PST when downtime is planned. Before that moment it isn’t important if data in source and destination DB are in sync.

Copying process starts early enough to allow appliance of any technique/technology – there’s enough time to find best solution.

Requirements:

1. Destination database is superset of source, having all tables and all columns plus few more columns in few tables. Additional columns have default NULL or they are computed. INT columns in source are BIGINT in destination.

2. If source and destination are not in sync at the moment downtime begins, it is allowed to have few minutes to wait for synchronization. For instance, if transaction logs from last hour or so need to be shipped, that would be ok. If large table need to be synchronized by comparing row-by-row, it’s not ok.

3. Source database performance must not be compromised by any copying if it’s done before scheduled downtime. There are couple of options I have in mind involving MERGE command but its appliance might be limited by current DB design. Additionally, based on business logic, I cannot be absolutely sure if some row, created while ago, wasn’t changed meanwhile.

For some large tables, there is no indicator at all when any row has changed – I cannot rely on IDs nor there is column like ‘[date last changed] DATETIME’

I have been reading about various kinds of replication, i.e. log shipping but I’m not clear if any of these is applicable. Key points, if there is replication involved, are copying process must start from empty destination database plus synchronization must not compromise source DB performance So, how to do this using any kind of replication?

View 17 Replies


ADVERTISEMENT

Copy Large Database

May 17, 2004

Does anyone know of a good way to copy a large database or backup of a database (~10 GB) across long distances (transatlantic) in a minimal amount of time?

I have tried regular file copies, BCP, DTC, my own handwritten extraction utility, PCAnywhere, and several file splitters, all to no avail. To complicate things, the source is SQL 7 with a different code page from the destination, which is SQL 2K. The file copies always seem to get interrupted after several hours.

View 6 Replies View Related

Shrink Log File In Specific Database In Mirror / Synchronized / Restoring State

Aug 12, 2015

I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).

View 4 Replies View Related

Recovery :: Pages On A Full Recovery Model Database Corrupted

Sep 17, 2015

Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.

View 4 Replies View Related

Copy A Column With Large Data

Jul 16, 2015

I have run the select query which returned one row. There is one column in it which has got large amount of data. I want to copy the complete content of that column(varchar(max)), but I am unable to do it. It's not the xml data. I don't want to do any conversions.

View 1 Replies View Related

Recovery :: AG Database In Reverting / In Recovery State

Nov 1, 2015

We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.

The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?

View 2 Replies View Related

Recovery :: Maintenance Task To Copy Bak File To Secondary Drive After Backup Is Complete?

Nov 6, 2015

I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.

An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.

View 7 Replies View Related

The Remote Copy Of Database X Has Not Been Rolled Forward To A Point In Time That Is Encompassed In The Local Copy

May 11, 2006

Hi,

I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:


alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go


The error message is:

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)

1. backup database DBmirrorTest on SQL1

2. backup database log

3. copy db and log backup files to SQL2

4. restore db with norecovery

5. restore log with norecovery

6. create endpoints on both SQL1 and SQL2

CREATE ENDPOINT [Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER)

7. enable mirror on mirror server SQL2

:connect SQL2

alter database DBmirrorTest

Set Partner = N'TCP://SQL1.mycom.com:5022';

go

8. Enable mirror on primary server SQL1

:connect SQL1

alter database DBmirrorTest

Set Partner = N'TCP://SQL2.mycom.com:5022';

go

This is where I got the error.

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy



Thanks for any help,

KT

View 8 Replies View Related

How To Keep Several Databases Synchronized???

Oct 20, 2000

I have a question about synchronizing databases. Let's say that I have 2 SQL Server 7 databases on 2 different computers running SQL Server 7.

The databases contain various related tables and are used to mange subscribers, contactpersonell and other data. In the databses subscribers are added, deleted and updated frequently so my problem is to keep the 2 databses that are located on different computers synchronized. If a subscriber is deleted on one computer, then it should be deleted in the other database too etc. These subscribers are identified by a subscriberID.

Is it possible in some (easy) way to do this synchronization?

And how should I specifiy where the databases are located so that my stored procedure (or whatever does this) knows where the databases and tables are located? I mean, if the databases are located for example in 2 different cities.

Thanks for all help!

Regards, Bob

View 1 Replies View Related

Synchronized Versions

Jul 23, 2005

I'm running a SQL 7 db on a LAN in city A with other users accessing it fromanother city (city B) over a WAN. Obviously the WAN users are getting muchslower access to the data. One thing I was wondering was if it was possibleto have two versions of the database, one on each server, and have themautomatically synchronize as users at each location make changes. Or, ifthat's not possible, then at least have the city B users get any changesthat the city A users get as they are made.Thanks for any input.Neil

View 5 Replies View Related

Last Time Synchronized

Feb 9, 2007

I use SQLCE2.0 and merge replication.

Is there a way to query SQLCE to find the time of the last synchronization without manually tracking the time myself.

Thank you.

View 1 Replies View Related

Copy A Database With Copy The .mdf File And Attaching It With A New Name?

Nov 4, 2006

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus





View 6 Replies View Related

SQL 2012 :: Shrink Log In Synchronized DBs

Feb 23, 2015

I have two server A & B and I have always on high availability turned on SQL14.

I need to shrink the log on server A and it's fine if it also needs to shrink B.

My problem is that when I run my shrink command I get:

"cannot be shrunk until all secondaries have moved past the point where the log was added.."

I think I need to drop the High availability on the servers and then run this:

USE iTest2_Claims;
GO
ALTER DATABASE iTest2_Claims
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (iTest2_Claims_Log, 10);
GO
ALTER DATABASE iTest2_Claims
SET RECOVERY FULL;
GO

If the only way to shrink my log file is to drop high availibity how do I do that in script? and how risky is it that the high availaibity will not re-connect?

View 2 Replies View Related

Setting Up A Web Synchronized Subscription For A Smart Client

Nov 20, 2005

So in a previous thread I discovered that in order to actually subscribe to any publication, the publisher needs to be a well-known network name, requiring DNS resolution. You can't simply point a SQLExpress instance at an ip addressinstance and have it resolve the communications.

View 5 Replies View Related

The Time Stamp Counter Of CPU On Scheduler Id 1 Is Not Synchronized With Other CPUs.

Sep 25, 2007


SQL Express 2005 with one instance on a networked dedicated stand-alone window XP€¦
We use this for custom software written in VB.net. We are not SQL experts€¦
Question 1) I am getting a event viewer warning that says
The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
This seems strange, since there is only one CPU on this machine. Any ideas?
Question 2) In SQL Server Management Studio Express, we do not have the actual data files attached to the instance, but the custom program does use the actual data files just fine, defined by the connection string. Is there any pro/con to having them attached in SQL Server Management Studio Express?
Thanks!
Bob

View 4 Replies View Related

The Time Stamp Counter Of CPU On Scheduler Id 1 Is Not Synchronized With Other CPUs.

Feb 23, 2008



Hi,
Get some errors in my log, does anyone knows how I can turn off all logging?

Indeed error should be investigated, but for the moment I just need to turn off all loggings.

Cheers!
Rickard

View 1 Replies View Related

The Time Stamp Counter Of CPU On Scheduler Id 5 Is Not Synchronized With Other CPUs.

Feb 17, 2007

64 bit sql 2005 - SP2 CTP is creating several of this messages in the log. Could somebody tell me what is the impact of these on the performance?

Also, could anybody from MS have any idea as to the release date of SP2 ( Non CTP)

View 24 Replies View Related

How To Copy A Database Table And Copy It Into A Different Database?

Oct 28, 2006

Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?

View 1 Replies View Related

Question About Move Large Amount Of Data From Database To Database

Apr 23, 2007

guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!

View 8 Replies View Related

SQL 2012 :: Database Recovery From Corrupt Database File?

Jul 9, 2014

Last week I backed up my SQL Server by using BE 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.what causes this?

View 7 Replies View Related

Recovery Of Database

Nov 30, 2000

Running SQL version 7 on "Terminal" Server I have a database marked in Load Receovery and we are unable to get to it. We tried MS SQL document on how to but we are still unable to recover it!

View 4 Replies View Related

Database Recovery

Oct 4, 2007

hai!
i have a database which crashed recently(26th sep 2007).my last backup is on 14 sep 2007.i have restored database after two days.Now i have a database (old) with data upto 14sep 2007 and from 26sep to till date.(data from 14sep to 26sep missing).I restored database on 28th with a new database name which has data upto 26th sep 2007.how to attach these two databases to a single database to have data from beginning to tilldate.

please help in this regard.

View 1 Replies View Related

Database Recovery

Jan 22, 2008

Hi,
I made an UPDATE query without specifying any criteria so I had more than 1500 records have been changed and I cannot undo the UPDATE.
Is there any way I can recover the data UPDATE from the Transactions Log.
I did not back up the database lately so what is the appropriate solutions for this?

Any thoughts… :S

View 10 Replies View Related

Database Recovery

Mar 10, 2004

Hi,

Is there a way to Revover a Sql Server 2000 database in the absence of the backup file(the backup file got overwritten). Does Sql Server do any automatic Transaction logging?

Thanks for any input

View 4 Replies View Related

Database Recovery..

May 23, 2008

Hai friends....

SQL Server 2005 Database, So I want to know about Database Backup and Pointing Recovery concepts..so can u please help me and any send me related documents...


---
Thanks,
Nageswar.V
New Horizons Cybersoft Ltd
+919848854114 : nageswar@nhclindia.com

View 2 Replies View Related

Database Recovery

Jul 23, 2005

Hi,I'm no where close to a SQL Server expert but we're using it as a back endto another application.I lost a PC that had MS SQL Server 2000. I was able to recover the .MDF and..LDF files but nothing else. How can I restore these files to another SQLServer installation on another PC. When I tried to attach them they are notrecognized as valid files (I get the red X instead of the green CHECK).Is this a basic security function that I'm not going to get around or shouldit be possible? Do the .mdf and .ldf files contain specific info about whatPC they were created on?Are there third party apps that might be able to at least extract thetables?Thanks,WH

View 1 Replies View Related

Database (In Recovery)

Jul 15, 2007

Hi all



When I restart server my database apear as database(In recovery )



what's ???



thanks

View 3 Replies View Related

Database In Recovery

Jul 30, 2007

Hello all,

Is there a way to check the status of a database in recovery. Like how far along it may be. If not is there a way to stop a database in recovery and just drop it.

Thanks in advance,

Mike

View 7 Replies View Related

Database In Recovery

Mar 30, 2007

Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery. Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?

View 12 Replies View Related

Database In Recovery

Aug 9, 2007

What does it actually mean when the database is in recovery? My database has been in recovery overnight. What is normally going on in this situation. Should I wait or take more drastic action?

View 10 Replies View Related

Help!! Database Always In Recovery...

Aug 28, 2007

Hi all,

I had to change the path of .mdf and .ldf files, so I decided to:

1) Take offline the database

2) run the quey ALTER DATABASE... MODIFY to change the path

3) Bring online the database.

The last step hung up (with no errors) and left the database In Recovery.

When I tried to stop and restart sql server other databases changed their status In Recovery...


Here is a dump of Errorlog files

2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'.
...
...
2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:
2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24
2007-08-26 18:13:30.09 spid24s *
2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334
2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
2007-08-26 18:13:30.09 spid24s * SPID: 24
2007-08-26 18:13:30.09 spid24s * Process ID: 1380
.....
.....
2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.
2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.
2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details

Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ?


Thanks in advance

View 9 Replies View Related

Recovery :: Server Local High Availability Using Failover Cluster And Disaster Recovery Using AlwaysOn

Aug 17, 2015

We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.

View 6 Replies View Related

Large DataBase

Jul 23, 2007

What happens if you have a website and the hard drive on your server is say 250GB. Then the database exceeds that and the database is 300GB in size.How would you spread your database into two different hard drives?Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved