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?
I have an old sharepoint db (SQL Server 2000 Standard edition). What I need to try to recover is the data in log backup files from the docs table. I need to see if there is a way to get any data in the log backups from columns dirname, leafname and content. The column content is an image type which old documents (word, excel...). I don't have a full log chain since the last full restore so any data I can get out of the logs is better than nothing. I really just need to get the image files stored in the column content and place them in a shared directory for the users and let them see what they can use.
hello, (this was originally posted in Administration Forum)
I am helping out with a disaster recovery of a database and i was given 4 files. 2 MDF and 2 LDF files. When I try to attach the files to SQL Server I recieve this err msg:
Server: Msg 823, Level 24, State 6, Line 1 I/O error (torn page) detected during read at offset 0000000000000000 in file 'E:chivirtualGPS0Dat.mdf'. Connection Broken
I then tried many other combinations hoping something would work --> tried individual files, just data files, tried to create a new DB and then switching/replacing out with the files i needed...etc, nothing seems to work for me. The people I am trying to help out do NOT have backups.
I have two databases on a Production Server that I want to Log Ship to a Test Server. According to the sys.master files the physical File Location is on an E drive. Early attempts at Log Shipping these two files error'd out due to space issues on the E drive (one Log Shipped and then one err'd out). I was subsequently informed from the server group that they would prefer that I Log Ship these two database files over to the M Drive where more space is available. In fact, they modified the Server Properties / Database Settings / Database Default Locations (for Data and Log) to the larger M drive (I'm not really sure why they just don't increase the E drive space but there is proabably a good reason).
Okay, so now my problems have been solved. Easy enough. Now I deleted the successful Log Shipped database and started from scratch. However, as before, one db restored and one failed (due to space issues). Apparently, both db are pointing towards the E drive. How is that possible?
So here I am with one successful database and the normal sys databases pointing to the E drive. What is the best way of approaching this move to the larger and preferred M drive?
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.
I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?
I need to move files for a lot of databases that are all part of an AG. I've used the method at the bottom of this link with success on a small test DB.
I'm trying to move a log file of a database that is part of an availability group. I have been following steps from the article: [URL]
At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.
I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.
Here are the steps I'm going through and what is happening at each step:
-------------------------------------- :Connect DEVSQL --This is currently PRIMARY USE[master] GO ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
[Code] ....
All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.
--Make SQL10 the PRIMARY :Connect SQL10 ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER; GO
SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.
:Connect DEVSQL --Enable XP_CMDSHELL sp_configure 'show advanced options',1 go reconfigure go sp_configure 'xp_cmdshell',1
[code].....
This is where the script is failing, returning the error:
Msg 1468, Level 16, State 5, Line 5
The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
I can not get the dB to recognize the log file at it's new location.
If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.
How do I force SQL to look for the log file again without restarting the service?
I'm working on a newly installed windows 2012 R2 server which has SQL Server 2012 and SSMS installed on it. What has been odd is that when I open the "restore database" dialog, in the "Select Backup Devices", "Locate Backup File" dialog, only the immediate folders off of the root of the E: drive are visible. E: is where sql server data and backup files are stored.
The account I am logged in with is supposed to have Enterprise Admin rights. How is can I not see these folders and files? The files are certainly there and are visible to me on that login with windows explorer. I am able to backup via SSMS without issue.
When I look at the folder's permissions everything seems normal. If I add "Everyone" to that folder's permissions the folders and files become visible. The SQL Server service is running on the NT ServiceMSSQLSERVER account, which is normal for my other installations of sql server.
I missed the ability to restore based on a time (10/23 6pm) due to our purge cycle in our production environment, but I was able to obtain the 10/18 full backup, the 10/23 differential backup, and the 4, 10/23 trans. log backups. I moved all the fore mentioned files to a staging environment, and now I am trying to restore all of the files to 10/23 6pm and I get :
"The log or differential backup cannot be restored because no files are ready to rollforward" error.
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
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.
in 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.
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
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
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?
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?
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?
Coming 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.
I'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.
Recently 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!
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.
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.
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 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?
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?
