Session 3

Creating / Deleting / Restoring a Database

Click here for you tube video


---------------------------------------
--> 1. Creation of database using SQL
---------------------------------------

--Syntax:

CREATE DATABASE <Database Name>

--> Creating database "SQL_Demo" Using SQL

CREATE DATABASE SQL_Demo



USE master ;
GO
CREATE DATABASE SQL_Demo
ON 
( NAME = SQL_Demo_data,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\SQL_Demodata.mdf',
    SIZE = 10,  MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON
( NAME = SQL_Demo_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\SQL_Demolog.ldf',
    SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) ;
GO



--> To Switch to the database Context "SQL_Demo"

USE SQL_Demo

-- OR choose from the drop down list of databases (GUI)






---------------------------------------
--> 2. Creation of database using GUI
---------------------------------------

--> Creating database "SQL_Demo" Using GUI

--> In Object Explorer, Right Click Databases folder
--> Click on New Databases
--> Provide Database Name
--> change the default path of data file and log file if need else use default
--> Click OK




---------------------------------------
--> 3. Deleting of database using SQL
---------------------------------------
--DROP DATABASE <DATABASENAME>

DROP DATABASE [SQL_Demo]


---------------------------------------
--> 4. Deleting of database using GUI
---------------------------------------
CREATE DATABASE [SQL_Demo]

---------------------------------------
--> 5. Exlopring the database files
---------------------------------------

CREATE DATABASE SQL_Demo

--At minimum there will be 2 files will be present SQL Server

--> Master Data File --> .mdf

--> Secondary Data File --> .ndf (Optional user-defined data files) / Data spread across multiple disks

--> Log File --> .ldf

--> Adding a secondary data file to database

-------------------------------------------------------------
--> 6. Restoring Database using backup file (.bak) using GUI
-------------------------------------------------------------

--> In Object Explorer, Right Click Databases folder
--> Click on Restore Database...
--> Choose the source
--> Click OK


--Few Sample Databases:

--> Adventureworks
--> WideworldImporters

link for downloading the sample databases:

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms