Had to re-install SQL server and reformat the hard drive. Prior to doing this, detached the database and saved it. I have re-installed SQL Server (personal edition) and re-attached the database. Only problem is that the database shows "read-only" and I got an error when I unchecked the read only box. Any ideas?
View 4 RepliesWe 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 Relatedis bulk logged recovery model support point in time recovery
View 9 Replies View RelatedPages 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 Relatedin the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies View RelatedWe 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?
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
View 4 Replies View RelatedComing more from Oracle background, I want to do a point in timerecovery test on SQl Server. Let's say database PUBS backed up everynight and Trans log backup 3 time every 6 hours between 6 a.m. and 6p.m. , now we want to create a database PUBS2 and recover data upto2:00 p.m yesterday , i.e. I think applied backup from two night beforethen apply to trans log backup ?? Is that what wehave to and how toapply Trans log? I am not sure. Please script and advise.
View 1 Replies View RelatedI'm working to test out a recovery scenario involving a SQL 2008 R2 2 node cluster. I want to restore it to a standalone server. The nodes are VMware VMs using physical RDM for the shared storage. Using Veeam as the backup tool. Veeam doesn't work with physical RDM so I'm dumping the databases (all Simple) elsewhere to be picked up and replicated with Veeam. No problem there. For my DR I have a vanilla 2008 R2 server being replicated so that I can use it as a base to restore SQL. So far the testing has worked ok. What I'm trying to learn is the best way to restore the cluster to the standalone server and have it look the same to the affected app servers.
The cluster has several instances. In my testing, I've recreated the instances, but I don't think they show up as they do on the cluster. For example, if the Windows server name is SQL01, and I install an instance called SQL-APP, SELECT @@servername while connected to the instance still shows SQL01 but in production it shows SQL-APP. What do I need to do to get everything to match up? I suspect that, while I haven't seen any problems, there could be something I'm missing that would manifest itself later.
Also, in testing, am I better off to delete the cluster server account from AD and join the domain with the DR server under the same name? (this is an isolated bubble so no impact on production).For the purpose of this scenario, assume all other required servers (DC, DNS, application, etc.) are present in the test environment as they would be in production and nothing requires resources outside the bubble. The only item affected is the SQL cluster.
I have an urgent query that we are running SharePoint server 2013 on single server and installed SQL server 2012 on single server, our management decided that we need to configure failover cluster on existing SQL server. I want to confirm that it is possible that we can install failover clustering services on existing SQL server and then add an other server on SQL 2012 for high availability or do we need to first install failover cluster service and then install SQL server for failover clustering.
View 2 Replies View RelatedRecently I watched Microsoft Support WebCast: Microsoft SQL Server 7.0 Common Problems and Resolutions dated August 29, 2000. I am trying to find information for a total disaster recovery for my server. In the webcast the presenter made a reference to a Microsoft article Q240872, "How to Resolve Permission Issues When a Database is Moved Between SQL Servers?". I can't find this article. Since we are working with the scenerio of total diaster, we are starting with a new server. I know that when we tried to recover a database to a new server, the server recognized that the database did not originate on that server. Does anyone have any ideas? Or does anyone know how to contact Microsoft so I can find out how to get a copy of this article?
Thanks!
Hi,
How can i make sure that i dont have any data loss in the event of DB crash.
I take daily full database backup dump to the disk and every 4 hrs transaction log dump.
What shoud be the backup strategy to get 100 % database from the backup/ to get in point in time receovery.
Best regards,
Madhu
Got a 5.5GB database created on a Windows 2000 server with latest service packs.
Running under Exchange 6 (2000) with service packs.
Server went down and the client had no backup. This, despite having a similar (yet smaller) problem back in April this year.
I've run all sorts of utilities on the database file; third party, stuff from OnTrack, etc - nothing works.
Best result I've had has been 1.7GB of data - but a heck of a lot was missing.
I've tried running eseutil in all its guises - but still, using an edb viewer, plenty of corruption is reported and about 10% of the data comes out in the wash.
I think I have a problem in that I don't have Server 2000 or Exchange 2000 to test it with - but I doubt it is even possibly to test it.
That's the long and short of it.
To make matters worse, all the client did was copy the database folder to a dual-layer DVD and reformat the hard drive for use in another system - so I can't even examine it forensically to get a clue why it failed.
Any help?
I am part of a team developing a Disaster Recovery plan for our company, most of the data is in SQL Server 2000, and Visual Foxpro, what are the best options of backing up this data to one remote site, and being able to failover to those servers in case of a disaster.
[
I'm new to Sql Server and want to install SQL server alwayson clustering on my test environment. I followed the steps by this site: [URL] ....
On each node I can connect to SQL Server but why my 2nd node is not available so that I can add it as my 2ndary Replica?
According to this site, till "Always-On Availability Group Configuration" everything was successful.
hi to all,
I'm newbie with Sql Server,
I've a serious problem. After a crash of one of my disk I loose all the data about the SQL Server db but I've the mdf and ldf files because were placed on another hard disk different from the SQL Serve Installation disk. I reinstalled the Sql Server (in a new path, a new hard disk letter) but I don't know how to rebuild the db starting from the mdf/ldf files. Is it possible?
Please help me, I'm in a ugly Situation.
I need to know if anybody has any suggestions on websites to visits, documents or templates that will give examples on how to set up a Disaster Recovery Plan for SQL Server 7.0 Databases. I am mostly interested in the Documentation portion to acquire knowledge on the various methodologies used out there. I am a Jr DBA and would appreciate any feedback.
Thanks.
Is is true that in SQL Server 7, a point in time recovery can not read the active logs? So that any point in time recovery has to be with in some time frame of the backups of the logs?
Example:
If you have a full backup at 5 am.
Log backups every 2 hours.
You need to recover at 3:55. You would have to go back to your full backup from 2 am and the transaction logs from 6, 8, 10, 12, and 2.
You would lose any work done from 2-3:55pm?
Good Morning guys n Gals.
New user on these forums, so basically, hey, how are yas?
I have a bit of a situation, and have done a search - although not much info has turned up relating to my situation (maybe my search was just useless).
Anyways, basically, I run SQL Server 6.5 on NT4 server.
The disks were striped - not RAIDED.
What happened was, the 4th disk died, and we replaced it with a new blank drive.
Now, I am left with two DAT files (log and data) for my database.
I cannot remember how large these were created as it was many years ago.
I have no backups.
Reseting the status hasn't worked, also I have tried creating a new DB / Devices (in size similar to those reported in windows), and "moving" the devices to point to the original ones, but no luck: and thus was wondering if anybody had any hint / tips or ideas?
Cheers,
Jamie
I was able to take a set of Full backups and script my publication beofre they took the server down. they rebuilt the Server on different hardware with the same IP address and Server name. I then reinstalled my Named Instance and recovered system databases, distribution database and then my user Database with replication. everything seemed fine.
last night, a couple of jobs that use sp_smtp_sendmail failed with this error:
DTSStep_DTSExecuteSQLTask_2, Error = -2147217900 (80040E14) Error string: Cannot load the DLL xpsmtp80.dll, or one of the DLLs i... Process Exit Code 2. The step failed.
I googled it and I found that it may have something to do with DLLs not registered properly. Does anyone know how to fix this?
Can not attach database,
Error message;
The file you've specified is not a valid SQL Server database file.
My costumer HDD some files Deleted mybe elektric prblem -Elektric
go,com,go,come- then windows not opened.
insert corrupt hdd Another computer, then recovering (recover my files
software)
Recover mdf and ldf files. but I can not not attach mdf. file,
I am recovery .mdf file http://www.officerecovery.com/mssql/ some table ok.
but my importand table is not ok.
if I can recover another recovery software , my database correct recover or
same.?
I am using RecoverMyFiles.
Pleace help me.
Thanks.
Unfortunately a table is Deleted by me from my database.
Now How I can Recover that
I have no data and structure of that table Now.
It Was Very IMP. Table .
So Plz Help.......
How can I keep a production SQL server and DR SQL server in sync? I'm not referring to data - I can use SAN replication for that. I'm referring to the many configuration settings and objects that are external to the databases..Server settings (xp_cmdshell, etc.)LoginsServer- and database-level rolesServer- and database-level permissionsObject level permissionsMaintenance plans/SSIS packagesSQL Agent jobsJob schedulesDatabase mail configurationObjects (proxies, alerts, credentials, operators)Linked serversScheduled tasksODBC connections
I realize that many of these items can be scripted out, and periodically run on the DR server; but not all of them can be. And some, although they can be scripted, may be problematic to restore on a DR server, such as SQL Agent jobs. And even with backup scripts, it seems like developing a process to refresh a DR server would be overwhelming given the number of elements and exceptions. I'm not adverse to doing the work to create a completely scripted solution, but was curious if one already exists that handles all or most of the elements in my list.
We have 10 SSIS packages which are deployed on 2 servers. (at different geographical locations)
On both the servers we will install all the 10 packages, but on server1 we will enable 1 - 5 and server2 we will enable 6 to 10.
the idea is that if server1 goes down we enable all the 10 on server2 (and vise-versa).
Is this the best way to implement disaster recovery of SSIS packages? We want that in all cases of failure the SSIS packages are ready to process the ETL: tasks.
regards,
Abhishek.
I am learning SQL Server failover cluster installation. My question is regarding the options to install failover cluster instances on the Windows Failover Cluster. I have seen ead there are two options to do so.
From the installation center, installation option-> New SQL Server installation option and afterwards add node to SQL Server failover instance.From Installation Center, Advanced option-> Failover cluster preparation and then Failover cluster completion step.What is the difference between these two options and which option to use in which scenario?
We need to move a cluster to a new data center. There is a strong preference to retain the previous SQL Virtual Name. The existing cluster has a single default instance. We understand that when you have created a SQL Server FCI that you can change the SQL Virtual Name but not the instance name. So if the current SQL Virtual Name is SQL1 then at a high level:
Build cluster at new DCInstall SQL Server FCI with a virtual name as SQLTEMP and as a the default instanceShutdown old cluster ???Rename SQLTEMP to SQL1 Since there is an existing SQL Virtual Name my concern is that we cannot simply rename SQLTEMP to SQL1. Do we need to delete the VCO from AD for SQL1 in step
I have a service (Windows service that is) that's set to automatic startup. The service makes use of one or more SQL server databases and is configured with a startup dependency on SQL Server.
Problem is, the service control manager considers that startup dependency to be satisfied once the SQL server service process successfully starts, but connections from within my service to my databases won't actually succeed until recovery on at least those databases is complete.
Of course, I can do something mundane like retry failed connections a few times, or ensure that I don't attempt to connect to a database until some hard-wired amount of system uptime has passed, but those solutions are just band-aids. The Right Answer, it seems to me, would be to detect when SQL Server has finished recovery and not attempt to connect to my databases before that time.
So, is there any way to determine if SQL server has completed recovery programatically? On a server-wide basis or on an individual database basis? I'm assuming that my service would need to connect to SQL Server with some high-powered credentials (sa or equivalent) to get that information, but that's not a problem in my application.
Any pointers appreciated!
Hello,
Any body helop me in such respect that my log file was corrupt. I have mdf file only. Is there any tool to recover data from mdf file or export data to any other db.
thanks
Mansoor .net developer
I have a DB shown as recovery pending when running the following:
SELECT Name, state_desc
FROM sys.databases;
The DB was created by someone outside of our team using the Full Recovery model & I can see that no transaction log backups have been taken for this, causing the log to growth to a large size.
The MDF is only 5,120kb but the TRN has grown to 10,773,120kb
When I checked the Server I could see the data area had run out of space so I have freed up some space for this so now have 2.5gb available as a short term solution.
The MDF & LDF files are still visible & when checking the SQL log the DB is being reported as having a Full Transaction Log.
Essentially I want to change the Recovery Model from Full to Simple, Reduce the size of the transaction log & bring the DB back online. Luckily this DB is only used by a handful of users but I still need to get it up & running asap.
Folks:
Is it possible to setup a Disaster Recovery server for Reporting services (Databases: ReportServer and ReportServerTempDB)? If so, will loading these 2 databases (Log shipping) allow to me access the same reports as in Production server. Is there anything which I have to do on DR Server when the Production site is down?
Thanks!
does anyone know of any potential issues of using a 32 bit sql server 2005 instance used for disaster recovery of a 64 bit sql 2005 cluster ?
thanks
I have SQL Server 2000 Standard Version backups going to Disk then we have Legato Networker Software to make backups of these backup directories to TAPE.
When we retrieve the file from the TAPE and use to recover the database we get the following errors: (Notice some work and some do not. This is from command 'restore verifyonly from disk='
Server: Msg 3242, Level 16, State 2, Line 1
The file on device '\backupfilersqlbackupsqlserver
ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709210130.TRN' is not a valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
The backup set is valid.
The backup set is valid.
Server: Msg 3242, Level 16, State 1, Line 4
The file on device '\backupfilersqlbackupsqlserver
ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709180130.TRN' is not a valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 4
VERIFY DATABASE is terminating abnormally.
The backup set is valid.
The backup set is valid.
If I take the original backup file that are on disk they are all valid.
Thanks for any help that can lead me to the solution
Mike