Backing Up And Restoring Transactional Replication
Dec 29, 2006
From Help
Transactional replication includes the sync with backup option, which can be set on the distribution database and the publication database:
It is recommended to set this option on the distribution database in all cases.
Setting this option on the distribution database ensures that transactions in the log of the publication database will not be truncated until they have been backed up at the distribution database. The distribution database can be restored to the last backup, and any missing transactions are delivered from the publication database to the distribution database; replication continues unaffected.
Does database will not be truncated until they have been backed up at the distribution database mean that the database itself needs to be backed up OR the log file with it?
Hi, I am in a production database server. Data has been added and deleted via the web. So there is alot of traffice in and out of the database. How can I make a backup plan to make sure I am not lossing any input or deleted data . I am familiar with backing up a database in regular intervals, but I amnot sure if this is the best way in this senario.
How can I take advantage of the transaction log in restoring the database.
What is the best way (short of backing up the entire DB) to make a copy of a Table so that It can be easily restored. We have a table that we want to make some serious changes to, but I want to make sure I can restore if if I need to (if the changes don't work)
I want to reformat my servers harddrive and install a newer operatingsystem (Win 2003). I am running SQL Server 2000 with about 10databases. What is the best way to backup and restore SQL Server forthis so I don't lose anything.Thanks,Rick
I have been using the backup feature of SQL Express for some time and I thought it would be good to test a restore in case the worst happend. I moved my bak files to a usb memory stick and copied them to a machine and installed SQL Express so its empty no database's etc. So I right click the databae folder and choose restore from device and point to the db's but I get errors restoring them. What am I doing wrong? Is their any guides that give steps to backup and restore database's ? Any help would be great - I can of course provide more information need be.
Hi all, plz help... I have an asp.net login control on my website; my users are stored in a SQL DB; The problem is when I back-up my DB from my PC and restore it to my server; the logins doesn't work anymore. I mean the user I've created on my PC can't login on the website on the internet... Thanks a lot...
i wanted to test routines using my local sql server engine and thought I could backup the mdf ldf file on the true network sql server in which I am listed as a dbowner with full permissions. In fact i can add users for the at db etc.The backup allows me to browse to a local folder
with the following being the default C:Program FilesMicrosoft SQL ServerMSSQL10_50.CMS_PROJECTMSSQLBackup
i name the file and it executes properly but when i try to restore that file to my local db that path is not even visible to me.In fact when i use exploreer I cannot even find the folders after Microsoft SQL Server!I search for .bak but nothing.How can I see that folder construction in the sql browser but never in explorer -
I am trying to backup and restore a 20GB SQL database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues:
1) The developers [against best practice] have stored multiple images in fields within the database as binary code.
This therefore exceeds the 65532 character limit in some fields, so even though the images do show [based upon the data saved within this field], I cannot find the data in the field beyond this 65532 limit, within SQL Server.
How can I export / locate this data after the 65532 character limit?
2) When I have attempted to restore the database I am getting this error message:
Restore of database 'zapkam' failed. (Microsoft.SqlServer.Management.RelationalEngineTa sks)
Additional information:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (1:1592996) in database "zapkam" as read from the backup set. (Microsoft.SqlServer.SmoExtended)
I have managed to restore two other smaller databases using the same technique, but am wondering if it's an issue with the database itself.
3) I have uploaded this database to the new server using FileZilla FTP Client, but it has cut out, painfully at 80% + 90% on a couple of occasions.
Is there a better solution for uploading these big files that I could possibly use? For example, uploading table by table or similar...
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology? Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example... Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
On my distribution server there is a directory called mssql7 epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.
Hi, I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval. So pls suggest me . I am using Sql 7.0 with service pack2.
I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher). If anybody knows how to solve it, please tell me
I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -
The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'. Access is denied.
The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the ( epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.
I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...
Hi guys can anyone help me with this issue. if so thankyou kindly.
1. created transactional publication (for tables, stored procs & views ) 2. created empty database at subscriber 3. set up pull subscription at subscriber 4. ran the initial snapshot agent at the publisher 5. checked the schema files & found out it hadn't scripted the primary keys on the tables.
Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.
? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?
? Should I break my publications into 3; say 1 for each object?
Hello all! I am setting up my first Replicated database, and want to get this right.
We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.
What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?
I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.
I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.
I want to set up the transactional replication. How many service accounts we need and what kind of permissions it required in windows level?Only two servers we are using. Pub/Distributor and Subsciber.Which option is better Push or pull?
at the 'specify articles" on the wizard. Some tables have a key next to it that doesn't let me select them to be replicated. This is happens on transactional replication. But if i do a snapshot, the keys next to the tables are not there.
Need help guys... I'm new to SQL Replication, though I successfully created a transactional replication testing on my machine using PUBS DB and a separate DB.
I'm planning to establish/setup a Transactional Replication. There are two SQL Servers, one is being used by WebApp_1 and the other is being used by WebApp_2. Both servers are maintain by different administrators and situated on different domain/location (both used Win2003 Server). We will transfer some data from Server_1 to Server_2. Server_1 will be setup as Publisher and the other is the Subscriber, then additonalServer_3 will be added and will serve as the Distibutor.
My questions are:
1. Do I need to request separate domain account from the two different servers to establish connection to the Distributor?
2. Will the User databases from Publisher in compromise/affected as far as database settings and data are concerned?
Hi There, I want to know how to backup and restore database from VB.net. ie I am trying to put two buttons on my form, Backup and Restore. When I click on Backup a Backup of the SQL Database with extension of *.bak is created. Similarly when I click on restore and select a database with *.bak extension it should restore that database. I saw a software where it created a backup of Sql database with extension of .zip containing the bak file which is password protected. when u restore the database it automatically gets unzipped and the database is restored. Can anyone help me. Thanks in advance. Regards, Amit
I'm still pretty new when it comes to SQL server and I'd like to get some opinions about replication. I need to set up replication between 10 sites. The database is about 2 GB and at each size about 10 users are entering data. The databases need to be synchronized at least 3 times a day. Now our application developer told us to use transactional replication while I'm thinking of using merge replication.
Can anyone give me some more advice about the pro and cons of either replication type. TIA
We have 3 sites & would like to do replication btw the 3 sites. 1 publisher & 2 subscribers. All 3 sites should be able to do updates. Subscribers should be able to operate even if publisher is down. Any advice will be appreciated.
I have a small problem with the transactional replication when I run the initial snapshot and distributing some of the stored procedures. For eg., Say if the stored procedure has the following statements:
--------------------------------------- create procedure abc as declare @paytype varchar(50) set @paytype='check'
insert into acct select cusname,cusacctno,@paytype from cuspay ---------------------------------------
I get the following error: for statement:
set @paytype='check' -------------------- Invalid column name 'check'. (Source: ServerSQL1 (Data source); Error number: 207)
I tried changing to double quotes. even tried adding article using query analyzer without using EM
Does SQL Server 2000 (Enterprise Edition), mandate the creation of a GUID on each table used in transactional replication?
I was told that in addition to defining a Primary Key, I "MUST" also define a GUID on each table. As I recall - this is not true. Can someone who's setup Transactional Replication please field this question?
