Create Single Table Backup

There are many ways to create backup for a single table in SQL Server database. 

In SQL Server 2008, you can create insert statements for selected tables. 

Export wizard is commonly used to create a flat file backup for a single table. 

Except above method I found following code by SQL Expert Fred.

USE AdventureWorks

GO

DECLARE @table VARCHAR(128),

@file VARCHAR(255),

@cmd VARCHAR(512)

-- If i need to create CSV file Product table then

SET @table = 'Production.Product'

SET @file = 'C:\BCP_OUTPUT\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112)

+ '.csv'

SET @cmd = 'bcp "AdventureWorks.' + @table + '" out "' + @file + '" -S. -T -c -t,'

EXEC master..xp_cmdshell @cmd

Above code uses BCP to create a CSV file for a given table. You can create a template of above code, and then just load, change values and execute. This method has a drawback. It creates a CSV file for all rows but it has no column header row. So how can I import this table later on, without column header row?

Then I found quickest and simplest way to achieve this goal. Simply type select * query but before execution

• Click on Tools > options and change values for Query Result Output Format and Custom Delimiter (I preferred pipe sign “|”)

• Press Ctrl+Shift+F buttons, so it can save result to file.

SELECT * FROM Production.Product

• On execution, provide file name and your desired path and it’s done

Split Name Into First, Middle and Last Name

Use below query to split Name into First, Middle and Last name.
SELECT LTRIM(SubString(CustomerName,1,ISNULL(Nullif(CHARINDEX(' ',CustomerName),0),1000))) As First_Name,  
LTRIM(SUBSTRING(CustomerName,CharIndex(' ',CustomerName),  
CASE WHEN (CHARINDEX(' ',CustomerName,CHARINDEX(' ',CustomerName)+1)-CHARINDEX(' ',CustomerName))<=0 then 0   
else CHARINDEX(' ',CustomerName,CHARINDEX(' ',CustomerName)+1)-CHARINDEX(' ',CustomerName) end )) as Middle_Name,  
LTRIM(SUBSTRING(CustomerName,ISNULL(Nullif(CHARINDEX(' ',CustomerName,Charindex(' ',CustomerName)+1),0),CHARINDEX(' ',CustomerName)),  
CASE WHEN Charindex(' ',CustomerName)=0 then 0 else LEN(CustomerName) end)) as Last_Name From CustomerMaster


Go  

Striped Backup of Database In SQL Server, Taking Database Backup In Different Parts

Sometime we may come across the problem of not having sufficient space in a single drive during Database backup. Suppose we have 2 drive in system. The drive C has 10 GB Space and drive D has also 10 GB.

Now we want to take backup of a Database which is over 10 GB. So we cannot take backup on any single drive. So in this case we can use striped backup. Striped backup is easy to use and can be done using SSMS and also from TSQL Query.

Open SSMS. Right click on Database Name. Click on Task > Back up….

Now a dialogue box opens for backup. Here we have added 2 backup files (C:\Backup1.bak and D:\Backrup2.bak). It is form of striped backup. So total size of backup will be divide into two parts.

Now during restore we have to select both file to restore.

We can do striped backup from TSQL query also:

BACKUP DATABASE VBDotNet TO   

DISK = N'C:\Backup1.BAK',   

DISK = N'D:\Backup1.BAK',   

WITH NOINIT,   

NAME = N'Database Backup',  

SKIP, NOREWIND, NOUNLOAD,  STATS = 10 

GO 

The above script will create 3 backup files. It will be dividing total size into 3 different files. Now during restore you must have all the files. If any of the file is missing, you cannot restore the database. Below is the TSQL Query to restore Database:

RESTORE DATABASE VBDotNet  

FROM  DISK = N'C:\Backup1.BAK',   

DISK = N'D:\Backup2.BAK' WITH FILE = 1,   

MOVE N'VBDotNet' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VBDotNet.mdf',   

MOVE N'VBDotNet_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VBDotNet.ldf',   

NOUNLOAD,  STATS = 10 

GO

sp_depends Stored Procedure

There are many undocumented Stored Procedure in SQL Server. 
sp_depends is one of them. This is a very useful stored procedure. This stored procedure reports the object dependencies in the current database. It shows all objects that depend on the specified object as well as all objects that the specified object depends on.


Syntax

USE AdventureWorks2008R2;
GO

 EXEC sp_depends 'Production.Product'


This SP will be removed from future version of SQL Server. So instead of using this we can use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities


Example:

USE AdventureWorks2008R2;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');
GO

USE AdventureWorks2008R2;
GO
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, referenced_minor_id, referenced_class_desc FROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');
GO

Disabled Triggers List

If you have triggers running against your database tables then you probably want to make sure they are turned on or off at the right times. Here's a script to check the status of your triggers.


SELECT db_name() AS [Database Name],
T.[name] AS [Table Name],
TR.[Name] AS [Trigger Name],
[Status] = CASE WHEN OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled') = 1
THEN 'Disabled' ELSE 'Enabled' END
FROM sysobjects T
JOIN sysobjects TR ON t.[ID] = TR.[parent_obj]
WHERE T.[xtype] = 'U'
AND TR.[xtype] = 'TR'

Cannot add column to existing table. Timeout expired

Sometime when we try to add a column to a large table, an error comes "Timeout expired". We can avoid this error by applying any of the method below:

Open SQL Server Managerment Studio > Goto Tools Menu > Option > Under Option Dialogue Choose Designers > Table and Database Designers

Uncheck  the option :“Override connection string time-out value for table designer update”

Open SQL Server Managerment Studio > Goto Tools Menu > Option > Under Option Dialogue Choose Designers > Table and Database Designers

Change“Transaction time-out after:” with high value

We can create Script of Table changes and Run It as Query. See under pic to generate query for Table changes

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

When you try to make change in table design using SQL Server Management Studio, the above error comes. This is default setting of SQL Server SSMS that it doesn’t allow user to modify the schema of table and throws the message on saving. The setting is “Prevent saving changes that require table re-creation”. It can be changed through SSMS.

Start SQL Server Management Studio

Go to Tools menu than choose Option

Click Designers > Table and Database Designers

Now uncheck the option: “Prevent saving changes that required table re-creation.”

Click Ok.

Grouping Sets

GROUPING SETS in MSSQL 2008 is an Extension to “Group By” and Gives ease of use and flexibility to define grouping expressions as compared to “RollUp” and “Cube”.

The advantage of using GROUPING SETS is that you can define grouping expressions in Grouping Sets.

Example

SELECT CASE WHEN CountryName IS Null THEN 'All Country' ELSE CountryName End AS CountryName,  

CASE WHEN StateName Is Null THEN 'Total' ELSE StateName END AS StateName, 

COUNT(CityName) CityCount  

FROM CityMaster Group By GROUPING Sets  

(

(CountryName,StateName),CountryName,()  

)  

Order by CountryName desc,StateName Desc  

create random string Function

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON 
GO 
Create View RandomString as select rand() as r
GO
CREATE Function [dbo].[GenerateRandomString] 
(
@StringLength AS smallint 
) 
RETURNS varchar(100) 
AS 
BEGIN 
DECLARE @RandomString varchar(100) 
DECLARE @characters varchar(100) 
DECLARE @count int SET @characters = '' 
-- Load Numbers 0 - 9 
SET @count = 48 
WHILE @count <= 57 
BEGIN 
SET @characters = @characters + Cast(CHAR(@count) as char(1)) 
SET @count = @count + 1 end 
-- Load uppercase Letters A - Z 
SET @count = 65 
WHILE @count <= 90 
BEGIN 
SET @characters = @characters + Cast(CHAR(@count) as char(1)) 
SET @count = @count + 1 end 
-- Load lowercase Letters a - z 
SET @count = 97 
WHILE @count <= 122 
BEGIN 
SET @characters = @characters + Cast(CHAR(@count) as char(1)) 
SET @count = @count + 1 
END 
SET @count = 0 
SET @RandomString = '' 
WHILE @count < @StringLength 
BEGIN 
SET @RandomString = @RandomString + SUBSTRING(@characters,CAST((SELECT r FROM RandomString)*LEN(@characters) as int)+1,1) 
SET @count = @count + 1 
END 
RETURN @RandomString 
END
GO


SELECT dbo.GenerateRandomString(12)
GO

ALTER DATABASE failed because a lock could not be placed on database

Sometime when we run the CheckDB query (dbcc CheckDB('DemoDB', REPAIR_ALLOW_DATA_LOSS) or while taking the database offline, we may face the following error:


ALTER DATABASE failed because a lock could not be placed on database 'DemoDB_DB'. Try again later.


Below is the simple solution to this error message:
Run sql command
EXEC sp_who2 


It will list all running processes.
Find your database name under dbname column.
Copy the SPID for that record and run this command


KILL <SPID> 


Try accessing the database now.
You should be able to access it successfully.

List tables that doesn't participate in any relationships

This query returns those tables which satisfy the below two conditions:

1. Tables that do not contain any Foreign Key referencing other tables.
2. Tables that are not referenced by other tables using foreign key constraints.
Solution: 
Till SQL Server 2000 days we used to write the below scripts [This still works with SQL Server 2005 also].
Select [name] as "Orphan Tables" from SysObjects where xtype='U' and id not in
(
Select fkeyID from SysForeignKeys
union
Select rkeyID from SysForeignKeys
) Solution which works only with SQL Server 2005:
Method 1:
Select [name] as "Orphan Tables" from Sys.Tables where object_id not in
(
Select parent_object_id from Sys.Foreign_Keys
union
Select referenced_object_id from Sys.Foreign_Keys
)
Method 2: 
Select ST.[Name] as "Orphan Tables"
from Sys.Foreign_Keys as SFK Right Join Sys.Tables as ST
On ST.object_id = SFK.parent_object_id Or
ST.object_id = SFK.referenced_object_id
Where SFK.type is null

Arithmetic overflow error converting expression to data type int.

Today I was trying to join two table each having approximately 1 million records in one of my SQL Server database. I was trying to find the count of some field when this arithmetic overflow error was thrown.

Arithmetic overflow error converting expression to data type int.

I understood that the calculation has exceeded the maximum INT range. 
The work around which did the trick for me is, instead of "Count" I changed it as "Count_big".

Find Last Restart of SQL Server

There are many ways you can see when SQL Server last restarted.


Method 1
The sys.dm_os_sys_info DMV contains a column that holds the startup time for SQL Server. You can run the following command to find the start time.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info


Method 2
If you open the SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log you can see when the server was started. 


Method 3
Another option is to use Windows Event Viewer to look for the startup time.  If you open Windows Event Viewer and filter your Event Sources (in our case MSSQL$SQL2008) for the SQL Server instance and Event ID 17162 you can find all of the startup times that are still in the Event Viewer log.


Method 4
Another option is to use the SQL Server Dashboard Report.  Right click on the server name in SSMS and select Reports > Server Reports > Server Dashboard and you will get a report

Enable Remote Connection on SQL Server 2008 Express

SQL Server 2008 Express doesn’t allow remote connection on default installation as on SQL Server 2005 Express. So you have to enable it manually.


If you’re trying to connect to SQL Server 2008 Express remotely without enable remote connection first, you may see these error messages:

• “Cannot connect to SQL-Server-Instance-Name
  
  An error has occurred while establishing a connection to the server.
  When connecting to SQL Server 2005, this failure may be caused by the
  fact that under the default settings SQL Server does not allow remote
  connections. (provider: SQL Network Interfaces, error: 28 – Server
  doesn’t support requested protocol) (Microsoft SQL Server)”

• “Cannot connect to SQL-Server-Instance-Name
  
  An error has occurred while establishing a connection to the server.
  When connecting to SQL Server 2005, this failure may be caused by the
  fact that under the default settings SQL Server does not allow remote
  connections. (provider: SQL Network Interfaces, error: 26 – Error
  Locating Server/Instance Specified) (Microsoft SQL Server)”
  
• “Cannot connect to SQL-Server-Instance-Name
  Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”
  

To enable remote connection on SQL Server 2008 Express, see the step below:

1. Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
2. Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
3. (Optional)
   Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Note: In SQL Server 2008 Express, there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.

Step-by-step

1. Open SQL Server Configuration Manager. Click Start -> Programs-> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
   
2. On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.
   
3. Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.
   
4. On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.
   
5. Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.
   
6. On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
   
7. Right-click on TCP/IP and select Enable to enable the protocol.
   
8. There is a pop-up shown up that you have to restart the SQL Service to apply changes.
   
9. On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
   
10. Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.
    
11. Right-click on the SQL Server Instance and select Properties.
    
12. On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.
    
13. Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.
    
14. Right-click on the SQL Server Instance and select Restart.
    

An error has occurred while establishing a connection to the server.

Error Description : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Solution 1:
Go to, Start  > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server 2005 Surface Area Configuration > Surface Area Configuration for Services and connections.
Within this check whether "Local and remote connections" is choosen. If not choose it.

Solution 2:
Check this URL http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1 :)

Using Switching you can move millions of rows from one table to another in less than a second

Assume that there are two tables table_1 and table_2 which have same structures and table_1 has 10 millions rows and you want to move all rows from table_1 to table_2. The quickest method that I have learnt is to use Partition Switching


ALTER TABLE Table_1 Switch TO Table_2;


The reason why it is fast is that data are not moved actually but SQL Server updates metadata about the location of the data

Moving master database

Moving master database is a bit different process than moving any other system database. Here is the process on how to move the master database.
Before doing anything, make sure you are having proper backups.
Stop the SQL Services.
Go to the "SQL Server Configuration Manager".
In SQL Server 2005, Click "Start" >> "All Programs" >> "Microsoft SQL Server 2005" >> "Configuration Tools" >> SQL Server Configuration Manager
In SQL Server 2008, Click "Start" >> "All Programs" >> "Microsoft SQL Server 2008" >> "Configuration Tools" >> SQL Server Configuration Manager
In the left pane, click on "SQL Server Services" 
Now in the right pane, select the SQL Server Service component  (which looks like "SQL Server (InstanceName)" ) and go to its properties.
In the "Properties" page, go to the "Advanced" tab
In the "Startup Parameters", click on the drop list and modify the parameters -d and -l to the new location where you want the master data file (master.mdf) and log file (mastlog.ldf) to reside respectively.


-d stands for the fully qualified data file path of master database.
-l stands for the fully qualified log file path of master database.
-e stands for the fully qualified path of the error log file.


Now move the files manually to the new location.
Start the SQL Services.

Hide an Instance of SQL Server in the network

1. Launch SQL Server Configuration Manager
2. Under "SQL Server Network Configuration" > Right-Click "Protocols for SQL01" > Select Properties
3. Set "Hide Instance" value to "Yes" from the drop-down list:
4. The Instance needs to be restarted in order for changes to take effect:Once the instance is restarted it will not longer appear in the "Network Servers" list:


We can do this through T-SQL also:
The information to Show/Hide instance is stored in the registry. We can use extended stored procedure xp_instance_regwrite to update the registry value to Show/Hide instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :
EXEC master..xp_instance_regwrite
      @rootkey = N'HKEY_LOCAL_MACHINE',
      @key =
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
      @value_name = N'HideInstance',
      @type = N'REG_DWORD',
      @value = 1
      – 0 = No, 1 = Yes
To check if an instance is hidden you can use xp_instance_regread to check registry values:
DECLARE @getValue INT
EXEC master..xp_instance_regread
      @rootkey = N'HKEY_LOCAL_MACHINE',
      @key=
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib',
      @value_name = N'HideInstance',
      @value = @getValue OUTPUT
SELECT @getValue
This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.