Tuesday, March 29, 2016

Add a column, with a default value, to an existing table in SQL Server


Sample data

CREATE TABLE [DBO].[ADD_COLUMN_TEST](
                [ID] [INT] IDENTITY(1,1) NOT NULL,
                [NAME] NVARCHAR(50) NULL,
                [COUNTRY] NVARCHAR(50) NULL
) ON [PRIMARY]

GO

now insert the data in table
INSERT INTO [ADD_COLUMN_TEST] VALUES('BRAIN','US')
INSERT INTO [ADD_COLUMN_TEST] VALUES('MICHEL','US')



BELOW SYNTAX IS USED TO ADD A COLUMN TO EXISTING TABLE WITH DEFAULT VALUE
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Now add column with default value to the add_column_test table

ALTER TABLE [add_column_test]
ADD newcolumn1 int NOT NULL DEFAULT(1)
GO

 If it is varchar or nvarchar use below syntax
ALTER TABLE [add_column_test] ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'NEWDATA'


Wednesday, March 23, 2016

Convert month number into month name in derived column expression

Scenario 1: Sometimes we need to convert (or) we need to extract month name from the date time datatype (or) any date format by using derived column or in expression task

Syntax for changing date to month name:-
(MONTH(getdate()) == 1 ? "January" :
 MONTH(getdate()) == 2 ? "February" : 
 MONTH(getdate()) == 3 ? "March" :
 MONTH(getdate()) == 4 ? "April" :
 MONTH(getdate()) == 5 ? "May" : 
 MONTH(getdate()) == 6 ? "June" :
 MONTH(getdate()) == 7 ? "July" :
 MONTH(getdate()) == 8 ? "August" :
 MONTH(getdate()) == 9 ? "September" : 
 MONTH(getdate()) == 10 ? "October" : 
 MONTH(getdate()) == 11 ? "November" : 
 MONTH(getdate()) == 12? "December":"")
Replace column name as per your table column name , here i replaced with [date] 


Place below expression in derived column expression area:-
(MONTH([date]) == 1 ? "January" : MONTH([date]) == 2 ? "February" : MONTH([date]) == 3 ? "March" : MONTH([date]) == 4 ? "April" : MONTH([date]) == 5 ? "May" : MONTH([date]) == 6 ? "June" : MONTH([date]) == 7 ? "July" : MONTH([date]) == 8 ? "August" : MONTH([date]) == 9 ? "September" : MONTH([date]) == 10 ? "October" : MONTH([date]) == 11 ? "November" : MONTH([date]) == 12? "December":"")



Scenario 2: Sometimes we need to convert (or) we need to extract day name from the date time datatype 

Syntax for changing date to day name:-
(DATEPART("dw", GETDATE())==1 ? "Sunday" :
DATEPART("dw", GETDATE())==2 ? "Monday" :
DATEPART("dw", GETDATE())==3 ? "Tuesday" :
DATEPART("dw", GETDATE())==4 ? "Wednesday" :
DATEPART("dw", GETDATE())==5 ? "Thursday" :
DATEPART("dw", GETDATE())==6 ? "Friday" :
DATEPART("dw", GETDATE())==7 ? "Saturday" : "") 

Place below expression in derived column expression area :-
(DATEPART("dw", [date])==1 ? "Sunday" : DATEPART("dw", [date])==2 ? "Monday" : DATEPART("dw", [date])==3 ? "Tuesday" : DATEPART("dw", [date])==4 ? "Wednesday" : DATEPART("dw", [date])==5 ? "Thursday" : DATEPART("dw", [date])==6 ? "Friday" : DATEPART("dw", [date])==7 ? "Saturday" : "")





Tuesday, March 22, 2016

How to backup a database to a network drive

As part of disaster recovery sometimes we require to take backup in network share drive. Most of the users are facing issues while doing this activity follow below steps to take back up in network drive.

Enable backups to network drive in sql server :
  • Check whether sql server running under domain account or local system/network service. if it is not running under domain account follow below steps to change service to domain account.
  • Open services.msc and click in sql services properties and change it to domain account as show below. password must be the domain user's

  • In order to take back up in remote machine your sql service must run under domain account.
  • Share the permission to the backup folder (destination machine/remote machine) and assign read/write permission to the user as shown in below image 


USE  AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = '\\Remotemechinename\\SQLServerBackupsfolder\' --Remote Machine folder path
  WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of 
AdventureWorks2012';
GO
  • As show in above syntax at to disk place replace your remote machine name and folder name which you share earlier steps.
  • Replace Remotemechinename  with machine name where you storing backups,
  • Replace SQLServerBackupsfolder with folder backup folder.
  • Run the query.