- Home
- About Us
- Recovery Services Individual RecoveryEnterprise RecoveryAdditional Recovery
- Software
- Testimonials
- Locations
MSSQL Server Repair (27)
For very large Microsoft SQL Server databases, a complete restore operation can take many hours. During this time the database cannot be used to prevent data being entered and lost as information is copied back from tape or disk. Obviously in a high-availability environment any downtime is costly, so keeping it to a minimum is essential.
Fortunately page level restore techniques can be used to keep recovery times to a minimum by reducing the amount of data that needs to copied back from the backup media. Since the release of Microsoft SQL Server 2005, DBAs have had the option of carrying out a ‘page level restore’ which allows them to recover a ‘handful’ of pages, rather than having to restore entire datasets and copy information back into the original database.
The page level restore operation is perfect for situations where data becomes corrupted during writes through a faulty disk controller, misconfigured antivirus software or an IO subsystem. Better still, restore level operations can be performed online for Enterprise editions of Microsoft SQL Server.
Prerequisites
As with any database recovery operation, page level restores are reliant on having a complete backup from which to work. If such a backup is not available, you will need to investigate an alternative method of recovering data from the server disks direct.
And although you can carry out the page level restore with the database online, you may decide to keep things safe by switching to single user mode whilst you transfer data using:
ALTER DATABASE <DBName> SET SINGLE_USER
WITH ROLLBACK AFTER 10 SECONDS
GO This command ensures that everyone is out of the system and cannot enter until you change the mode back again. You will also want to ensure that you have the end of the log file backed up so that you have all transactions fully accounted for and to prevent any further data loss:
BACKUP LOG DBName
TO DISK = N'X:\SQLBackups\DBName_TailEnd.trn'
GO Issue
You have a database in MS SQL that is tagged as (suspect) and you are unable to connect to the database.Possible Causes
- The database could have become corrupted.
- There is not enough space available for the SQL Server to recover the database during startup.
- The database cannot be opened due to inaccessible files or insufficient memory or disk space.
- The database files are being held by operating system, third party backup software etc.
- There was an unexpected SQL Server Shutdown, power failure or a hardware failure.
These steps require you to have Microsoft SQL Server Management Studio installed on your computer. If you do not have this installed please follow the steps outlined in the following article: How To Connect To Your MS SQL Database
Always back up the website before making any changes to the database . Shared hosting customers can do this through the Control Panel. Refer to Back Up Your Website Using Plesk. Dedicated server customers can back up the site either through the Control Panel, or through the Control Suite. Refer to How to Back Up a Domain Using Control Suite.
Introduction
In order to repair tables in a Microsoft SQL Server database, the following commands will need to be issued through the query editor in Microsoft SQL Server Management Studio Express.
Ensure that no Webtrends services are started while the database repair is underway.
Next Steps
1. Download and install MS SQL Server Management Studio Express for the version of the MS SQL database installed with the product.
Webtrends Analytics 8.5 and 8.5a use MS SQL Server Express 2005 by default:
Microsoft SQL Server Management Studio Express
Webtrends Analytics 9.2x and 8.7d use MS SQL Server Express 2008 by default:
Microsoft SQL Server 2008 Management Studio Express
2. After installation, open the application from Start > Programs > Microsoft SQL Server 2005/2008 > SQL Server Management Studio Express.
3. Log into the database using Windows Authentication
4. Expand Databases, then right-click on wt_sched and choose "Properties."
5. Select "Options" and scroll to the bottom in the pane on the right.
6. Change "Restrict Access" to "SINGLE_USER."
7. Select OK and repeat the above steps for the wtMaster section of the database.
8. Select "New Query" from the toolbar, and in the query pane that opens, paste in the following commands:
Today we will show how to repair corrupted MS SQL database.
In MS SQL Studio run new query:
1. dbcc checkdb(‘DB-NAME’) with no_infomsgs
DB-NAME is a name of your corrupted database. If this is completed without any errors then the database does not need to be repaired.
2. Alter database DB-NAME set SINGLE_USER
Before we begin repairing the database, the database must be set in single user mode.
3. dbcc checkdb(‘DB-NAME’,REPAIR_REBUILD)
There are number of repair model usually we use first REPAIR_REBUILD. When everything is ok go to step 5.e (multi user mode) If not, go to next step.
4. dbcc checkdb(‘DB-NAME’,REPAIR_ALLOW_DATA_LOSS)
Just a day before , come across new issue of database . for some reason database found in suspect mode ! and that was horrible moment.
After some search , we got brief idea that , database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.
To find out what exactly gone wrong with database we tried following query
DBCC CHECKDB (‘YOUR_DATABASE_NAME’) WITH NO_INFOMSGS, ALL_ERRORMSGS
(It wasn’t much helpful in my case . I am just posting here in-case it hepls other !)After a while we run another block of code , to put the database in emergency mode (Ref : MSDN )
EXEC sp_resetstatus ‘YOUR_DATABASE_NAME';
ALTER DATABASE YOUR_DATABASE_NAME SET EMERGENCY
DBCC checkdb(‘YOUR_DATABASE_NAME’)
ALTER DATABASE YOUR_DATABASE_NAME SET SINGLE_USER WITH ROLLBACK
IMMEDIATE
DBCC CheckDB (‘YOUR_DABASE_NAME’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE YOUR_DATABASE_NAME SET MULTI_USER
Background When you see the your database in Suspect mode that a code red situation. Its not something that you face everyday. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file). This post will show how to recover from suspect mod
Background
When you see the your database in Suspect mode that a code red situation. Its not something that you face everyday. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file). This post will show how to recover from suspect mode, but still go through SQL-server error logs and find out the root cause of the error.
Reason
At start-up, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process or if the file is missing, sql server start displaying error.
In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.
Recently one of my friend sent me email that he is having some problem with his very small database. We talked for few minutes and we agreed that to further investigation I will need access to the whole database. As the database was very big he dropped it in common location (you can use livemesh or dropbox or any other similar product) and I picked up from the location.
I was able to install the database successful. He informed me that he has created database diagram so I can easily understand his database tables. As soon as I tried to open the database diagram I faced following error. For a while I could not figure out how to resolve the error.
Error: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
In this post, I will teach you how to determine what user has administrative rights on the server as well as walk you Step-By-Step how to configure SQL Server to allow other users to login as Administrators to SQL Server. The generic message “Login Failed for User (Microsoft SQL Server, Error: 18456)” means you entered invalid credentials when logging into SQL Server. In the below screen shot, I am logging into Microsoft SQL Server Management Studio with a user that does not have administrative permissions to connect to the server.
You need to figure out what user does have rights to SQL server. Usually this is the user that you logged in with when you installed SQL Server or the user that is the default administrator on the computer. To get a list of users on the machine (Assuming Windows Server 2012 or above)
Step-By-Step
Microsoft SQL is the preferred choice when it comes to selecting a Relational Database Management System (RDBMS) for an organization. However, just like other RDBMS, it is plagued by several errors – corruption being a major one. SQL database corruption has many facets and there are quite a few places where the corruption can occur.
This article will be focusing on file header corruption in MS SQL database and also explain its solution. But first, let us start with the basics.
What is a database File Header Page?
The fundamental unit of data storage in SQL Server is called a Page.An SQL data file has the extension .mdf or .ndf and the disk space allocated to a data file is logically divided into pages numbered contiguously from 0 to n. Disk input – output (I/O) operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
The structure of an SQL Server data file is divided into the following pages:
- Page 0: Header
- Page 1: First PFS
- Page 2: First GAM
- Page 3: First SGAM
- Page 4: Unused
- Page 5: Unused
- Page 6: First DCM
- Page 7: First BCM
You’re still here? Why? There hasn’t been anything new to read here for weeks! Sorry, I’ve been busy, sitting in endless meetings, learning how to play “manager”. It hasn’t been pretty.
I’m back, with a new tale of good versus evil. For dramatic effect, I suggest that you read this one aloud, in Christian Bale’s “Batman” voice. Go ahead, try it!
This tale starts two days ago, with one of my Oracle DBA’s reporting a problem. “We’re missing records from a table, looks like replication from SQL Server is broken.” Yeah, sure, you Oracle guys are always blaming SQL Server when something goes wrong. Based on the evidence so far, he was, however, seemingly correct. Grabbing my cane, I struggled out of my easy chair and hobbled over to the computer console.
Copyright © 2024 DataRecoup Recovery Services. All Rights Reserved. Designed by DataRecoup Lab.
