Reverting Back To Production Server After Standby
Jul 15, 2001
We need to perform some maintainence on our database server which may take quite some time. We have a standby server which we constantly apply logs to.
We are planning to recover the standby server and use it while the production server is being maintained.
My question is how to I get back to using the original production machine.
The only way I can think of is to do a full backup on the standby machine,
do some transaction log backup and then restore these on the original production machine.
Is there any way to use the full backup from the original production machine and well as logs from the standby machine. When I try this I get the message that the database has not been rolled forward far enough.
In summary:
1. Backup full database on Prod Server
2. Restore full database on Standby Server
3. Backup transaction log (1) from Prod Server
4. Restore transaction log (1) on Standby Server
5. Recover database on Standby Server
6. Make modifications on Standby Server
7. Backup transaction log (2) on Standby Server
8. Restore full database on Prod Server
9. Restore transaction log (1) on Prod Server
10. Attempt to restore transaction log (2) fails.
View 3 Replies
ADVERTISEMENT
May 10, 2001
Question:: I know there is a switch to take a database that was upgraded to 7.0 back to 6.5. How does it work? If I upgrade a database and for 2 weeks input
data and then decide I need to go back to 6.5 -- do I lose the data from those 2 weeks or does it revert all information back to 6.5
View 1 Replies
View Related
May 12, 2008
We are using MSSQL 2005 and Log Shipping.
After making our secondary SQL server primary, how can we put the secondary SQL server back into standby mode?
View 1 Replies
View Related
Aug 3, 2015
I have user called DBA_USER he has db_owner access on (123DB) databaseĀ ....
But when ever my machine restarted the user was getting access error (i.e.. you don't have access on this database) and i checked his access on that particular database his access got reverted back ....
Here my question is why this particular user getting this type of error ....
View 7 Replies
View Related
Apr 24, 2007
I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express.
OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.
View 4 Replies
View Related
Nov 19, 2015
I have question regarding SQL Transactional Replication methodology
1. Let's say successfully created SQL Transactional Replication and running / transferring data from publisher to subscriber
2. Now one day the source production / publisher SQL Server is down and the remaining DR SQL Server is up (subscriber)
3. Next day, we fixed and bring up the production / publisher SQL Transactional Replication server, then how can we sync back all existing data records from subscriber into publisher side ?
View 3 Replies
View Related
Nov 14, 2007
I've been reading a million and one posts on replication
My scenario is that i have a live SQL 2000 server. In a DR invokation, i'e i've lost my live sever, i want to be able to access the same data at the DR (SQL 2000) site and have it accessable to the users. DR server has a different name to the live box.
Replicate
Master
CRMDatabase
Data changes all the time but can have hourly replication of transaction logs for this example. I've currently researched a sp called update logons but this has to be fed each account name to enable them on the new server. There must be a way to activate all CRMDatabase logons with the new server?
Could someone be kind enough to lead me through a step by step guide on the best solution.
Thanks a lot
David
View 3 Replies
View Related
Feb 28, 2015
I have a SQL Server 2008 R2 database.
I have modified the autogrowth configuration several times. The strangest thing... they keep reverting back to the original configuration.
View 7 Replies
View Related
Nov 9, 2007
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ???
is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?
View 1 Replies
View Related
May 11, 2007
Can someone provide a step by step tutorial for this? I'd like to safely update a database that is used for a website without much or any downtime.
View 1 Replies
View Related
May 20, 2015
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;
[Code] .....
View 6 Replies
View Related
Nov 19, 2007
Hi,
The standby file (.tuf file) got deleted accidentally on the secondary. How can I recover the database without the standby file?
View 3 Replies
View Related
Sep 22, 2006
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isnt completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
View 1 Replies
View Related
Dec 28, 2001
Hi
Could you suggest me some of the ways to have standby database server?
All of a sudden my company is in a need for standby database server in sync upto the 10 minutes. Main production database is about 8 GB. It serves almost 6 main sites for the company. No body in my group has worked with standby server issue before.
Any suggestion would help.
Thanks,
Linea
View 2 Replies
View Related
Oct 23, 2001
Hello !
I need to have a hot backup of my Production db
and i going to set up StandBy server : restore Production's backup
to StandBy
could you help me ?????
View 2 Replies
View Related
Dec 28, 2000
Hello,
I need to create standy server. My production server has SQL Server 7.0 and standby server has SQL Server 6.5. I've sync the data between two servers and is planning to do log shipping every 15 min. But I'm little confused, Can the version of Sql server be problemetic? Pls. anyone guide me.
Thanks.
Sejal
View 1 Replies
View Related
Feb 15, 2000
Can I implement the Standby SQL Server using the SQL Server Standard Edition? What's actually the difference between Standard and Enterprise Edition? As I know the Enterprise edition can be installed for up to 32 CPU while Standard edition can only be installed for up to 4 CPU.
Many Thanks in advance!
View 2 Replies
View Related
Jul 20, 2005
Hi:Anyone has a detailed procedure description about how to setup a standbyserver? Microsoft makes it sounds very easy which are more complicated.How to handle system databases, does standby server need to be exacthardware as the primary one?Thanks in advance,Xiaodong*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Feb 15, 2000
Can I implement the Standby SQL Server using the SQL Server Standard Edition? What's actually the difference between Standard and Enterprise Edition? As I know the Enterprise edition can be installed for up to 32 CPU while Standard edition can only be installed for up to 4 CPU.
Many Thanks in advance!
View 1 Replies
View Related
Aug 20, 1999
Where can I find a document that describes how to setup and configure a standby server?
I'm new to SQL Server 7 (I like it). We have a NT 4.0 server with SQL Srvr 7. We just bought another Box that will have the same thing on it with the idea if the 1st goes down, the new box is a 'hot' standby...of course a 'cold' standby will be ok too... I'm not fussy at this point. If you have info on both great.
I'm the SQL Srvr 7.0 DBA (my background is Oracle DBA). I've been doing alot of reading!!!
Can some provide the information I need or point me to someone I can contact?
Thanks
Ron Nixon (650) 940-6419
View 3 Replies
View Related
Feb 28, 2002
I hope to create a 'Standby' server for a critical web application. I am tempted to set up replication, but realize this task could also be done by a scheduled DTS job, which may be much simpler. I have read up on replication and have some reservations about it. A little about our environment: Data does not change very rapidly. Some days maybe no edits, some days maybe changes to 100 rows. The environment is stable, there are few DDL changes....development is essentially over. This system has 10 databases which it uses. Hence, all would need either replication or DTS Transfers. Intended publishing & subscribing servers are on same domain. Both are production servers. I Believe either Snapshot or Transactional would meet our needs if we replicate. There is no user need for replicated data. This is SOLELY a Disaster Recovery motivated situation. Reservations about replication include the hassles with identity columns, the hassles with Subscriber Databases being left in 'Read Only' mode (thereby slowing & complicating the recovery process), the difficulty in cleaning up a server after replication is halted if we abandon replication. My gut feeling is DTS might be best. There are 10 Databases. Sizes (inaccurately) reported are 5 at less than 5 MB, others at the following sizes: 10 mb, 24 mb, 26 mb, 39 mb and one big one: 3000 mb (3 gb). Latency is not a big issue. If these were DTS'd nightly that would be fine. Transactional Replication would be slightly better, but the amount of data change is not high. I would solicit anybody's input on this. Thanks,
View 3 Replies
View Related
Jan 14, 2004
We are maintaining a stand-by database server by doing a daily manual restore of logs. On weekends we do Reindexing of the tables for performance boost. some of these reindexing jobs take upto 10 Hrs
We have found that the restore of the logs taken in the source server after the reindexing job takes as much time it took to do the reindexing.
Typically 15 transaction log sessions for a day takes 40 minutes to restore. However the restore of the tranlog that was taken after a 10 Hr long reindexing job takes as much time(10 hrs) to do the restore also.
The size of the tranlog does not justify this time frame. ( Size of tran logs after reindex is more than other logs but is less than 3 GB)
I would like to know what exactly is happening during the restore of the tran log. Is it doing the reindexing in the destination system as well?
The destination server (standby) is in non recovered mode all the time.
The interesting thing is that I would only take 8 Hrs to do a full restore of the database. We are using ArcServ software for backup and restore.
We are on SQLServer Enterprise 7.0 sp4 on NT 4 Enterprise on source and destination.
We run SAP R/3 on a MSCS Clustered env.
The source database size is 260 GB
Any Comments?
Appreciate your replies
Cyriac
A
View 1 Replies
View Related
Dec 15, 2006
We have a process wherein the production DB is replicated to a server.subscriberA nightly and then a job processes the database for reporting services. Whenever the job fails we have to redo the process all over again. By the time we are finished it is already late in the day and our customers dont like it. Our solution for a standby server is as follows:
1. create another server.subscriberB and start same process from server.subscriberA. So, both servers are replicated from the Production server but SubscriberB is set to run half an hour delayed than subscriberA. The purpose is that should subscriberA process fails, we still have subscriberB. Then we could just change the datasource of the reports to subscriberB
2. replicate subscriberA.ReportServerTempDB to subscriberB.ReportServerTempDB
3. replicate subscriberA.ReportServer to subscriberB.ReportServer
Any insights highly appreciated...
View 2 Replies
View Related
Oct 29, 2007
I have some doubts on standby.
I will explain my environment.
I have two servers A and B both with Win2003 SP1
In the server A, i have a database sql server 2005 in compatibility mode -> 8.0.
In server B have SQL server 2005 and one database in standby / read-only mode. This base is getting to the transactional hour of the server A and applying in standby database. This works fine.
The problem I have is: I need to get the standby server B and create another database for test using yours files. I can get the files of server A because i have 512kpbs link and would be impractical.
Putting in the standby offline mode, i copying the files to another directory on the same machine, but when I try to create another database using the duplicate files of standby receive the following message:
Msg 1824, Level 16, State 1, Line 2 Can not attach a database that was being restored.
What is the command that I need to execute to attach the new bank without message Msg 1824. The command that executing is below:
USE master CREATE DATABASE ON homo (FILENAME = N'F:MSSQLDATAhomohomo_prd_homo.mdf '), (FILENAME = N'F:MSSQLloghomohomo_prd_log_homo.ldf ') FOR ATTACH
As a newcomer I do not know which command I run before attach to conclude restore.
I execute restore database with homo recovery but the database does not yet exist. If I run the restore standby database with recovery, I lose standby and need to download backup copy the entire database in server A to B using link.
If someone has any idea is welcome.
Gustavo
View 3 Replies
View Related
Aug 3, 1999
I have been asked to implement a Warm Standby using Transaction Logs on our main SQL Database - Version 6.5.
Can anyone give assistance regarding scripts, etc., which control the process.
Any assistance is much appreciated.
Regards,
Ian
View 1 Replies
View Related
May 9, 2007
I have one cluster (two machines) of sql server 2005 active/passive called "ClustA" with one Ent. server edition license and another separate server called "STDA" as warm standby server for "ClustA".
Do I need SQL Server license for server "STDA"?
Thanks
View 1 Replies
View Related
Sep 1, 2007
We have the following scenario:
Server A replicates Database A to Server B.
Server C has Database A on it as well, but in standby mode. We are applying the transaction logs generated by Database A on Server A to the database on Server C leaving it in standby mode each time.
Let's say we had planned maintenance for Server Aand dumped the last set of transactions on Server A in standby mode to be applied to to Server C. What happens to the replica on Server B? When I start to use Server C, can I backup its transactions and apply them to Server A, and then have those transactions replicated to Server B? And then what do I do when the maintenance is complete so that I can swithc back to Server A and have the replication continue on as before the maintenance to Server B?
Thanls
View 1 Replies
View Related
Jun 21, 2001
Can anyone direct me to a code that would automatically apply transaction logs to a DB in the standby server? We have a process that dumps the transaction log backup from the primary server into the backup server every hour on the hour, but I need to apply that transaction log as soon as it is in the standby server.
I am sure someone will ask, why not do transactional replication or log shipping? My answer to that is I have yet to learn how to setup replication between servers. I need to get our backup server up and running in the next few days.
Thanks in advance for your help.
Helen
View 1 Replies
View Related
Jul 20, 2005
SQL Users/DBAs,I'm trying to move data files around for a database that is in standbymode. I can detach/attach the database fine usingSP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the logsequence is broken and I can't restore any more logs. Does anyoneknow if there is a way to move around data files and keep the databasein standby mode?Thanks,JB
View 4 Replies
View Related
Dec 20, 2007
For the second time in two days, a completely rewritten procedure (using "alter procedure" statement) has reverted to an earlier version after hours of testing and confirmation. In both cases, there was 4-6 hours from successful final testing to discovery of the "reversion."
Since sql server has no memory of earlier procedure versions, I can only conclude:
1) someone restored an earlier version of the db (log says no)
2) someone acidentally ran a script for the old procedure (no evidence for that)
3) the database crashed and was rolled back as part of restore (but alter procedure is a ddl and, as such, is auto-commited so it cannot be rolled back)
None of these 3 possibilities seems to have occurred. Is there another possibility I'm missing?
View 5 Replies
View Related
Dec 28, 2006
The following list of actions leads to a corrupt database on SQL Server 2005.
Create a database snapshot
Drop a table in the database
Backup the database
Restore from backup
Revert to the snapshot
I'm not entirely surprised that it results in a corruption, what is surprising is that I can revert from a snapshot after restoring. There needs to be some kind of check to prevent reverting to a snapshot in a case like this. Until SQL Server prevents you from doing it, I'd recommend a best practice is to delete all snapshots before you restore a database so that you cannot do this by accident.
View 3 Replies
View Related
May 12, 2007
I have installed MSSQL 2005 on my D: drive of my windows 2003 server, but now need to install MSSQL version 2000 b/c I realized I have one less license than I thought I had. I have build no databases, only the default information. Can I install another instance (being the 2000 instance) on the same drive? If I can't, can I install it on the Crive without a lot of issues???? Or would it be easier to remove 2005 and just start from scratch? The sql version is 9.0. Any help or comments would be greatly appreciated.!!!!!
View 3 Replies
View Related
Mar 29, 1999
Hi
Is there anyway I can implement a standby server in SQL Server 6.5 ? In addition are there any tools in the market which I can use to perform a differential backup ?
I believe SQL Server 7.0 can do this, will I be able to use ARCserverIT or seagates's backup exec to perform these functions ?
Vijay
View 1 Replies
View Related
