Quick Way to Rebuild SQL Server Master Database

All the system-level configuration settings and login account information of SQL server are stored in the corresponding SQL Master Database files. It contains information about other databases that are present in SQL server. Master database consist 2 files – master.mdf and mastlog.ldf. Also other information details which are included in master database are processes, locks, remote logins, etc. Since it contains records of all the files existing in database, SQL Server will not even start if the master database is – corrupt or unavailable.

Rebuild Master Database from Backup

You can rebuild SQL Master Database by using an existing backup.

To restore the backup in order to rebuild the Master Database follow these steps:

Note: First to proceed for restore task, you must have backup of the master database file on and Server instance should be running in single user mode.

• Right click on the database then goto Task then Restore ->Database MDF from the system files.
• Select Task option.
• Select the Backup option and click Restore.

Rebuild Corrupted Master Database

The process of rebuilding the master database is divided into 3 sections-Prerequisites, Rebuilding and Post-rebuild tasks.

PREREQUISITES

For rebuilding the master database in their current settings, following tasks should be performed before you start rebuilding.

1. First record the server configuration values

select * from sys.configurations ;

2. In order to reapply the updates after rebuilding master database record all the hotfixes and service packs applied to server.

3. Keep record the location of all log and data files stored in the database. This should be done because rebuilding process saves all the files in their original location. So in case you have moved your files to other location you can do the same after rebuilding if you have the record of these files’ location.

4. Locate the backup of the master database.

5. Make sure that you are permitted to rebuild the master database. For this you must be a member of sysadmin fixed server role.

6. Make sure that copies of log, master and model files exist on the local server. The templates files are used during the rebuilding process and should be available. Location of template files is:

C:\ProgramFiles\MicrosoftSQLServer\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates

 I.   REBUILDING PROCESS

The task should be performed in active mode and the SQL Server resource should be taken in offline mode before starting.

• You can insert and run the SQL Server 2014 installation media. Other method is to run the command prompt and change the location of the directory to the location of setp.exe file present on the local server. The default location of the file on server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

• In a command prompt window enter the given commands. The command prompt should be run as administrator. If you are using Windows operating system in which User Account Control is enabled, running setup may require more privileges.

Setup/QUIET/ACTION=REBUILDDATABASE/INSTANCENAME=InstanceName/SQLSYSADMINACCOUNTS=accounts[/SAPWD=StrongPassword] [/SQLCOLLATION=CollationName]\

NOTE: Ensure that the following points are kept strictly considered:

1. When specifying multiple accounts in /SQLSYSADMINACCOUNTS=accounts, separate the accounts with a blank space.
2. It is suggested to specify a strong password for sa account as it is vulnerable to get hacked.
3. If we change the server-level collation, it does not change the location of existing database. New databases will occupy new space by default.

A. After the setup process has finished rebuilding the database, it returns to the command prompt with no message. Verify the rebuilding process has been completed by examining the Summary.txt log file.

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Logs.

II  POST-REBUILD PROCEDURE

After the rebuilding is done you need to perform the following procedure.

1. Restore the recent full backup of the master database. If the backup is not available or the current backup is not updated, recreate the missing entries. The most suitable way to recreate missing entries is by running the scripts that created the entries.
2. Restore the distribution database if the instance of your SQL server is configured as a replication distributor.
3. Move the master database to the previously recorded location.
4. Verify whether the previously recorded values and server-wide configurations values match with each other.

NOTE: In case you have changed the server collation do not restore the master database as it may replace the new server collation with the original server collation.

Limitations of Rebuilding Master Database

There are certain limitations that apply when you want to know how to rebuild SQL master database. If a new location is provided to the rebuild statement, the master database is rebuilt at that location. Also any user defined changes made in the master database are lost during the rebuilding process. When the Master database is rebuilt, it is recreated and saved at its original location.

Read More

Tips for Securing SQL Server Database from All Sort of Risks

Securing SQL Server Database is a challenging task as there are number of external and internal risks or parameters affecting the Server and its databases. In this section, we have discussed about all the concepts and the precautions that can be taken to prevent databases and the accumulated components such as tables, stored procedures, triggers, etc. To protect the SQL DB, it is mandatory to provide security to the overall SQL infrastructure. Therefore, this approach is divided into given segments;

•  Platform Security
•  Application Security
•  Database Security

Let’s discuss about these parameters in detail, so as to get better understanding on all the perspectives that can help safeguard crucial information existing under SQL environment.

      Platform Security:

Platform security of the database refers to securing the hardware components from outside risks. It can be done by proposing access limitations for SQL Server to the few and authorizing users.  It is recommended to store the backup media at some offsite location with secure and minimal access. This can be done by configuring least users with access authorities and controlling unauthorized access on network devices and components.

The integral components such as Operating System and associated files can be protected by referring to the below mentioned measures;

Method 1:

Update Notifications: The internal security of the SQL Server Operating System can be ensured by installing genuine applications. After installing any new application or software in SQL Server, do always remember to update the associated notifications and security enhancements received from Microsoft.

Note: This can alternatively be done while installing SQL Server 2014 by checking the associated checkbox in the set-up window.

Method 2:

Keep Firewalls On: By keeping the Firewalls On, the security measures can be increased to ensure extended security. Firewalls are considered the security system that controls the network traffic by applying defined set of rules. It serves as the barrier between secure network (such as home network) and the external network such as internet (that might not be secure or fully trusted).

        Application Security:

Since SQL Server relies upon systems’ files in order to store data components so it is important to limit the access to these files for assuring more data security. This can be done by implementing the following given methods;

Method 1:

Access Authority: By assigning access restrictions to the distinct users existing on database, the files can be prevented from any unauthorized access. By doing so, the files can only be accessed by the users who are marked eligible to access them by the Database Administrator.

Method 2:

Authentication Check: Another powerful method to protect the integrity and security of data is via assigning strong passwords to the important files. This method can be implemented in a powerful manner when used with the combination of access authorities.

        Database Security

Now the most powerful aspects comes i.e. the SQL Database and the incorporated objects such as data stored in tables, stored procedures, indexes, triggers and all other components. Following concepts can be utilized for securing SQL Server and the data components from all sorts of internal and external factors.

Method 1:

Regular Backup: It is recommended and even serves as a best practice to backup SQL database and system log files on regular basis so as to avoid any data loss. Backup approach provides full security over data as if entire database or any of its components becomes inaccessible due to corruption or any other reason, the lost data can be gained back via backup restoration.

Note: Sometimes user forget to take regular backup or scheduled backup.In that case, if any database corruption occur or any accidental deletion of data occur, the chances of data loss becomes quite high. So in that case, you can use SQL Database Recovery Software for repairing and recovering of your SQL Server Database.

Method 2:

Data Encryption: The most powerful way to safeguard data and all encapsulated elements is via encrypting the files or the segments of data. However, this approach will not help in controlling the access over database but in case if the crucial information is leaked out via any unknown resources; it will not put any adverse effect on the data or the concerned person (to whom the particular information belongs to).

Conclusion:

Since the information existing into SQL databases is crucial as it includes data associated to many users or individuals. It is important to protect it from unauthorized access and illegal interruptions so as to avoid the occurrence of adverse circumstances. Using either or all of the above stated tips to secure SQL Server Database, the entire infrastructure, the application or the encapsulated data can be protected from distinct kinds of risks factors.

Read More

Tactics to Find Outdated Statistics in SQL Server

Statistics in SQL Server are used to identify the type of plan that needs to be executed for a query. SQL Server is pretty good in estimating the statistics. But, there are some circumstances in which it does not, in that case, a better understanding of statistics is required. Moreover, how SQL Server finds outdated statistics in SQL Server is also important to resolve the performance problem. If a user is not having good command on statistics, then it is difficult for a user to handle queries without having proper indexes. Thus, in this post, what exactly is SQL Server Statistics and how to find the outdated statistics in SQL Server is discussed to improve the SQL Server performance.

What is SQL Server Statistics?

In simple words, statistics in the SQL Server are the base on which the indexes are built. If there are no accurate statistics, then the indexes simply do not work and moreover, the good execution plans is also not created. It is generally used by the query optimizer to determine an accurate execution plan for a particular column.

The statistics, which are collected by the SQL Server are the number of different values that occur for a given column or a set of columns. In addition, after sampling the data in the table, SQL Server identify the statistics. But, it all depends on the size of the table whether it has to sample large or small percentage of the rows.

For example, assume that a user is having a column in a table in which all responses of MCQ question are stored. In it, one-fourth of the values are A, half are B, and the another one-fourth are C. however, it can also be possible that SQL Server will have a look on only one percent of the rows and comes up with three different values. It is because the server has correctly estimated the number of different values and give correct results. So, for a large amount of data low sampling rate is always a good idea.

Method to Check SQL Server's Statistics

As everyone is making SQL Server at different rates and perform the comparison of those samples, then it becomes possible to determine how the computed density changes with the sampling rate. In particular, one can easily compare the default sampling rate of the SQL Server with the actual statistics. After comparison, the result will determine whether the SQL Server handle the updating of statistics or if one needs to create his/ her own statistic update scripts. Now, to perform comparison process, follow the steps given below:

• First, you need to identify all the statistics for a particular database by executing sp_autostats command on each table
• After that, execute the UPDATE STATISTICS on all tables with the help of default sampling rate
• Now, to record the estimated density values, run the DBCC SHOW_STATISTICS for every statistic
• Then, execute the UPDATE STATISTICS command along with the FULLSCAN option on each table
• Now, to record the actual density values, use DBCC SHOW_STATISTICS
• Now, compare both the estimated values & the actual values. If the density varies by a large amount, then one cannot use the SQL Server default statistics routines

From the above procedure, it is pretty much clear that, if there is a non-avoidable difference in both actual & estimated density value then, SQL Server statistics are outdated and needs to be updated quickly.

The Bottom Line

Statistics in SQL Server are very important from the performance point of view. It has a direct impact on the performance of the SQL Server. If the statistics are outdated then, it will degrade the performance really fast such as bad execution plan. Therefore, a manual method to find outdated statistics in SQL Server is covered above. It is simple to execute and does not take much time. One simply use it and identify whether they are working on outdated statistics or not.

Read More

Know About SQL Server Error 6401

Introduction

The ACID acronym standing for Atomicity, Consistency, Isolation, and Durability are the properties of SQL transactions. Transactions are used when the database has to be modified by using one or more SQL statements and to make sure the security of the database as well. However, at times, this normal functioning of the transactions may pose unexpected errors that disrupt the performance of transactions.

This blog is the outcome of several queries that I’ve come across regarding SQL Error 6401. Therefore, here is a complete article dedicated to SQL Server Error number 6401.

SQL Server Error 6401

While running transactions in SQL server, the user gets an option to nest them. However, care should be taken that the number of COMMIT TRANSACTION and BEGIN TRANSACTION should be same. The number of BEGIN TRANSACTION commands can be known by running global variable @@TRANCOUNT. SQL Server Error 6401 basically occurs during the rollback transaction of nested transactions. Rollback transaction enables user to bring back the SQL database in its original state. However, this is not possible when the Nested transactions are concerned.

Multiple rollbacks in case of nested transaction are not possible and hence when run, gives the following error:

Msg 6401, Level 16, State 1, Line 33 Cannot roll back Inner Transaction. No transaction or savepoint of that name was found.

The error encountered clearly specifies that the user has tried to do multiple rollback in nested transaction.

In the example, shown below, the user has created nested transaction in which the outer transaction is being denoted by outerTran and the inner transactions are denoted by innerTran

In the highlighted section, you will be able to see that a ROLLBACK TRANSACTION on the innerTran has been carried out. When this query is run, the SQL Server Error 6401 is encountered by the user.

How To Remove SQL Server Error 6401?

There are three possible ways by which Error 6401 can be prevented from occurring. They are as follows:

• Using ROLLBACK TRANSACTION statement without specifying the name of the transaction.
• Replace BEGIN TRANSACTION statement with SAVE TRANSACTION statement.
• Using third-party SQL Server Log File viewer software.

Conclusion

SQL Server Error number 6401 is a result of ROLLBACK TRANSACTION done in a nested transaction. The best method to avoid the error has been specified in the above section. In case, the error has already been encountered, the complete transaction will have to be run again and care should be taken while doing this.

Read More