Basic Database Operation Using SSMS

(1)

203

Basic Database Operation Using SSMS

Mr. Hrishikesh Belgamwar

1

, Prof. S.L.Thombare

2 1

Student, I.T. Department, J.D.I.E.T, Yavatmal, [email protected]

2

Assistant Professor, I.T. Department, J.D.I.E.T, Yavatmal , [email protected]

ABSTRACT- In today’s world, database sizes everywhere are growing with high speed. Many applications / systems has Tera bytes to Pico bytes of data. It is challenging to support and maintain such huge database. Every year companies spend big money to maintain their Database servers and hire professional DBAs. Getting skilled and expert DBA has been always challenging for the organizations. On the other-hand, it has been equally difficult for the IT professionals to learn and get all the DBA skills under his belt. Most of the DBA learn the required skills on job or the companies have to provide them through training. It costs lot of money and time. Understanding the practical difficulties faced by the companies and the stiff learning challenges for the Fresh IT Graduate, we came up with idea of helping newbie DBAs to quickly learn the essential DBA skills and become a Pro-DBA. This Paper helps DBA to perform like a gem and produce Specific, Measurable, Accurate, Reliable, and Time-bound solutions. Write the underneath SQL commands of that operation in the textbox. And relevant description in brief. This will help the DBA in learning things for doing it SMARTly.

Keywords: DBA, Reliable, Time-Bound

1. INTRODUCTION

In this Paper Contain the Various type of DBA Activities i.e. Create, Drop, Attach - Detach, Online - Offline, Shrink and Server Explorer all this types are use in this paper. How to performed the operation in SQL Server Management Studio this is write in this paper. Two ways of performing activity are as follows :-

1) Using GUI ( Graphical User Interface ) 2) Using T - SQL (Transact SQL Script ) 1) Using GUI :-

In that type contain step of activity perform and the snapshots are also available. For going this way in SQL Server Management Studio and performed the activity.

2) Using T-SQL :-

In this type write the SQL query of all operation included in this Paper i.e. Create, Drop, Attach - Detach, Online - Offline, Shrink . if write this query in window of sql server then executed and completed the operation Successfully. Using this Paper DBA doing work of SMARTly i.e. Specific, Measurable, accurate, Reliable, and Time -Allocation Understanding the practical difficulties faced by the companies and the stiff learning challenges for the Fresh IT . Graduate, we came up with idea of helping newbie DBAs to quickly learn the essential DBA skills and become a Pro - DBA.

2. CREATE DATABASE

Create Database for using storing data in table in rows and column, as well as database contain views, function. In one SQL Server Management Studio 32767 Database is created. Creating database it is recommended to create data file and log file under different drive as well as same drive also. Usually the datafile is of bigger size than log file.

In SQL Server Management studio Databases is created using two ways. 1) Using Graphical User Interface 2) Using T-SQL Script

(2)

3. ONLINE / OFFLINE DATABASE

Database is made offline to move its physical files. it is not deleted physically it can be brought back online

If Database is offline and need this database that time online the database using GUI or T In SQL Server Management studio Databases is Online/ Offline using two ways.

1) Using Graphical User Interface 2) Using T

A) Online the database by using GUI :

Step 1: Select Database Step 2: Right click on Database Step3: Click on Tasks

Step4:Click on Bring Online

A) Using Graphical User Interface

Steps of Creating Database are as follows:-

Step1: Right click on Databases Step2: Select New Databa Step3: Mention Database name, path, size, maxsize, file growth Step 4: Press Ok

. ONLINE / OFFLINE DATABASE :-

Database is made offline to move its physical files. Database Offline means it is not available for users, it is not deleted physically it can be brought back online.

If Database is offline and need this database that time online the database using GUI or T ement studio Databases is Online/ Offline using two ways.

1) Using Graphical User Interface 2) Using T-SQL Script

Online the database by using GUI : A) Offline the database by using GUI :

Step 1: Select Database Step 2: Right click on Database Step3: Click on Tasks

Step4:Click on Take Offline ) Using Graphical User Interface :-

Steps of Creating Database are as

Step1: Right click on Databases Step2: Select New Database

Step3: Mention Database name, path, size, maxsize, file growth

B) Create Database Using T

Create database Databasename primary on

(

name=Databasename.mdf , Filename = 'C:\

size = <size> MB, maxsize = <size>MB,

Filegrowth = 10% )

logon ( name= Databasename.ldf, Filename = 'C:

size = <size> MB,

maxsize = <size> MB, Filegrowth = 10%

)

204 Database Offline means it is not available for users,

If Database is offline and need this database that time online the database using GUI or T-SQL Script. ement studio Databases is Online/ Offline using two ways.

Offline the database by using GUI :

) Create Database Using T-SQL Script :-

Create database Databasename primary on

(

name=Databasename.mdf , \\Databasename_Data.mdf', size = <size> MB, maxsize = <size>MB,

Filegrowth = 10% )

logon ( name= Databasename.ldf,

Filename = 'C:\\Databasename_Log.ldf', size = <size> MB,

maxsize = <size> MB, Filegrowth = 10%

(3)

B) Online the database by using Script :

Alter database <Database_name> Set online

4. DROP DATABASE

Drop Command is use to all the rows is remove an object from database. It is also make free space and table structure is also remove. Drop Command remove the physical file also.

In SQL Server Management studio Databases is Online/ Offline using 1) Using Graphical User Interface 2) Using T A) Drop the database by using GUI :

Step 1: Select Database Step 2: Right click on Database Step3: Click on Delete

B) Drop the database by using T-SQL command :

Drop database <Database_name>

5. Attach / Detach DATABASE

Attach Database means attach the data file and log file from program files. if any database is delete or drop from SSMS but after that this database is

SQL Script or using GUI. therefore time is also save and less memory required.

Detaching removes the registration of the database within SQL Server. After Detaching, the database will be remove from SQL Server but will be intact within data and transaction Log File that compose the database.

In SQL Server Management studio Databases is Online/ Offline using two ways. 1) Using Graphical User Interface

database by using Script : Alter database <Database_name>

B) Offline the database by using Script :

Alter database <Database_name> Set offline with rollback immediate

Drop Command is use to all the rows is remove an object from database. It is also make free space and table structure is also remove. Drop Command remove the physical file also.

In SQL Server Management studio Databases is Online/ Offline using two ways. 1) Using Graphical User Interface 2) Using T-SQL Script A) Drop the database by using GUI :

SQL command : Drop database <Database_name>

Attach Database means attach the data file and log file from program files. if any database is delete or drop from SSMS but after that this database is required then do not create database. Directly attach database using T SQL Script or using GUI. therefore time is also save and less memory required.

In SQL Server Management studio Databases is Online/ Offline using two ways. 1) Using Graphical User Interface 2) Using T-SQL Script

205 Offline the database by using Script :

Alter database <Database_name> Set offline with rollback immediate

Drop Command is use to all the rows is remove an object from database. It is also make free space and table

two ways.

Attach Database means attach the data file and log file from program files. if any database is delete or drop required then do not create database. Directly attach database using T-

(4)

A) Attach the database by using GUI : Step 1: Select Database

Step 2: Right click on Database Step 3: Select Attach

Step 4: Add File Step5 : Click Ok

B) Attach the database by using Script :

Exec sp_attach_db @dbname =N' <Database Name>', @filename1 = N'C:\<Database Name>.mdf',

@filename2 = N'C:\<Database Name>_log.ldf';

6. SHRINK DATABASE :-

Shrink Databases or data files

unoccupied space closer to the front of the file. When enough free space is created at the end of file can be deallocated and returned to the file system. Shrink Database means compress

in MB or Percentage(%). In SQL Server Management studio Databases is Online/

1) Using Graphical User Interface A) Database shrinking by using GUI:

Step 1: Select the database Step 2: Right click on it

Step 3: Go to task and then click on shrink

Step 4: Click on database which you want to shrink Step 5: If you want to shrink database as per your convenience then and number in percentage

Step6:Click OK

) Attach the database by using GUI :

Attach the database by using Script :

Exec sp_attach_db @dbname =N' <Database Name>', <Database Name>.mdf',

<Database Name>_log.ldf';

A) Detach the database by using GUI : Step 1: Select Database

Step 2: Right click on Database Step 3: Click on Tasks

Step 4: Click on Detach Step 5: Check drop and update Step 6: Click on Ok

B) Detach the database by using Script : Exec sp_detach_db <Database Name>

Shrink Databases or data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of file can be deallocated and returned to the file system. Shrink Database means compress Database or mak

.

In SQL Server Management studio Databases is Online/ Offline using two ways. 1) Using Graphical User Interface 2) Using T-SQL Script

rinking by using GUI:

Step 3: Go to task and then click on shrink

Step 4: Click on database which you want to shrink Step 5: If you want to shrink database as per your

convenience then and number in percentage

Click OK

B) Shrink database by using T USE [ database_ name] GO

DBCC SHRINKDATABASE(N’da in %)

GO

206 ) Detach the database by using GUI :

Step 1: Select Database Step 2: Right click on Database

tep 3: Click on Tasks Step 4: Click on Detach Step 5: Check drop and update

B) Detach the database by using Script : Exec sp_detach_db <Database Name>

recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of file can be or make available free space .

Offline using two ways. SQL Script

B) Shrink database by using T-SQL:

(5)

7. BACKUP DATABASE

It is full copy of your entire database. Although the full backup provides best protection, most organization used their on a periodic basic because they are time consuming and often require large disk space.

is use for if in case any disaster is occur like t this backup is restore in SSMS and use the database.

In SQL Server Management studio Taking Full Backup of Database by using two ways. 1) Using Graphical User Interface 2) Using T

A) Full database Backup by using GUI :

Step 1: Select Database Step 2: Right click on Database Step 3: Click on tasks

Step 4: Click on Back Up

Step 5: Select Database, Backup Type Differential

Step 6 : Select Component - Database, write Path Step 7 : Add destination Path

Step 8 : Select Option Page

Step 9 : Choose Overwrite Media, Write Media Set Name

Step 10: Click on OK

8. CONCLUSION

I Conclude that during the study of DBA i found that various ty explained only 5-6 operation on database

Server Management Studio. This paper is very useful to newbie student of dba for understanding the basic database operation with GUI step as well as query. i hope so this is very beneficial to all the student.

REFERENCES

[1] http://dev.mysql.com/doc/refman/5.7/en/creating

[2] https://pubs.vmware.com/vsphere50/index.jsp?topic=%2Fcom.vmware.vsphere.upgrade.doc_50%2FGUID -15C8CA36-C7C8-4846-8416

[3] http://stackoverflow.com/questions/31866585/how [4] http://www.tutorialspoint.com/sql/sql

[5] http://technet.microsoft.com/

your entire database. Although the full backup provides best protection, most organization used their on a periodic basic because they are time consuming and often require large disk space.

is use for if in case any disaster is occur like that Harddisk is crash or database is deleted by mistake then that time this backup is restore in SSMS and use the database.

In SQL Server Management studio Taking Full Backup of Database by using two ways. 1) Using Graphical User Interface 2) Using T-SQL Script

A) Full database Backup by using GUI :

Step 5: Select Database, Backup Type -

Database, write Path

Step 9 : Choose Overwrite Media, Write Media

B) Full Database Backup By Using T BACKUP DATABASE Dbname TO DISK=’C:\Dbname_Full.Bak’ WITH FORMAT,

MEDIANAME=’C:\’;

Conclude that during the study of DBA i found that various type operations on database . In this paper i have 6 operation on database with Using GUI i.e. Graphical User Interface and T

paper is very useful to newbie student of dba for understanding the basic database operation with GUI step as well as query. i hope so this is very beneficial to all the student.

http://dev.mysql.com/doc/refman/5.7/en/creating-database.html

https://pubs.vmware.com/vsphere50/index.jsp?topic=%2Fcom.vmware.vsphere.upgrade.doc_50%2FGUID 8416-692D4D7EEC3B.html

tackoverflow.com/questions/31866585/how-to-bring-back-offline-database http://www.tutorialspoint.com/sql/sql-drop-database.htm

http://technet.microsoft.com/msdn

207 your entire database. Although the full backup provides best protection, most organization used their on a periodic basic because they are time consuming and often require large disk space. Backup Database Harddisk is crash or database is deleted by mistake then that time

In SQL Server Management studio Taking Full Backup of Database by using two ways. SQL Script

B) Full Database Backup By Using T-SQL Script :

Dbname_Full.Bak’

pe operations on database . In this paper i have with Using GUI i.e. Graphical User Interface and T- SQL Script in SQL paper is very useful to newbie student of dba for understanding the basic database operation with GUI step as well as query. i hope so this is very beneficial to all the student.

https://pubs.vmware.com/vsphere50/index.jsp?topic=%2Fcom.vmware.vsphere.upgrade.doc_50%2FGUID