To Enable the SQL Server Admin login


In a case the SA account is locked and the windows authentication is not enabled in the SQL Server, go to the SQL Server configuration manager and then go to server properties and choose the startup tab. here, add -m as a startup parameter.

This will enable the resetting of the sa password by the windows authentication login.

Advertisements

To restore a Database from a bak file


In order to restore a database that was backed up from an existing server or a remote server, the following sql command can be used

 

RESTORE DATABASE [MyDb] FROM DISK=’C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL\MSSQL\Backup\MyDb.bak’
WITH MOVE ‘MyDb’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL\MSSQL\DATA\MyDbdata.mdf’, MOVE ‘CelloV45_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL11.LOCAL\MSSQL\DATA\MyDblog.ldf’

Get All Foreign Key References for a table


The following query returns the columns and the foreign keys that the given table has.

For example , if there are two tables like Sales and Customer, If we were to hunt for the foreign keys of Customer in the entire database, the following query will return the list of tables, columns and foreign key names that refer the columns in this table.

 

Example : EXEC sp_fkeys ‘TableName’

 

The result will be the columns in all other tables in the current database context that has foreign key references to the given ‘TableName’ table.

SQL update from one Table to another based on a ID match


I have an employees table and an EmployeeTerritories table. The employeeid in both these tables are in integer datatype.   I have to create a new unique identifier column in both these tables and then update them accordingly.

1. I created a newsequentialid column in the employees table that will create new ids for the employees

2. Next, i added a new column to the employee territories table and set it as a foreign key to the employees table

3. now, i am in need of a query that will fetch the new guid from the employee table and fill in the employee territories table

4. i need to have a single update statement that do the trick by making use of the existing numerical id values

UPDATE EmployeeTerritories
SET
	empid = Id
FROM
	EmployeeTerritories
	INNER JOIN Employees
		ON Employees.EmployeeID = EmployeeTerritories.EmployeeID

This update statement will update the empid in the territories table based on the id matching. This saves a lot of time in comparison to the manual matching of ids and updating the data.

Enabling the SQL Server to be accessed across machines or from remote machine


Following steps are to be followed so that the SQL Server can be accessed across machines or be accessed from a remote machine via Sql Server Management Studio [SSMS]

Enable the following
1) TCP/IP,
2) Shared Memory
3) Named Pipes

from all prorams> sql 2012 > configuration tools > sql server configuration manager > expand sql server network configuration > protocols for sql2012r2de

Now, in the “Run” command, put Services.msc and then choose the SQL Server and then enable the SQL Server Browser service

Now try connecting from any remote machine.

Trackback: http://stackoverflow.com/questions/5956926/unable-to-connect-to-a-sql-server-database-remotely