Creating a database

1>USE master

2>go

3>disk init name='phil_data', physname='E:\newdb\phil.dat', vdevno=45, size=10240

4>go

Device to host the log file

1>disk init name='phil_log', physname='E:\newdb\phil_log.dat', vdevno=4, size=5120

2>go

Then we create the database.

create database phil on phil_data='10m' log on phil_log='5m'

Note : To load a dump on a new database use the "for load" option while creating database

Example for creating a database for load operation

1>create database sam on phil_data='10m' log on phil_log='5m' for load

2>go

1>load database sam from '/path/to/bak file'

2>go

If required transaction log could be applied

1> load tran sam from '/path/to/trn file'

2> go

User online database to make the database accesible

1>online database sam

2> go

Adding User to a database

This will add login to the Server

1>sp_addlogin <username>,<password>

2>go

After that modify the default database

1>sp_modifylogin <username>,defdb,'<user_db>'

2>go

Add the user to required user database

1>use <user database>

2>go

1>sp_adduser "<username>",<display_name>,null

2>go

Grant permission to the user so that he could operate on the database

1>use <user_database>

2>go

1>grant select,insert,update,delete to <table_object> to <username>

2>go

To increase Database Size.

To Increase the size of the devices

1>use master

2>go

3>disk resize name='dev1', size ='100M'

4>go

Above increases the capacity of the device, now to increase the size of the database

1>alter database <database_name>

2>go

Above will increase the database size using the default device

To use the specific device use the following

1>alter database <database_name> on dev2='200M'

2>go

One more example

1> alter database <database_name> on dev='200M' dev2='100M' log on tdev2='100M'

2>go