SQL Database

What is SQL Database?

In simple database is collection of objects which stores a specific set of structured data.

Let us consider an example of Facebook. It needs to store, manipulate, and present data related to members, their friends, member activities, messages, advertisements, and a lot more. These all information is stored in different tables and collection of these tables are kept at one place.

 

How to CREATE SQL Database?

To create a new database in a SQL server use CREATE DATABASE statement.

Syntax:

CREATE DATABASE DATABASENAME

 

How to ALTER existing SQL Database?

If we want to change the database name or file locations or collations etc. then we use ALTER DATABASE statement.

Syntax:

ALTER DATABASE { database_name  | CURRENT } 

{   MODIFY NAME = new_database_name  

  | COLLATE collation_name 

  | <file_and_filegroup_options> 

  | SET <option_spec> [ ,...n ] [ WITH <termination> ]

  | SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 }  

}

To modify existing database name in a SQL server

ALTER DATABASE OLD_DATABASENAME MODIFY NAME = NEW_DATABASENAME

To change .mdf/.ldf file names

Alter DATABASE DATABASENAME MODIFY FILE ( NAME = OLDNAME, NEWNAME = NEWNAME )

To change .mdf/.ldf file location

Alter DATABASE DATABASENAME

MODIFY FILE ( NAME = Edu_TSQL_NewName, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\New_File\filename_log.ldf' )

 

How to DROP SQL Database?

To drop existing database in a SQL server we use DROP DATABASE statement.

If you feel the database is no more required in the server then we drop the database to save space.

Syntax:

DROP DATABASE DATABASENAME

 

How to BACKUP SQL Database?

To backup existing database in a SQL server we use BACKUP DATABASE statement.

Taking backup of database in a periodic manner is a good practice. Which helps us to recover the data in case of any disaster.

Syntax:

BACKUP DATABASE DATABASENAME TO DISK = 'D:\backups\testDB.bak'

WITH DIFFERENTIAL will help you to take backup of the parts of database that have changed since the last full database backup.

BACKUP DATABASE DATABASENAME TO DISK = 'filepath' WITH DIFFERENTIAL

How to RESTORE SQL Database?

To restore a database in a SQL server we use RESTORE DATABASE statement.

Suppose we have a database which is running fine till yesterday and today a developer had made some changes to the database which unfortunately corrupted the database. In this case we can use the backup file to restore the database.

Syntax:

RESTORE DATABASE DATABASENAME FROM DISK = 'FILE LOCATION + FILENAME.BAK'