how can make new database in MsSqlServer

/*
Hello Friends Today You will Learn
How Can Make A New Database IN MANAGEMENT STUDIO
WITH USE OF GRAPHICAL USER INTERFACE AND
WITH QUERY EDITOR
NOTE: THESE STEPS ARE FOR
MSSQLSERVER2005 AND
IN
MSSQLSERVER2008 ARE SAME
*/
/*
STEP 1: ENSURE TO THIS IN YOUR PC MSSQLSERVER 2005 OR
MSSQLSERVER2008 ARE COMPLETLY INSTALL ESSENTIALLY MSSTUDIO
NOW RUN YOU MSSTUDIO
*/
/*
STEP2: NOW CONNECT TO DATABASE ENGINE(if not connected) AND RIGHT CLICK ON Databases
then select new database option(double click on it) and make new database
*/
–STEP 3: NOW WITH QUERY HOW CAN MAKE DATABASE AND IT’S PATH SETTINGS
CREATE DATABASE dbOrient
/*
This query will automatically create .mdf and .ldf file and size is
provided by model database and size is depend on version of sql.
It’s Default Location Is In C Drive into MsSqlServer folder-> then
Go into MSSql->Data folder
*/
–how Can Check Properties of Databaase and it’s Path With Query
EXEC SP_HELPDB dbOrient
–Note: Database name cannot be exceed from 128 characters
/*
How can give different Path of Storage to Primary and Secondary File to your
database.
*/
CREATE DATABASE db_Orient
ON
(NAME =LOGICAL_DBORIENTMDF ,FILENAME=’D:\Test\DBORIENT.MDF’,SIZE=100MB,MAXSIZE=500,FILEGROWTH=10MB)
LOG ON
(NAME=LOGICAL_DBORIENTLDF,FILENAME=’D:\Test\DBORIENT.LDF’,SIZE=20MB,MAXSIZE=50,FILEGROWTH=10MB)
/*NOTEs:
By Default it take .ldf file size 25% or 512kb
Filegrowth Means Increase to your databae after 50mb in 10mb increment.
name if a logical name of your file of database
filename will get of path storage
*/
–how to drop your database
Drop Database db_Orient
Description About Query :- This Query will delete your database from ur DataConnection .
Before To execution of this query remember that database should not be in used.
—————————————
–1) How Can Create Multiple Files of Database.
create database dbemp99
on
(name=’dbemp11′,filename=’e:\test\dbemp99.mdf’,size=50,maxsize=100MB,filegrowth=10MB),
(name=’db3′,filename=’e:\test\dbemp09.ndf’,size=50,maxsize=100MB,filegrowth=10MB)
log on
(name=’dt’,filename=’e:\test\dblog01.ldf’,size=50,maxsize=80,filegrowth=10),
(name=’dt1′,filename=’e:\test\dblog02.ldf’,size=50,maxsize=80,filegrowth=10)
————————————
/*
Description About Query :- This query is creating at userdefined location databasefiles
for a single database.In This query Files has partioning of Primary and Secondary File
*/
–2) Use of Filegroups and What is A Filegroup in Database
Filegroups are named collections of files that allow a database
developer to place database objects into specific sets of files.
Filegroups can be used to improve performance, as well as aid in the
backup and recovery of large databases.
Database Storage Requirements
The size of your database will depend on two things:
what you store in it and how much that data is modified.
A large amount of space is used by the data files to store all objects in the database.
The transaction log is responsible for storing all modifications to data and
maintaining transactional integrity.
When you are creating a new database, it is important to choose the size of these files
appropriately, rather than relying on SQL Server to automatically grow the files as needed.
Database files can be grouped into filegroups for performance and administrative reasons.
Filegroups allow an administrator to control the placement of a database object,
such as a table or index, into a set of data files.
Filegroups also perform the following functions:
Allow I/O operations for heavily used tables and indexes to be focused
• on a specific disk or array.
• Enable individual database files to be backed up separately
from the rest of the database.
• Enable piecemeal restore, which lets you bring different pieces of the database online in phases during disaster recovery. Piecemeal restore will be covered in more depth in Chapter 9, “Data Recovery.”
• Separate read-only data from the rest of the database.
The primary filegroup exists in every database on SQL Server. The primary filegroup contains all data files that haven’t been specifically assigned to a user-defined filegroup, and it is the default filegroup for new database objects. If the primary filegroup becomes full, no other data files can expand, because the primary filegroup contains all of the system tables. For this reason, it is recommended that a user-defined filegroup be set as the default for creating new database objects. Here are some more hints for using filegroups:
• Most databases work well with a single data file and a single transaction log file. Make sure you have clear reasons for using additional filegroups in a database.
• When using multiple files, creating an additional filegroup and setting that filegroup to be the default will allow isolation of the system tables from the rest of the database objects. Make sure to size each file appropriately.
• Creating filegroups on as many disks and RAID arrays as possible will provide the best performance.
• Placing tables and indexes into separate filegroups on separate volumes can improve query performance.
–3) How Can Create Filegroups
create database dbemp99
on primary
(name=’d1′,filename=’e:\test\dbemp99.mdf’,size=100,maxsize=200,filegrowth=10),
(name=’d2′,filename=’e:\test\dbemp91.ndf’,size=100,maxsize=200,filegrowth=10),
filegroup grp1
(name=’dd1′,filename=’e:\test\dbemp92.ndf’,size=100,maxsize=200,filegrowth=10),
(name=’dd2′,filename=’e:\test\dbemp93.ndf’,size=100,maxsize=200,filegrowth=10),
filegroup grp2
(name=’ddd1′,filename=’e:\test\dbemp94.ndf’,size=100,maxsize=200,filegrowth=10),
(name=’ddd2′,filename=’e:\test\dbemp95.ndf’,size=100,maxsize=200,filegrowth=10)
log on
(name=’ddlog1′,filename=’e:\test\dbemplog.ldf’,size=100,maxsize=200,filegrowth=10)
–4) What is Sanpshot and It’s Advantage.
A database snapshot is a read-only, static view of a database.
Database snapshots were introduced in the Enterprise edition of SQL Server 2005.
A database snapshot can use considerably less space than a separate read-only copy of a
database. Database snapshots work by capturing changes as they are made to the source
database. Only the modified page values are stored in the files used for the database snapshot.
This  process is accomplished using sparse files and copy-on-write technology present in
the NTFS file system. As the source database’s pages are modified,
SQL Server saves the original data pages in a sparse file using a copy-on-write process.
This ensures that only changed database pages take up physical space on disk for the snapshot,
and this can considerably reduce the amount of storage used in comparison to a separate,
read-only copy of a database. The amount of storage needed can very significantly based on
the age of the snapshot and the volume of changed data in the source database.
Some common scenarios for using database snapshots include:
5) Maintaining historical data for
6) reporting purposes
7) Using a mirrored database created for availability purposes for reporting
8) Protecting data against administrative or user error
9) Managing a consistent test environment where changes can easily be rolled back
10) Database snapshots can introduce some important limitations on the source database as well as the snapshot itself.
11) Performance is reduced due to increased I/O from the copy-on-write operation that occurs
12) when data pages are modified.
13) The source database may not be dropped unless all snapshots are also dropped.
14) The database snapshot must reside on the same instance as the source database.
15) Database files must be stored on an NTFS partition.
Creating a Database Snapshot
In order to create a database snapshot, you must use the Transact-SQL
CREATE DATABASE…AS SNAPSHOT OF statement.
While you can view database snapshots in SQL Server Management Studio,
you cannot create snapshots from within the user interface;
you must use Transact-SQL. When creating a new snapshot,
you must specify a new path and filename for the logical name of every database file of
the source database.
First Make a Database Then His Snap Shot
create database dbbook
on
(name=’a1′, filename=’e:\test\dbbook.mdf’,size=100,maxsize=200,filegrowth=10)
create database snap55
on
(name=’a1′,filename=’e:\test\dbsnp.ss’)
as snapshot of dbbook
Description about Query :- This Snapshot will be in DatabaseSnapshot Folder of
Databaseengine
–how To delete snapshot
drop database snap55
drop database dbbook
Description About Query :- Snapshot Always composed with the help of sparse file.This Query will Make a Read Only view of Your database.

/*Hello Friends Today You will Learn How Can Make A New Database IN MANAGEMENT STUDIOWITH USE OF GRAPHICAL USER INTERFACE ANDWITH QUERY EDITORNOTE: THESE STEPS ARE FOR MSSQLSERVER2005 AND IN MSSQLSERVER2008 ARE SAME*//*STEP 1: ENSURE TO THIS IN YOUR PC MSSQLSERVER 2005 OR MSSQLSERVER2008 ARE COMPLETLY INSTALL ESSENTIALLY MSSTUDIONOW RUN YOU MSSTUDIO */
/*STEP2: NOW CONNECT TO DATABASE ENGINE(if not connected) AND RIGHT CLICK ON Databasesthen select new database option(double click on it) and make new database*/

–STEP 3: NOW WITH QUERY HOW CAN MAKE DATABASE AND IT’S PATH SETTINGS

CREATE DATABASE dbOrient

/*This query will automatically create .mdf and .ldf file and size is provided by model database and size is depend on version of sql.It’s Default Location Is In C Drive into MsSqlServer folder-> thenGo into MSSql->Data folder*/–how Can Check Properties of Databaase and it’s Path With Query

EXEC SP_HELPDB dbOrient–

Note: Database name cannot be exceed from 128 characters
/*How can give different Path of Storage to Primary and Secondary File to yourdatabase.

*/

CREATE DATABASE db_OrientON(NAME =LOGICAL_DBORIENTMDF ,FILENAME=’D:\Test\DBORIENT.MDF’,SIZE=100MB,MAXSIZE=500,FILEGROWTH=10MB)LOG ON(NAME=LOGICAL_DBORIENTLDF,FILENAME=’D:\Test\DBORIENT.LDF’,SIZE=20MB,MAXSIZE=50,FILEGROWTH=10MB)

/*NOTEs: By Default it take .ldf file size 25% or 512kbFilegrowth Means Increase to your databae after 50mb in 10mb increment.name if a logical name of your file of databasefilename will get of path storage*/

–how to drop your databaseDrop Database db_Orient–Description About Query :- This Query will delete your database from ur DataConnection .Before To execution of this query remember that database should not be in used.

———–1) How Can Create Multiple Files of Database.

create database dbemp99on(name=’dbemp11′,filename=’e:\test\dbemp99.mdf’,size=50,maxsize=100MB,filegrowth=10MB),(name=’db3′,filename=’e:\test\dbemp09.ndf’,size=50,maxsize=100MB,filegrowth=10MB)log on(name=’dt’,filename=’e:\test\dblog01.ldf’,size=50,maxsize=80,filegrowth=10),(name=’dt1′,filename=’e:\test\dblog02.ldf’,size=50,maxsize=80,filegrowth=10)————————————

/*Description About Query :- This query is creating at userdefined location databasefiles for a single database.In This query Files has partioning of Primary and Secondary File*/

–2) Use of Filegroups and What is A Filegroup in Database Filegroups are named collections of files that allow a database developer to place database objects into specific sets of files. Filegroups can be used to improve performance, as well as aid in the backup and recovery of large databases.
Database Storage RequirementsThe size of your database will depend on two things: what you store in it and how much that data is modified. A large amount of space is used by the data files to store all objects in the database. The transaction log is responsible for storing all modifications to data and maintaining transactional integrity. When you are creating a new database, it is important to choose the size of these files appropriately, rather than relying on SQL Server to automatically grow the files as needed. Database files can be grouped into filegroups for performance and administrative reasons. Filegroups allow an administrator to control the placement of a database object, such as a table or index, into a set of data files. Filegroups also perform the following functions:
Allow I/O operations for heavily used tables and indexes to be focused
• on a specific disk or array.• Enable individual database files to be backed up separately  from the rest of the database.• Enable piecemeal restore, which lets you bring different pieces of the database online in phases during disaster recovery. Piecemeal restore will be covered in more depth in Chapter 9, “Data Recovery.” • Separate read-only data from the rest of the database. The primary filegroup exists in every database on SQL Server. The primary filegroup contains all data files that haven’t been specifically assigned to a user-defined filegroup, and it is the default filegroup for new database objects. If the primary filegroup becomes full, no other data files can expand, because the primary filegroup contains all of the system tables. For this reason, it is recommended that a user-defined filegroup be set as the default for creating new database objects. Here are some more hints for using filegroups: • Most databases work well with a single data file and a single transaction log file. Make sure you have clear reasons for using additional filegroups in a database. • When using multiple files, creating an additional filegroup and setting that filegroup to be the default will allow isolation of the system tables from the rest of the database objects. Make sure to size each file appropriately. • Creating filegroups on as many disks and RAID arrays as possible will provide the best performance. • Placing tables and indexes into separate filegroups on separate volumes can improve query performance.
–3) How Can Create Filegroups

create database dbemp99on primary(name=’d1′,filename=’e:\test\dbemp99.mdf’,size=100,maxsize=200,filegrowth=10),(name=’d2′,filename=’e:\test\dbemp91.ndf’,size=100,maxsize=200,filegrowth=10),filegroup grp1

(name=’dd1′,filename=’e:\test\dbemp92.ndf’,size=100,maxsize=200,filegrowth=10),(name=’dd2′,filename=’e:\test\dbemp93.ndf’,size=100,maxsize=200,filegrowth=10),filegroup grp2

(name=’ddd1′,filename=’e:\test\dbemp94.ndf’,size=100,maxsize=200,filegrowth=10),(name=’ddd2′,filename=’e:\test\dbemp95.ndf’,size=100,maxsize=200,filegrowth=10)log on (name=’ddlog1′,filename=’e:\test\dbemplog.ldf’,size=100,maxsize=200,filegrowth=10)
–4) What is Sanpshot and It’s Advantage.

A database snapshot is a read-only, static view of a database.Database snapshots were introduced in the Enterprise edition of SQL Server 2005.A database snapshot can use considerably less space than a separate read-only copy of adatabase. Database snapshots work by capturing changes as they are made to the source database. Only the modified page values are stored in the files used for the database snapshot. This  process is accomplished using sparse files and copy-on-write technology present in the NTFS file system. As the source database’s pages are modified, SQL Server saves the original data pages in a sparse file using a copy-on-write process. This ensures that only changed database pages take up physical space on disk for the snapshot, and this can considerably reduce the amount of storage used in comparison to a separate, read-only copy of a database. The amount of storage needed can very significantly based on the age of the snapshot and the volume of changed data in the source database.
Some common scenarios for using database snapshots include:5) Maintaining historical data for

6) reporting purposes

7) Using a mirrored database created for availability purposes for reporting

8) Protecting data against administrative or user error

9) Managing a consistent test environment where changes can easily be rolled back

10) Database snapshots can introduce some important limitations on the source database as well as the snapshot itself.

11) Performance is reduced due to increased I/O from the copy-on-write operation that occurs
12) when data pages are modified.

13) The source database may not be dropped unless all snapshots are also dropped.

14) The database snapshot must reside on the same instance as the source database.

15) Database files must be stored on an NTFS partition.Creating a Database SnapshotIn order to create a database snapshot, you must use the Transact-SQL CREATE DATABASE…AS SNAPSHOT OF statement. While you can view database snapshots in SQL Server Management Studio, you cannot create snapshots from within the user interface; you must use Transact-SQL. When creating a new snapshot, you must specify a new path and filename for the logical name of every database file of the source database.
First Make a Database Then His Snap Shot

create database dbbookon(name=’a1′, filename=’e:\test\dbbook.mdf’,size=100,maxsize=200,filegrowth=10)create database snap55on(name=’a1′,filename=’e:\test\dbsnp.ss’)

as snapshot of dbbook

Description about Query :- This Snapshot will be in DatabaseSnapshot Folder of Databaseengine

–how To delete snapshotdrop database snap55

drop database dbbook

Description About Query :- Snapshot Always composed with the help of sparse file.This Query will Make a Read Only view of Your database.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s