SansSQL

Thursday, May 21, 2015

Restore database from corrupt SQL database backup file - Another Guest Post by Jyoti Prakash

Often people have corruption issues regarding Microsoft's SQL server database as well as its backup. Basically, backup is the only way to make your database secured and protective; it helps you to restore your inaccessible files of the main database when any type of corruption or damage occurs. But, what if backup also corrupt, while attempting to restore database from backup file. There could be multiple reasons behind such disaster situation, here in this write-up you will get to know about the reason and solutions behind such case, where SQL Server user faces corruption in SQL Server database backup and not able to restore their databases.

How and why the backup file gets corrupt:     

SQL backup files are basically a replica of your original SQL database, which can be located in different locations on the system. There could be multiple reasons of inaccessible backup file. Here are some most common causes of damaged SQL BAK files:

  • Virus attack
  • Abrupt system shutdown
  • Use of a wrong driver
  • Bad sectors in your system's hard disk
  • Sudden removal of a selected tables, records, and procedures
  • unconventional functioning of Hard disk
  • Improper shutdown of application
  • Wrong database synchronization
  • System crash
  • corrupt database system rules and tables
The most common error message during restoration of database is: 'Backup or restore operation terminating abnormally.'

A Backup restoration error occurs when a filemark in the backup device could not be read. There could be multiple causes of when a user encounters a filemark error. The most common reasons are:
  • A media failure may arise on the same device where the backup is stored
  • A write failure may occur while creating the backup file
  • Loss of connectivity may arise while creating a network backup
  • A failure in the Input/Output path occurs in the disk just after successful write to the disk

Manual Solution:
After backup restore error the first thing you could do is to check whether all the sets of backup have issues or just some sets have issues. It might be possible that only some sets of backup have issues due to which you are getting restore error. In order to retrieve other backup sets from the device, you need to specify the file number. In case, there are multiple backup sets available on a single device, then to determine the usable backup, you can run the following query:

RESTORE HEADERONLY FROM DISK='<Backup Location>'

If you got the usable set from the disk, copy it to another drive for usage and try to restore the damaged files with the help of SQL restore commands. Here are some of the SQL commands that you can use to restore corruption in your SQL database backup.                                 

To recover a database use the following command. This will put your database in the "restoring" state

RESTORE DATABASE <DB Name> FROM DISK='<Backup Location>' WITH FILE = <FileNumber>

Note: Write the backup set number instead of 'FileNumber' that you want to restore.

The following command will take the database, which is in 'restoring' state and make it available for end users.

RESTORE LOG <DB Name> FROM DISK = '<Backup Location>'
WITH RECOVERY

The above mentioned commands are used to restore corrupt backup file of SQL database. However, these corrupt backup recovery solutions provided by Microsoft are not applicable for deep corruption cases. In order to restore your highly damaged or corrupt SQL backup database you can always choose a third party SQL backup recovery software. These professional utilities are designed to restore data from a corrupt (.BAK) SQL backup file.  

Third party applications have functions to restore SQL backup file due to all above mentioned reasons. Before buying any professional backup recovery tool, you need to choose the most reliable one. For that you should use the online demo versions of the backup recovery applications to test their efficiency.

Jyoti is a Sr. DBA - SQL Server at Stellar Data Recovery and has written several article on SQL Server disaster recovery planning & fixing. In addition, she spend her time on Technical forums helping people with the issues related to SQL server.
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Wednesday, April 8, 2015

SSRS reports not visible on Google Chrome or Safari

Have you developed a new report in SSRS 2012?
Yes,
Is it working on IE?
Yes.
Okay, is it working on Chrome?
No.
Okay, is it working on Safari?
No.
What's the issue?
Is the new report developed with proper logic?
Yes, No, Maybe?

Don't worry, if the report is working on IE then it should show up on other browsers.
There is an issue with the SSRS 2012. Chrome and Safari render "overflow: auto" option in different way than Internet Explorer.

To Fix this issue,
  1. Open the server where SSRS is installed
  2. Navigate to the path <SSRS Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\
  3. Backup the file ReportingServices.js
  4. Edit the file ReportingServices.js and append the below code into it
    function pageLoad() {
      var element = document.getElementById("ctl31_ctl09");
      if (element) {
        element.style.overflow = "visible";
      }
    }
  5. Save the file
Now you should be able to view the reports on Chrome and Safari.
The div name is not always ctl31_ctl09. So, if you are still unable to view the reports on chrome and safari after the change, make sure to replace the highlighted div name in the code to the right div name of yours. This could be found by looking at the HTML source of report URL from your browser.

Saturday, March 7, 2015

Suggested ways to manage SQL server password - A Guest Post by Jyoti Prakash

Security management of SQL server is equally important and you must know that how to manage it. The most initial step to make your SQL server authentic is password security, which required management. Whether it is server or database, all the roles are simplified by security administration by allowing every individual user inherit permissions granted, denied, or abolished to those roles. Although there are multiple areas where user has to put the passwords, which require a proper handling.

SA account is a special login, which is provided for the complete backward compatibility. It is assigned to system admin to fix the roles, which cannot be changed. Therefore, SA account is the most often attacked account and deserves special attention. Here in this article you will get to know about the various rules to manage your SA password. Make sure your password must be random, complex, long and most important not used for multiple instances.
If you are looking for an efficient way to manage your passwords, it is advisable that follow the given techniques to keep track of all your passwords, in case of multiple servers you must use them. There could be multiple ways, here are some of them:

Store Password list
If your number of passwords count is manageable then you can just list them on a sheet and store that hard copy in a safe location. It is advisable that do not store the soft copy of this document as it could be a security risk. This is a very secured way to manage passwords, but could be risky if you have lost the hard copy.

Somewhere this method is not very convenient way to manage passwords as at the time of changing passwords, you have to immediately access this list. If in that case you are not in the situation to access this list. Then this list might become incomplete or become wrong over the time. Therefore, it is recommended that you should opt this method only if you have a reliable and diligent person to take care of this list.

Use Cloud storage
If your organization distributes teams with a rich number of users then, it would be difficult for you to manage all the passwords in the hard copy format. Therefore, to manage such large number of password list you can use several cloud based password management systems that let you to share passwords with the entire team. Many password management tools allow you to your passwords locally so that no one can access your passwords except authenticated users, ever if any other organization using the same password management service.

Local Password storage
Some people do not believe on cloud systems, if you are the one, then a local password store like KeePass, Password Safe, might make more sense to you. Such local password store facilitates multiple powerful features, which make them most trustable and secured ways to manage passwords.

With these tools the password store in encrypted format on a local system. To encrypt the stored password keys, tool uses powerful algorithms that make it infeasible for the attacker. These passwords are handled in such a manner that the system memory will also never store an unencrypted password.
However, these tools also have some shortcomings as well, but in terms of security they are much better.

Enterprise Password Managers
If you belongs to a really big enterprise and have number of servers to manage then enterprise password management tools might be the best thing you are looking for.

An Enterprise password manager not only used to store the passwords, however, they provide various other features as well. This tool can automatically manage access auditing and temporary access. This tool can also allow people to quickly access in case of emergency at the time of creating a 'loud' audit entry as well as sending relevant notifications to the authenticated people.
These tools can also schedule the time for regular password change. For example, you can use this feature to change your SA passwords on monthly or quarterly basis without any user interaction.

Do not use Password
In case you do not feel that there is a requirement of any password, so for this SQL Server also has a feature to disable its Authentication. If this is what you required then your all the authentication and password management system completely depends upon Active Directory. In this mode, you are allowed to control anyone's access up to a particular limit by just including the account into the appropriate AD group, this action can be easily analyzed. Additionally, You will also have a simple process to take all those accounts back out of those groups after a particular interval.

The best advantage of this approach is, that you do not have to manage multiple passwords therefore do not have any risk of security in terms of loss passwords. Here you can easily disable SQL Authentication, therefore, can say that it is the most secured way to handle sysadmin access to your SQL Server instances. Although it is not applicable in every case but this option is surely a considered to a good one.

Conclusion:
In the above mentioned information, you can see various ways to manage your SQL Server SA passwords. Each technique has its own pros and cons, so you have to choose the one according to your requirements. However, in case after following a proper way you have lost your password due to any reason then there are professionalSQL password recovery tools available that can access SQL master database file and allow you to reset your lost or forgotten SQL SA and other individual user passwords. You can choose a reputed and reliable product if you are facing any of such problems. 

About Jyoti Prakash
Jyoti is a Sr. DBA - SQL Server at Stellar Data Recovery and has written several article on SQL Server disaster recovery planning & fixing. In addition, she spend her time on Technical forums helping people with the issues related to SQL server.

Friday, December 26, 2014

The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

You might get the following error when trying to connect to an newly installed SQL Server 2014 instance.
The error says "The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement."


This is because the server on which SQL server is installed, is denying the client connections to SQL Server.
To fix this error, allow connections to the SQL Server Port in the firewall.

Saturday, November 15, 2014

Import data from SharePoint Online

It's all cloud could and cloud now-a-days. Everyone are moving towards the cloud for making life easier.
In this post I will be explaining on how to extract/Import data from SharePoint Lists which is hosted on SharePoint Online of Office 365 using SSIS.
There is no direct way to connect to the SharePoint Online using SSIS.
For connecting to SharePoint Online using SSIS, we need the below components installed before stating.
  1. Microsoft OData Source for Microsoft SQL Server 2012
    Download from http://www.microsoft.com/en-in/download/details.aspx?id=42280
  2. SharePoint Server 2013 Client Components SDK
    Download from http://www.microsoft.com/en-in/download/details.aspx?id=35585 
Microsoft OData Source for Microsoft SQL Server 2012 is the SSIS component which we use as source connection to the Sharepoint Online Lists and SharePoint Server 2013 Client Components SDK allows us to connect to Sharepoint Online.

Once you download and Install both the components, 
  1. Open the Visual Studio and create a new Integration Services Package
  2. Drag and Drop "Data Flow Task"
  3. In the "Data Flow Task" Drag and Drop "OData Source"
  4. Right-Click on "OData Source" and Click Edit
  5. Enter the "Connection Manager Name" and "Service Document Location" For Service Document Location, append "/_vti_bin/listdata.svc" to the SharePoint site
    For Office 365, we need to use "User this user name and password"
  6. Click "All" and change the "Microsoft Online Services Authentication" to True
  7. Click "Test Connection"
  8. Click "OK"
  9. Then Choose "Collection" in "User Collection or resource path"
  10. In the "Collection" choose the list you want to import
  11. When you click on "Preview..." you will be able to see the data from the list
  12. Now add the destination connection and run the package to import data from the SharePoint Online list