SQL Server

Important commands to manage DataBase

• To Create database:

    create database DB1;

---

• To delete database:

    drop database DB1;

---

• If statments in Sql Server are allowed:

if not exists (select * from sys.databases where name = 'DB1')
begin
    create database 'DB1';
end

if exists (select * from sys.databases where = 'DB1')
begin
    drop database 'DB1';
end

---

• Switching between databases:
  we can switch between database by writing this statment in the beginnig of the query.\

use DB1

---

• Creating a table:

  create table db_table1 (
    column1 data_type null,
    column2 data_type not null,
    column3 data_type not null,
    ......
  );

  create table Persons(
    ID int not null,
    Name nvarchar(50) not null,
    Phone varchar(30) null,
    Salary smallmoney null,
    primary key(ID)
  );

---

• Delete table from database:

    use DB1
    drop table Persons;

---

• Add column in a table:

use DB1
alter table Persons
add Gender char(1);

---

• Rename column in a table:

alter table Persons
rename column Gender to Sex;

Note: it's not allowed in sql server, so we can use stored procedures instead:
exec sp_rename 'Persons.Gender', 'Sex', 'COLUMN';

---

• To Rename a table in sql:

alter table OldTableName
rename to New TableName;

In SQL-Server:
exec sp_rename 'Persons', 'People';

---

• Modify Column in a Table:

alter table Persons
alter column Name varchar(100);

---

• Delete column in a Table:

alter table Persons
drop column Gender;

---

Backup & Restore Database:

• To backup database completely:

backup database DB1
to disk = 'D:\DB1.bak'

in this case the database will make a backup for all files.

• To backup a database and you just want save changes:

backup database DB1
to disk = 'D:\DB1.bak'
with differential

---

• To restore a database:

restore database DB1
from disk = 'D:\DB1.bak'