:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

Managing SQL Server 2012 Databases

 

 

SQL Server 2012 database or a database is a set of structured data that contains tables and columns. Every column has a data type associated to it, rather be integer, double, currency or string. Multiple databases can reside in a single SQL server 2012 instance. Instance in SQL Server is an isolated installation that allows system administrators and database administrators to set various settings. SQL Server creates an EDB file for every database created. This is how SQL Server store the data on the server. To improve query performance, often these files are located on fast SAS 10K RPM disks or SAN hardware such as Netapp filers. Solid State Drives, SSDs are getting cheaper and this may be the future for IO intensive SQL databases.

SQL Management Studio in SQL Server 2012 is a main tool used for managing SQL Server 2012 databases. This tool also includes a tool to run TransactSQL queries. Out of the box, every SQL instance contains five system databases—Master DB, msdb DB, model DB, Resource DB, tempdb DB. Every database stores the data on filesystem using .edb and .ldf file. .EDB file stores all the data such as indexes, stored procedures and tables. Where .ldf file stores all database transactions that is required to recover the data. Filegroups in SQL Server consist of all data files that makes up the database. For example, you may have a primary data file that resides one drive and secondary data file that resides on the second drive.

 

Transporting a Database Between Servers

You may need to copy or move a database between different SQL Servers. A lot of times for testing or dev environments, you need a copy of the current production database. There are a few options to get the job done:

• You can use the database copy wizard
• If you can afford to take the database offline, you can deattached and reattach the database. To deattach a database, rightclick on the database, choose Tasks, then click on deattach.

 

How to Create a SQL 2012 Database

To create a new database using SQL Management studio:

1. Open SQL Management Studio.
2. Rightclick Databases and choose “New Database”
3. You may also rename the newly created database by rightclicking and choosing rename.

 

To Increase the Database Size

1. Open SQL Management Studio.
2. Rightclick Databases and choose “properties”
3. Select files and enter value in “Initial Size” field.

 

How to Shrink the Database

1. Open SQL Management Studio.
2. Rightclick Databases, choose “Tasks” and click Shrink
3. Choose “reorganize files before releasing unused space”.