Some SQL Tips

1. I am back after a very long break. So, I somehow forgot SQL Server ‘SA’ Password, and this is how I recovered that.

• Stop SQL SERVER
• Change the startup parameters by adding add -m;
• Start SQL Server
• Open command prompt and type SQLCMD

Method 1: Create new user and add to systemadmin role

CREATE LOGIN recovery1 WITH PASSWORD = 'abc123%';
sp_addsrvrolemember 'recovery1', 'sysadmin';
GO;

Now login using created user and change the ‘Sa’ password.

Method 2: Change Password

EXEC sp_password NULL, abc123!@#$', 'sa';
GO;

Method 3: Give system admin roles to the Windows user

EXEC sp_addsrvrolemember 'Softcat\tharaka_r', 'sysadmin';
GO;

2. I could not remember who wrote this query, but I am sure it was not me. :p So, I just simplified that.

DECLARE @RunDate AS DATETIME = getdate()
SELECT CONCAT (DATEPART(yyyy,DATEADD(dd,30,@RunDate)),'-', _
DATEPART(mm,DATEADD(dd,30,@RunDate)),'-', _
DATEPART(dd,DATEADD(dd,30,@RunDate))) AS DateNow , 30 AS Value;

The simplified version is as given below:

DECLARE @RunDate AS DATETIME = getdate();
SELECT CONVERT(VARCHAR(10),DATEADD (dd,30, @RunDate),120) AS DateNow , 30 AS Value;

Filed under: CodeProject, SQL
Tagged: Database, SQL, SQL Server