Structured Query Language (SQL)

SQL is a standard language for accessing and manipulating databases.
According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

The standard SQL commands are such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop”.The data in RDBMS is stored in database objects called tables.

A table is a collections of related data entries and it consists of columns and rows.

SQL can be divided into:
The Data Manipulation Language (DML) and
the Data Definition Language (DDL) ,
Data Control Language (DCL) and
Transactional Control Language (TCL) .

1) The query and update commands form the DML part of SQL.
SELECT – extracts data from a Table.
UPDATE – updates data in a Table.
DELETE – deletes data from a Table.
INSERT INTO – inserts new data into a Table.

2) The DDL part of SQL permits database tables to be created or deleted. The most important DDL statements in SQL are:
CREATE DATABASE – creates a new database.
ALTER DATABASE – modifies a database.
CREATE TABLE – creates a new table.
ALTER TABLE – modifies a table.
DROP TABLE – deletes a table.
CREATE INDEX – creates an index (search key).

3) DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Examples: GRANT, REVOKE statements

4) TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements

Determine users connected to sqlserver database

There are three ways to find who is connected to a database in sqlserver.

First one:

Use the SQL SERVER Management Studio — Management — Activity Monitor
This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

Second One:

Use the built in stored procedure called sp_who2
Run the command exec sp_who2
This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.
Third One:

Use the script
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = ‘databasename’

User this also
select * from sys.dm_exec_sessions

This gives the status, login name and host name for the database.

Restore MDF file without LDF file

1 ) Use this statement to create new log file.

sp_attach_single_file_db @dbname = ‘testcrashdb’
, @physname = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testcrashdb.mdf’

Message :File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testcrashdb_log.ldf” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testcrashdb_log.LDF’ was created.

2 ) Then attach the mdf and ldf file to restore db

USE [master]
GO
CREATE DATABASE [testcrashdb] ON
( FILENAME = N’C:\Program Files\location of mdf file\testcrashdb.mdf’ ),
( FILENAME = N’C:\Program Files\location of log file\testcrashdb_log.ldf’ )
FOR ATTACH
GO

How To Rebuild The master Database :

If your SQL Server master database becomes corrupt, such as from its disks losing power while SQL Server was running, the conventional advice is to rebuild the master database, start SQL Server, then restore the backup of the master database. That’s because SQL Server won’t start with a corrupt master database. But rebuilding the master database is usually quite time-consuming and frustrating, with a substantial amount of trial-and-error, especially if it’s on a cluster. It can be so troublesome, a lot of people consider reinstalling SQL Server from scratch!

Instead, you can restore a backup of the master database on another instance of SQL Server as long as it’s the same version of SQL Server, then use the restored files to replace the corrupt files on the broken system.

Steps : Restore your master database backup to another instance of SQL Server and in the “To database:” section of the Restore Database dialog box, use an alternate database name such as “recover_master” to avoid conflict with the master database on this instance of SQL Server. This will cause the mdf/ldf to be named “recover_master.mdf” and “recover_master_1.ldf”. Then detach the recover_master database, go into the file system, copy the recover_master.mdf and recover_master_1.ldf to the instance of SQL Server with the corrupt master database. Delete the corrupt master.mdf and mastlog.ldf files, rename recover_master.mdf to master.mdf, and rename recover_master_1.ldf to mastlog.ldf. Now you’re ready to try starting the SQL Server service again!

Because the master database is usually very small, this method should just take a few minutes, it should work for all versions of SQL Server, and it works for both stand-alone instances and clustered instances.

IMPORTANT: The version of SQL Server used for the restore has to match that of the damaged instance. For example, if you restore a database backup from SQL Server 2005 on SQL Server 2008, the restore process will modify the structure of the file, and you won’t be able to use the restored mdf/ldf on the older version of SQL Server.

By the way, if you’ve ever experienced pain in rebuilding a master database, and your reaction to this post is “why didn’t I think of that?”… it could have been because you were under a lot of pressure to get the system working again, etc.

Think of how bad off you’ll be if you don’t have a backup of your master database: You’ll be wasting a lot of time recreating logins, roles, database mappings, and setting permissions — all of which could be avoided with a simple backup. And test restores not only validate the quality of your backups, they also give you valuable practice so the first time you perform restores isn’t while you’re under the stress of a real disaster!

Giving and removing permissions in SQL Server:

SQL Server offers three pretty simple commands to give and remove access, these commands are:

GRANT – gives a user permission to perform certain tasks on database objects
DENY – denies any access to a user to perform certain tasks on database objects
REVOKE – removes a grant or deny permission from a user on certain database objects
Here are some examples of these commands.

Allow users Joe and Mary to SELECT, INSERT and UPDATE data in table Customers

GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary

Revoke UPDATE access to table Customers for user Joe

REVOKE UPDATE ON Customers to Joe

DENY DELETE access to table Customers for user Joe and Mary

DENY DELETE ON Customers to Joe, Mary

As you can see from the above examples it is pretty easy to grant, deny and revoke access. In addition to grant SELECT, INSERT, DELETE and UPDATE rights you can also grant EXECUTE rights to run a stored procedure as follows:

GRANT EXEC ON uspInsertCustomers TO Joe

To determine what rights have been granted in a database use the sp_helprotect stored procedure.

In addition to granting rights to objects that you create you can also grant users permissions to do other tasks such as create tables, views, stored procedures, etc… To grant a user permissions to create a table you would run this command.

GRANT CREATE TABLE TO Joe

As you can see granting rights and permissions to certain features is not all that difficult to do. Take the time to understand what permissions are really needed by the database users and grant, deny and revoke accordingly instead of just using the default database roles.

What is the difference between sdf and mdf files :

SDF: It is the database format of SQL Server Compact edition. This is the embedded edition of sql server. Think about it as a “single-dll” sql server. Thus it will run together with the application that is using it. It is not a service, it is not a real server. But if your database is accessed from a single application (even a web application that is multi-user), this is a good approach, although it has some limitations.

An SDF file is a Sql Server Mobile file. It is also referred to as Sql Server compact. It is predominantly used in mobile development but recently more and more common in single user desktop developments as well.

Visual Studio 2008 does not support Linq to Sql for Sql Server Mobile but the Framework does.

MDF: It is the database format of the “big”, real server editions. Indifferent which one you use (from express to datacenter), this is the format. But it depend on many things, which edition you need/can use – one of this things is the amount of money you are willing to pay .

One of the main differences between a Sdf and Mdf is that until recently an Mdf could not be opened and treated like a normal file, whereas an Sdf is accessed as a file.
However in Visual Studio this concept was reproduced by allowing Sql Server Data files which dynamically attaches the Mdf to a Sql Server Express session.

The difference is that SQLServer is a database server intended to serve
1 to many users (client apps, web requests or whatever) while SQLServerCE
is an embedded database that is superb for serving 1 users

Understanding roles in SQL Server security:

Role types:
Server roles are maintained by the database administrator (DBA) and apply to the entire server, not an individual database file. The public role sets the basic default permissions for all users. Every user that’s added to SQL Server is automatically assigned to the public role—you don’t need to do anything. Database roles are applied to an individual database.

Predefined database roles:
You may need to create your own, but you have access to several predefined database roles:
db_owner: Members have full access.
db_accessadmin: Members can manage Windows groups and SQL Server logins.
db_datareader: Members can read all data.
db_datawriter: Members can add, delete, or modify data in the tables.
db_ddladmin: Members can run dynamic-link library (DLL) statements.
db_securityadmin: Members can modify role membership and manage permissions.
db_bckupoperator: Members can back up the database.
db_denydatareader: Members can’t view data within the database.
db_denydatawriter: Members can’t change or delete data in tables or views.

Fixed roles:
The fixed server roles are applied serverwide, and there are several predefined server roles:
SysAdmin: Any member can perform any action on the server.
ServerAdmin: Any member can set configuration options on the server.
SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.
Security Admin: Any member can manage server security.
ProcessAdmin: Any member can kill processes running on SQL Server.
DbCreator: Any member can create, alter, drop, and restore databases.
DiskAdmin: Any member can manage SQL Server disk files.
BulkAdmin: Any member can run the bulk insert command.