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.
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’
To Drop a constraint in a given table, the following SQL can be used
USE [DATABASE_NAME] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Name]’) AND parent_object_id = OBJECT_ID(N'[dbo].[TableName]’)) ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name] GO
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.
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
empid = Id
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.
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
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.