Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Tuesday

Fix SQL error: 26 - A network-related error occurred in SQL Server

Introduction

This article explains how to configure an instance of the SQL Server Database Engine to listen on a particular fixed port by utilising the SQL Server Configuration Manager to fix SQL error 26. In my earlier article, I have thoroughly highlighted how to get database tables size in SQL Server. The default example of the SQL Server Database Engine responses (listens) especially on TCP port 1433. Named instances of the Database Engine and SQL Server Compact are arranged for dynamic ports. This implies they select an accessible port when the SQL Server administration is started over. When you are interacting with a named instance through a firewall, configure the Database Engine to listen on a particular port, so that the fitting port can be opened in the firewall.

Friday

Know About Transparent Data Encryption (TDE) in SQL Server

Introduction

The basic purpose of encryption feature ensures the confidentiality of any digital data stored on a system. Also, the data, which is transmitted through the internet or via network of another computer. Encryption brings data in such a state that it becomes very difficult to read or analyse it. It is not possible for a user to access the encrypted data without access to decryption key/password/certificates. In the following section we will learn what is transparent data encryption (TDE) in SQL Server, the method to enable TDE and also, its advantage and disadvantages.

What Is Transparent Data Encryption

SQL Server has various built-in technologies for data protection, and one of the most essential is Transparent Data Encryption. This feature is introduced in SQL Server 2008 and present in all the later versions for bulk encryption at the database file level, which includes logs, data and backup file. Moreover, to fulfil the demands of corporate data security standards, SQL Server provides the option to enable TDE on the database level or at column/cell level. This feature is completely transparent to your application. Users can even use file level encryption, which is provided by Windows for database files. In the following section we will discuss the method to enable Transparent Data Encryption along with the advantage and disadvantages of TDE.

How To Enable Transparent Data Encryption

These are the mentioned steps you need to perform to enable TDE on a database. You can follow these steps only if, you have the permission to create a database master key and certificates in the master database and also, control permissions on the user database.
  • Firstly, you need to create a master key: It is a very symmetrical to the key that is used to create certificates and also, asymmetric keys.
  • Then, obtain or create a certificate protected by the master key: Certificates can be used for the encryption of data directly or to create symmetric keys to encrypt the database.
  • Generate a key of database encryption for the protection by the certificate.
  • Next, set the database to use encryption: Encryption, for tempdb data, is automatically enabled once you enable TDE on any of the user database. This results in the prevention of temporary objects (used by the user database) from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE.

Transparent Data Encryption (TDE) in SQL Server

It’s very essential to take backup of the keys and certificates. This restores the encrypted database on another instance of SQL Server after restoring the keys or certificates there.
Whenever you try to use a certificate without taking a backup of it, SQL Server gives a warning like this:

The certificate used for encrypting the database encrption key has not been backed up

Pros And Cons Of Transparent Data Encryption

Pros
  • Quite simple to implement.
  • No modification is needed for the application tier.
  • Is invisible to the user.
  • Works with high availability features, such as mirroring, AlwaysOn and log shipping.
Cons
  • Overall database is encrypted not only the data, which is sensitive.
  • There is a small performance impact.
  • File Stream data is not encrypted.
  • Data, which is in motion or held within the application is not encrypted

Conclusion

After considering the need of encryption we have discussed the Transparent data encryption (TDE), which is a feature of SQL Server. We have gone through the process to enable TDE in SQL Server. We have also learned about the advantages and disadvantages of TDE. This article provides a deep understanding of Transparent data encryption in SQL.

Suggested Reading

Thursday

Fix Error 9002: Transaction Log Full For SQL Server

Overview of SQL Server Error 9002

Sometimes while working on SQL Server database we come across an error 9002. Which indicates that Transaction log for SQL Server is full. There may be several reasons through which the log file may become very large and run out of space or become full. Transaction log error 9002 generally occur when a log file is full or the disk space in which log file stored is full and cannot expand the log files further. In such circumstances, database remains online, but can only be read and no update operation can be performed. If this error occur while recovery then databases marked as resource pending.

Error 9002 msg:
The log file for database '%*Is' is full. Back up the transaction log for the database to free up some log space.
SQL Server database records all the transactions and the modifications done by each transaction. Transaction log must be regularly truncated to maintain the log space and keep it away from filling up. Some operations can be minimally logged to reduce their impact on transaction log size.
If the error 9002 occurred, when database was in recovery mode then after resolving problem ALTER DATABASE database_name SET ONLINE must be used.

How to Fix SQL Server Error 9002?

Following action can be performed for troubleshooting SQL Server transaction log full Error 9002:
  • Log backup can be done.
  • Log file can be moved to some other disk having sufficient space.
  • Log file size can be increased.
  • Freeing disk space so that the log file may grow automatically.
  • Long running transactions could be killed.
  • Adding a log file to other disk.
However, Transaction log is an essential part of database, which is required to return the database in consistent state if any system failure occurred. That’s why shrinking, deleting or moving transaction log be done after fully understanding the outcome of the action performed.
The above actions performed for troubleshooting t-sql error 9002 described below:

Transaction Log Backup

In case database uses the full or bulk-logged recovery model, and transaction log backup has not been done recently. Then there is need to take recent backup of the transaction log to free some space and supports restoring the database from a specific point. Log backup should be taken frequently to keep the log from filling up again.

Moving Log File to Another Disk

If creating enough space on the disk that containing the log file is not possible, then log file should be moved to some other disk having sufficient space. While moving log file one should never place the log file on a compressed file system. The log file can be moved by using concept of database detach and attach.
sp_detach_db executed to detach database.
sp_attach_db executed to attach database.

Increase Log File Size

As maximum size for log file is two terabytes(TB). So if space available on the disk log file size can be increased. Size can be increased manually to produce a single grow increment in case autogrow disable and a database is online. Also, we can enable autogrow by using ALTER DATABASE statement.

Freeing Disk Space

The disk containing the transaction log file can be freed by deleting or moving some files from that disk to another disk. Freeing the disk space will enlarge the log file automatically on that disc.

Add Log file to Another Disk

A new log file can be added to the database on the different disk by using ALTER DATABASE ADD LOG FILE.

Conclusion

We get to know about SQL Server Transaction Log Error 9002. Error 9002 in SQL Server occurred when the transaction log file is full. While moving or deleting a transaction log file the outcome of the action performed should be kept in mind. Several actions which can be performed to solve this error are discussed briefly.

Suggested Reading

Monday

Fix SQL error: 26 - A network-related error occurred in SQL Server

Introduction

This article explains how to configure an instance of the SQL Server Database Engine to listen on a particular fixed port by utilizing the SQL Server Configuration Manager. The default example of the SQL Server Database Engine listens on TCP port 1433. Named instances of the Database Engine and SQL Server Compact are arranged for dynamic ports. This implies they select an accessible port when the SQL Server administration is begun. When you are interfacing with a named instance through a firewall, setup the Database Engine to listen on a particular port, so that the fitting port can be opened in the firewall. Here are given 5 different approaches to get resolution of 26 - A network-related error occurred in SQL Server. In my earlier, I have already explained how to find and delete duplicate values in SQL server.

Error Description:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Approach 1: We can run this query to view your TCP endpoints 


SELECT name, protocol_desc, type_desc, state_desc, is_admin_endpoint, port, is_dynamic_port, ip_address FROM sys.tcp_endpoints

tcp endpoints

Approach 2: Here you can check which SQL port is running on your machine.


USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

Approach 3: Run below command to check applications listening on ports, try this command on command line:


netstat -ap TCP

Approach 4:


i) Open Immediate Window (Windows + R) on your machine.
ii) Next type %windir%\System32\cliconfg.exe and click on Enter button.
iii) Now a new window will open, left side will show disabled port and right side will show enabled port.
disable tcp ip protocols

iv) Now Select TCP/IP disabled protocols and click on Enable button to add in enabled protocols list.

tcp ip properties

v) Now you will enabled TCP/IP protocols and click on properties, then finally you will see SQL Server port i.e. 1433



sql port 1433

Approach 5: 

  • Start from open your SQL Server Configuration Manager
  • Next click  on "SQL Server Network Configuration" and click on "Protocols for Name".
  • Now right click on TCP/IP, click on properties and make sure it is enabled.
  • Presently Select "IP Addresses" Tab - and-Go to the last passage "IP All".
  • Now enter SQL server port TCP/IP 1433
  • Remove 0 (keep totally blank) from TCP Dynamic Ports property
  • Now restart your SQL Server (Right click on SQL server Management Studio and click restart button).

Conclusion

Here are given 5 different approaches to resolve issue 26 - A network-related error occurred in SQL Server. Hopefully, these approaches would work to all who are looking to resolve issue 26 - A network-related error occurred in SQL Server.

Relevant Reading

SQL Server Schema Corruption Error 211: A Technical Solution

Introduction

The SQL Schema is a logical collection of SQL Objects including the tables, associated with columns, its relevant entries, or other elements. While creating the schema, a user will be able to access the database in more effective manner. However, a situation occurs in SQL server that different errors occurs while accessing it. One such error is SQL server error 211, which occurs when the server table is getting updated. In the following section, we will cover reasons for corruption in the server Schema and then possible solutions to overcome this error.

Problem Statement

Suppose a user tries to update some database tables on his server and meanwhile a schema corruption error message occurs on the system. This error can exist in any version of SQL Server such as Server 2005, 2008 etc.
“Msg: 211, Level 23, State 51, Line 1 Possible schema corruption.
Run DBCC CHECKCATALOG
Msg 0, Level 20, State 0, Line 0”

Reasons Behind SQL Server Error 211

Generally, schema corruption in database evolve very rarely in the SQL Server. When this error took place, a user have to check out the main cause of the error i.e., either via SQL error Log files or some other.
There are multiple reasons where schema gets corrupted and some of them are mentioned below:
  • Due to improper computer activities by server users
  • Sudden software failure leads to Schema corruption
  • Hardware failure is the major issue behind corruption

How to Fix Corruption Error 211 in SQL Server

There are several methods to resolve this server error. A user can choose any one approach to remove or delete the schema corruption which is present within the server. Following are the all possible solutions to fix the error:
  • One of the compatible solution for resolving this error is to restore the backup files of the database. In addition, go through following scenarios that will help you in restoring backup files:
    1. If a user has backup of transaction log, then take a backup of tail-log and restore the latest backup completely with entire transaction logs that are ending with tail-log backup. In such case, the result will become in 0 data loss.
    2. If a user does not have a backup of the transaction log then, develop a new database and import more data as possible or if a user is able to determine culprit table, then drop that table & recreate it.
  • The another approach is to repair entire MDF database file and then, you will have to deploy the database again.
  • The last manual trick is to execute DBCC CHECKCATALOG command on the server screen.
When you will run DBCC command the error will be displayed. Sometimes, much exceptions might occur while executing the Transact-SQL statement through SQL users with dedicated Schema. Make sure, a user will get the suitable suggestion from SQL server database by running DBCC CHECKCATALOG syntax. With the help of this command, users will get to know the location and reason behind the SQL server error 211. However, DBCC CHECKCATALOG signifies no error, but it is not possible at all time.

Alternate Solution to Resolve SQL Server Database Corruption

The most appropriate solution for fixing SQL error code 211 is third party SQL recovery tool. It is safe and secure way to troubleshoot this error with help of following steps:
Step1: Launch SQL Recovery, Add MDF file and press Open button

Step 2: Analyze all your data of the server file and then click on Export button

Step 3: Now, select an option between two i.e., with the only Schema or with Schema & Data for exporting MDF file and click on save to start migration process

Finally, you will be able to get a healthy MDF file with help of this solution and hence, fix SQL server error 211.

Conclusion

Here we are ending with one of the server error, which create hurdle while accessing the SQL Server database. The best possible solutions are also discussed to overcome this possible Corruption Schema error. Well, it is recommended to first restore the backup file of server, if a user is having it. Else, you can go for other solution for resolving SQL server schema corruption error 211.

Suggested Reading

Wednesday

How to find and delete duplicate values in sql server

Introduction

Today, I am writing about how to Find and delete duplicate values in SQL server and delete in our SQL Server database, sometimes data is inconsistent due to not proper relational integrity it results to duplicate rows in corresponding tables. Duplicate records of any database will lead to unstable application and it will not generate information according to end user expectation. I have clarified extremely known SQL Server error SQL Server Error 25 And 87.
SQL duplicate records

Monday

Software Review - Stellar Phoenix SQL Database Repair

While the database is in Suspect mode, no transactions are possible till it gets repaired. There can be a number of reasons due to which SQL database is marked as SUSPECT. These reasons include faulty hardware components, inappropriate shutdown, missing transaction log, malware attack, corruption in transaction log file, application crash and much more.

I came through the same situation where SQL Database turned to the SUSPECT mode. Analyzing the error log helped me to detect the actual reason behind the cause. This sometimes helps to bring the database to the online mode efficiently. To resolve this issue, initially, it requires changing the status of the database to EMERGENCY mode to gain read-only access as a DBA.

After doing this, I tried executing DBCC CHECKDB for executing logical and physical integrity checks. Further, repair_rebuild and repair_allow_data_loss were executed for SQL repair operation. However, during the initial attempt to gain access to the database tables, I realized that some content is missing.

Probability: Executing DBCC_CHECKDB with repair_allow_data_loss might have resulted in loss of data components and, sometime DECC CHECKDB with REPAIR_ALLOW_DATA_LOSS is not working.

Read here the complete article on this problem: http://www.stellarinfo.com/blog/dbcc-chekcdb-repair-allow-data-loss-not-working/

Since no recent backup is available, all deleted records need to be recovered. Then I decided to deploy Stellar Phoenix SQL Database Repair tool with the hope to get data recovered. The tool is known to integrate the following features:

     1. Repairs damaged MDF and NDF database files
     2. Recovers deleted records
     3. Recovers SQL tables, queries, rules, indexes, keys, defaults and triggers
     4. Recovers selective data from the selected database file
     5. Saves recovered files as HTML, MS SQL, CSV and XLS file formats
     6. Supports SQL Server 2016\ 2014\ 2012\ 2008 and lower versions

What does SQL Database Repair Tool Offer?

Since database encounters data loss after being recovered from the SUSPECT mode, the MS SQL database repair software was initiated for repairing the corrupt data. The performance of the tool is quite surprising as it recovered maximum contents from the inaccessible database files. Initially, the recovered contents get displayed within the tool panel.

Listed below is the complete functionality of the tool:


     1. Download, install and launch Stellar Phoenix SQL Database Repair. The tool will instruct to stop the SQL Server and copy the corrupted database to another location. This is quite practical because database connected to MS SQL Server cannot be modified as it is under the control of the Server.

Select Corrupt MDF file


     2. Click on Select Database button to upload the damaged database file: During MS SQL 2016 repair procedure, the tool provides the option to select the drive on system with the file type (i.e. MDF or NDF) and search database in that particular location. Alternatively, click on Find button if the location of the file is unknown.

     3. Select the checkbox that says “Include Deleted Records” to recover deleted data from database

     4. Click on Repair button to start the repairing procedure

     5. Select a particular component or folder from the left pane of the tool to preview the integrated items.Once the corrupt database file is selected, the tool enlists all the items saved into that particular file in the right panel of the tool
Preview of repaired data

     6. A message will be displayed that indicates “Selected MS SQL database repaired successfully”. Click on OK to save the repaired data
     7. The tool provides different options to save the repaired data that includes MS SQL, CSV, HTML and XLS
Save Repaired SQL Database

Note: The tool also provides the option to save repaired database file at a selected location on the machine. You can select destination path by clicking on the Browse button. By selecting the given checkbox, you can instruct the tool to automatically open the destination folder once the repaired file is saved.

     8. Then, click on the OK button
     9. Next, the tool offers to save the repaired data either in the New Database or Live Database

Note: You also need to provide Server credentials while saving repaired data to the SQL Server. Next, the tool will prompt you to enter the Server instance name, Authentication, and the name of Existing database. 

     10. The tool will display a message “File saved at the desired path.” Click on OK to complete the procedure
SQL Repair Versions and Purchase Information

SQL Database Repair tool offers two different versions that include: 

     - Trial edition
     - Licensed edition

The trial version of the software offers restricted access. With this version, you can test the functionality up to Step 6 mentioned in the above procedure. This version allows accessing the preview of the repaired data within the tool panel. However, the repaired data can be saved after purchasing the licensed version of the tool. 

To buy the licensed version of the tool, click on the Purchase icon available in the Help menu on the Menu Bar. Next, select the appropriate option for purchasing the software and select a payment mode. Once the payment is processed, the Serial Key will be sent to the registered email id. You can then activate the tool by using this Serial Key. 

Final Verdict
As per my experience, the functionality of Stellar Phoenix SQL Database Repair tool is accurate and efficient when tested on MS SQL 2016 database. During MS SQL 2016 repair process, the tool repaired entire data components that have become inaccessible after the database is brought online from Suspect mode. The integral contents and attributes of the tables, triggers, rules, indexes and other components remain intact, and the integrity of the database is not compromised. 

SQL Backup Repair Tool to Recover Corrupt BAK File (Review)

SQL Server is a relational database management system designed with the aim to store and retrieve data. Users can back up the SQL database in a backup file named as BAK file. This file enables the users to recover the entire SQL database in case of any damage to the database. However, the real problem arises when the BAK file gets corrupted or damaged due to certain issues. Thus, in this case it becomes necessary to recover corrupted BAK file. Now the question comes how to recover corrupt BAK file? One such solution to recover the BAK file is by using the third party utility, i.e. SQL Backup Repair Tool. This review discusses about the same based on the performed software testing.

Introduction

SQL backup repair tool is designed in such a way that it helps to recover and export the .bak file into SQL server. The application maintains the integrity of data after recovery. It recovers unlimited amount of data within few moments. It can be accessible on all Windows Operating system.

Quick View

Functions Recovers the SQL Backup File
Current version 5.2
Size 6.3 MB
Interface Graphical User Interface
Windows version supported 8.1 and below editions
Price $149

Different Editions of SQL Backup Repair Tool

The tool is available in two versions, i.e. Demo and Licensed. Users can choose any version accordingly.

  • Demo Edition
Before going for the license version, it is advised to check the working of the tool with the help of the trial edition. The trial version is available free of cost and can be downloaded from the official website. However, there are some limitations, i.e. it scans, and shows preview the entire recovered .bak file but cannot store and export them.





  • Licensed Edition

  • Once the user is satisfied with the working of the demo version, the license version of the tool can be purchased from the official website. It permits users to retrieve, store, and export them into Database of SQL Server.

    Essential Features

    • Recover Complete Backup File
    • SQL backup repair tool enables the user to recover the entire data stored in the backup file like rules, triggers, tables, stored procedures, etc. The users can view the entire data before exporting the data from the file.
      Recover Complete Backup File
    • Export Data With Schema
    • The application offers two options to save the recovered data from the SQL BAK file. The user can save only Schema or can save both Schema and Data. Both the options ensure maintaining the original structure intact.
      Export Data With Schema
    • Dual Export Options
    • The users can export the recovered data directly in SQL server database or can store it in SQL server compatible scripts. The SQL script file can be later restored in SQL server accordingly.
      Dual Export Options
    • Selective Data Export
    • SQL backup repair tool allows the users to recover corrupt SQL BAK file and export selective data from SQL BAK file. The user can selectively export only the necessary data items from the SQL backup file.
      Selective Data Export
    • Supports Both Keys
    • The software provides extended support for all the elements of the database along with primary and foreign keys. The application is programmed in such a way that supports both the keys while retrieving the database.

    • Preview Data Component
    • Once the corrupt SQL backup file is recovered by the application, it allows the users to preview the data. The preview of complete data contains the tables, rules, triggers, functions, columns, Indexes, views, etc.
      Preview Data Component

    Pros:
    • Supports all editions of Windows
    • No file size limitation

    Cons:
    • Fails to back up the deleted files
    • Does not supports Mac OS.

    Conclusion

    Considering the working performance of the SQL backup repair tool, it can be rated as 9.5/10. Although, the application fails to recover the deleted files and does not support Mac operating system. However, it efficiently recovers the highly inaccessible files. Moreover, its in-built and user-friendly interface is very helpful for users to repair corrupt SQL backup file. It supports dual mode to export the recovered data.

    Suggested Reading

    Saturday

    SQL Table Size: How to Get Database tables size in sql server

    Introduction

    In Last article, I have explained very known SQL Server Error 25 And 87, here going to fully explain how to get database tables size in SQL server. Tables stores information in rows and columns in database. I am here explaining how to check tables storage in SQL database. This is very important to check database tables storage to optimise our application performance. Explaining all steps to check our database tables storage in single command. Here, you can see how to get all database tables size with detail.

    Solution I (Shows usage one by one table)

    1. Open your SQL Server Management Studio and login into your database.
    2. Now navigate to Query Analyser (Ctrl + N).
    3. Then now write given T-SQL command select 'exec sp_spaceused ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' in query analyser.

    database table list

    4. Now copy all records from result window.
    5. Again now open a New Query (Ctrl + N) Analyser and paste all records copied from result window.
    6. Next select option from top toolbar Results to File (Ctrl + Shift + F).


    7. Now hit button Execute (F5) from top toolbar to run T-SQL command to store all database tables storage stats into a report file (.rpt).
    8. Now open file saved into your machine hard drive to check the database tables storage stats. This file contains given columns  name (name of table), rows (total rows in table), reserved (reserved space), data (stored data size), index_size (total indexed size), unused (shows unused data).

    tables space used file

    Solution II (Consolidated Database Usage)

    1. Open your SQL Server Management Studio and Right click on Database.
    2. Now navigate to Reports >> Standard Reports >> Disk Usage.

    Disk Usage Report
    3. You will see here showing consolidated data usage detail Total Space Usage, Data Files Space Usage, Transaction Log Space Usage with graphical representation.

    Total Space Usage

    Conclusion

    Database is soul of our application so it requires well-tuned, managed and optimised to run our application superbly. Here, I have managed all steps to get database tables size in SQL server so that we can manage accordingly.

    Relevant Reading

    Monday

    Fix SQL Error : SQL Server Error 25 And 87

    Introduction

    Error SQL Server Error 25 And 87 is very known error while connecting through MS SQL Management Studio. SQL Server Error 25 And 87 comes out commonly when we type wrong host name instead of correct host name (server name). I have already provided resolution in my earlier article SQL error of Could not open a connection to SQL Server and Login failed for user iis apppool default apppool. I have provided main key factors how we can resolve this issue through given below steps.

    Error Description

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87).

    Step 1: Login Authentication Windows or SQL Server

    Check your login Authentication whether its Windows or SQL Server.

    Step 2: Use (.) (dot) instead of complete server name

    If you are using local MS sql database then you can use (.) (dot) instead of complete server name.

    Step 3: Enter Correct Host Name

    Enter your correct host name: (hostname\SQLEXPRESS) my_hostname\SQLEXPRESS (in case of SQL Server Express) or (my_hostname\mssqlserver).

    Step 4: Try localhost slash (\) then host name

    You can also try localhost slash (\) then host name, here is sample as (localhost\SQLEXPRESS) or (localhost\mssqlserver).

    Authentication Windows or SQL Server
    Fig 1: Authentication Windows or SQL-Server
    SQL Authentication using dot
    Fig 2: SQL-Authentication using dot
    Authentication Windows or SQL Server
    Fig 3: Use localhost in server-name

    Conclusion

    Explained all primitive steps to resolve very known SQL Server Error 25 And 87. You may go one by one steps provided to resolve this issue so that take over from this SQL error.

    Relevant Reading

    Saturday

    SQL Server: Top 10 Queries of SQL Server

    Introduction

    T-SQL most top queries demonstrating today in this article. T-SQL is most important while dealing with database. MS SQL Server has provided  numerous T-SQL queries to check database performance, data manipulation queries, data definitions queries, roles/security check, transaction level etc.

    Database Table with Data

    CREATE TABLE [dbo].[tblPersonalDetail](
     [TablePK] [int] IDENTITY(1,1) NOT NULL,
     [First_Name] [nvarchar](100) NULL,
     [Last_Name] [nvarchar](100) NULL,
     [EmailID] [nchar](100) NULL,
     [Address] [nvarchar](200) NULL,
     [CellNo] [int] NULL,
     CONSTRAINT [PK_tblPersonalDetail] PRIMARY KEY CLUSTERED 
    (
     [TablePK] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[tblPersonalDetail] ON
    INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],[Address], [CellNo]) VALUES (1, N'David', N'Moore', N'david.moore@you.com', N'new lane drive USA', 456789456)
    INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],[Address], [CellNo]) VALUES (2, N'Marry', N'Champ', N'marrychamp@you.com', N'straight drive, Lane I', 15478945)
    INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID],[Address], [CellNo]) VALUES (3, N'Allan', N'Lamb', N'allan@you.com', N'Main Lane, London', 12312313)
    INSERT [dbo].[tblPersonalDetail] ([TablePK], [First_Name], [Last_Name], [EmailID], [Address], [CellNo]) VALUES (4, N'Arya', N'Bhatt', N'arya@abc.com', N'Nalanda University', 12313154)
    SET IDENTITY_INSERT [dbo].[tblPersonalDetail] OFF
    

    Table Data View

    table data view

    SQL Next Record: How to get next record in SQL Server Table

    SELECT TOP 1 LEAD(PD.TablePK) OVER (ORDER BY PD.TablePK) NextValue from 
     [tblPersonalDetail] PD where PD.TablePK >=1 order by PD.TablePK asc;
    SQL NEXT RECORD

    SQL Previous Record: Go to Previous Record in Table using SQL Server

    SELECT TOP 1 MAX(TablePK)Previous_Record FROM tblPersonalDetail WHERE TablePK < 3;
    SQL Previous Record

    SQL Server: How to check Total Rows, Spaced Used, Indexed and unused Size?

    EXEC sp_spaceused @updateusage = N'TRUE';
    
    GO
    EXEC sp_spaceused N'dbo.tblPersonalDetail';
    check sql space used

    Highest Salary: How to get 3rd Highest Value using SQL Server

    SELECT TOP 1 TablePK from (SELECT DISTINCT TOP 3 TablePK FROM tblPersonalDetail 
     order by TablePK desc) a order by TablePK

    SQL Defined Tables: List all user defined tables using SQL Server


    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
    INFORMATION SCHEMA TABLES

    SQL Columns: List all columns in our database using INFORMATION_SCHEMA

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    INFORMATION SCHEMA COLUMNS

    Other INFORMATION_SCHEMA.ViewName: Here are listing of other Information_Schema metadata which allows you to retrieve rows of specific database from Master database.

    SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
    SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
    SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
    SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
    SELECT * FROM INFORMATION_SCHEMA.DOMAINS
    SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
    SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
    SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
    SELECT * FROM INFORMATION_SCHEMA.TABLES
    SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
    SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    

    SQL Date Formats: To see the date formats for your culture using MS SQL Server

    EXEC sp_helplanguage
    

    EXEC sp_helplanguage

    Fig: Date Formats

    XML Showplan: This statement returns showplan in XML format by the plan handle.

    USE master;
    GO
    exec sp_who;
    GO
    SELECT * FROM sys.dm_exec_requests
    
    XML Showplan

                                         Fig: XML Showplan

    Select Last SQL Records: In this query how we can select last records using Top Statement in T-SQL

    SELECT Top 4 * FROM dbo.tblPersonalDetail Order By TablePK desc
    
    Select Top Query

    Conclusion

    In this article, I have demonstrated T-SQL top 10 queries in SQL Server. Which shows data from Master database of the SQL Server and few queries manipulate of specific database. Whenever we are working on top db administrator then we need to fire administrator level queries to tune up our database.

    Relevant Reading

    Monday

    Database Repair Tool: How to Repair MS SQL Database Using Stellar Phoenix SQL Database Repair Tool

    Introduction

    Stellar Phoenix SQL Database repair tool is able to repair corrupt .mdf and .ndf files. Few important features of this tool are:

    • The tool supports MS SQL Server 2016, 2014, 2012, 2008 and all the lower versions. 
    • It has the ability of self-reconnect after interruption on the repairing process.
    • Restores the compressed (Row compressed, page compressed) data.
    • Recovery of clustered and non-clustered indexes. Etc

    Step 1: Download and install from here Stellar Phoenix SQL Database Repair 

    The main interface of the tool looks like this. Select the corrupt database (.mdf) file.

    Stellar Phoenix SQL Database Repair

    User can also use Search the .mdf file in the specific drive by Search in Folder option. To include the deleted records, click on the check box.

    Include Deleted Records

    Step 2: Open the corrupt .mdf to start Repair Process.

    Repair Process
    Step 3: Click on the Repair button to start the repair process as shown in the figure below:

    start repair
    Step 4: After completion of the repair process, User become able to see the preview of the data.

    preview of the data

    Step 5: User have option to save the log report in .XLS, .HTML and CSV formats. Click on the Save Log option which is present in the top ribbon then save the log file.

    Save Log

    Step 6: To register the tool, click on the Registration button then select the appropriate option.

    Registration

    Conclusion

    Steller Phoenix SQL Database Repair helps to repair all SQL Server database (SQL 2008, 2012, 2014, 2016 & lower  versionbs) with great perfection. This tools is enabled with great capability to recover corrupted SQL database of all versions up to date.

    Relevant Reading

    Tuesday

    SQL Server Installation Fix: Repair a Failed SQL Server 2016 Installation

    Introduction

    SQL Server 2016 is a recently released version of Microsoft’s popular database platform that is developed with inbuilt advanced analytic and higher security technology. It targets all the critical applications and deeper insights in your data across on-premises and cloud. Here are few tips to handle the installation process to avoid the failure along with the method to repair the failed SQL Server 2016 Installation.

    Scenarios when SQL server 2016 installation failed and need repair operation

    • When an instance of SQL Server got corrupted after successful installation and you need to repair that particular instance only.
    • When you need to repair a particular instance of SQL Server in case the upgrade operation is cancelled or failed after the instance name is mapped to the newly-upgraded instance.

    If the following message appears in summary log, you need to repair the failed upgrade instance:

    Error message: "SQL Server upgrade failed. To continue, investigate the reason for the failure, correct the problem, and then repair your installation."

    In case the following message appears in the summary log, you need to uninstall and reinstall SQL Server, as you will not be able to repair the SQL Server instance.

    Error Message:"SQL Server upgrade failed. To continue, investigate the reason for the failure, correct the problem."
    When you repair an instance of SQL Server all the missing or corrupt files, registry keys are replaced with the new ones and all missing or invalid configuration values are set to their default values.
    Note: Before initiating SQL Server failover clusters, remember that the repair process must be run on individual cluster nodes only. To repair a failover cluster node after a failed prepare operation, use Remove node and then continue performing the Prepare step again.
    Repair steps when SQL Server installation get failed from the Installation Center
    1. Run the SQL Server Setup application (setup.exe) from SQL Server installation media.
    2. When you are done with prerequisites and system verification, you will be prompted to the Setup program that displays the SQL Server Installation Center page.
    3. Now, select Maintenance located in the left-hand navigation area, and click Repair to initiate the repair operation.
    4. The next window runs all the setup support rule and file routines to get assurance about all the prerequisites installed and then Setup validation rules. Now, select OK button or Install to continue.
    5. Now, on the Select Instance page, choose the instance you want to repair, and click ‘Next’ button.
    6. To validate the operation repair rules will run, click ‘Next’ to continue.
    7. The next page will be ‘Ready to Repair page’, which indicates that the operation is ready to proceed, click Repair.
    8. You will be able to view the status of the repair operation through Repair Progress page.

    SQL Server Installation Fix
    Steps to repair failed installation of SQL Server using Command Prompt

    You can also run the given command at a command prompt to repair failed installation:
    ‘Setup.exe /q /ACTION=Repair /INSTANCENAME=instancename’
    

    Summary

    These are the manual steps you can use to repair a failed SQL server 2016 installation. But in case you are unable to repair SQL instances then you can always go with the alternate solutions available in the market. There are various SQL repair tools available that offer you advanced modules to repair SQL 2016 instances without following many steps.

    Relevant Reading

    Author Bio: Priyanka Chouhan is a technical writer in Stellar Data Recovery with 5 years of experience and has written several articles on SQL server & SharePoint. In the spare time, she loves reading and gardening.

    SQL Error: Saving Changes not permitted in SQL Server

    Introduction

    Today, I am explaining about error saving Changes is not permitted in MS SQL Server, to authorize this permission you have to un-check check box under Tools >> Options menus. If we keep it checked then it will not allow us to change data type during the development so that we require to frequent change or specific requirement then we required it immediately.

    Error Description

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    Saving Changes not permitted in SQL Server

    Saturday

    The underlying provider failed on open

    Introduction

    I am now providing resolution of The underlying provider failed on open problem whenever we are working on across the network. If you see inner exception message then you'll caught in below error description in detail. This problem comes often when we are working with Entity Framework with MVC, ASP.Net, Windows Forms, WPF etc. This issue can be encountered in noticed scenarios. I've also written in detail associated with issue  Could not open a connection to SQL Server.

    Error Description

    {"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. 
    Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 
    (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}

    Tuesday

    How to Delete Contraints in SQL Server

    Introduction

    Suppose we are working with Entity Framework or some very complex database and moreover we don’t have matured database then very tedious work to carry out changes with our back end database in MS SQL Server, so I am proposing this great utility to handle out this problem.

    Saturday

    Could not open a connection to SQL Server

    Introduction

    One day, I was attempting to connect with MS SQL Server but it didn't succeeded not even after making attempt once more and once more. I ran into error provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server on server. I'm providing you all necessary steps to resolve issue error: 40 - Could not open a connection to SQL Server. To succeed this issue, I would recommend to experience gave all strides one by one until your issue get resolve. This error comes out in many ways so that you need to check out all steps provided in this article until you succeed your issue.  

    Detail Error Description:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1016


    Sunday

    How to Design Entity Relationship Diagram

    Entity Relation Diagram (ER-Diagram) is very important aspect of database designing, with the help of ER Diagram we can define Primary Keys, Foreign Keys, Unique Keys so entire relations in our schema. I have suggested key steps to design fantastic ER-Diagram.

    How to Schedule Backup in SQL Server ?

    Introduction

    I am writing about very important part of our development How to schedule backup in SQL Server. We work sometimes offline (at our local machine) or online server, our day-to-day database development backup versions are very important in our SDLC. If we are working & omitting important information from back end MS SQL Server, It pulls back us, we need put extra efforts to recover the lost time of development.  I have provided below steps how to schedule backup in SQL server?

    Step 1: Open Microsoft SQL Server Management Studio to schedule a backup plan in SQL Server.
    Step 2: Then expand Management > folder

    Management

    Step 3: Open Management > Maintenance Plans folder.

    Management Plans
    Step 4: Right Click on “Maintenance Plans” & context Menu contains “New Maintenance Plans…

    New Maintenance Plan

    Step 5: A popup window will open to write your Maintenance Plan name.

    New Maintenance Plan








    Step 6: Left side a toolbox window will open where you can drag & drop “Backup Database Task” option.

    Maintenance Plans Task

    Step 7: Now two part of window will be shown upper part have Plan Name, Schedule, and Plan Description and lower part contain “Backup Database Task Plan”.


    Step 8:  Once drag & drop "Back Up Database Task", now on lower part you can see Back up database task window

    Back up Database Task



    Step 9: Right click on “Backup Database Task Plan” contains on lower part of window and click on “Edit” option.

    Backup Database Task

    Step 10: Now a new window will pop up where have different options to schedule your back up plan i.e. Connection, Backup Type, Database (Select databases as per requirements) etc.

    Backup Database Task

    Step 11: Now at the bottom of “Backup Database Task Plan” there is an option to check option “Create a sub-directory of each database” and we can set back plan “Folder” path.

    Backup Database Task

    Step 12: Upper part of Window there is a button to schedule maintenance day and time.

    Maintenance Plan Task

    Step 13. A new window will open with name “Job Schedule Properties”, sub plan name, Schedule Name, “Frequency” have Occurs, Recurs every, Daily frequency, Duration, Summary.

    Job Schedule Properties

    Step 14. You have done all steps now click on “OK” button.

    Your Management studio is not showing all list of configuring option, I have given below stored procedure run in your query analyzer & will up show advanced configuration options.

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Agent XPs', 1;
    GO
    RECONFIGURE
    GO

    Summary

    If you have done carefully all above 14 steps, It will help you schedule your backup maintenance plan moreover I have provided snapshots of each item so you can understand well when you are going to implement on to your machine.

    Video: How to Schedule Backup in SQL Server

    Suggested Reading