DATABASE BACKUPS

DATABASE BACKUPS

FULL BACKUP:

BACKUP DATABASE [sales] TO  DISK =

'C:\backup\sales_200906291032.bak' with noinit, stats=10

[noinit= append to the existing backkup set & stats=10 while processing the backup it shows the percentage of the backup increment]

TRANSACTION LOG BACKUP:

BACKUP LOG [salesdetails] to  DISK =

'C:\backup\sales_200906291044.trn' with noinit, stats=10

RESTORING FULL BACKUP:

RESTORE DATABASE [EMP] FROM  DISK = N'C:\BACKUP\empfull.bak' WITH  FILE = 1, STANDBY ='C:\Program Files...',  REPLACE,  STATS = 10 [REPLACE MEANS OVERWIRTE THE EXSISTING DATABASE]

RESTORING LOG BACKUP:

RESTORE LOG[EMP] FROM  DISK ='C:\BACKUP\emp.TRN' WITH  FILE = 1,REPLACE,  STATS = 10

How to change the recovery model from full to bulk-logged

USE [master]

GO

ALTER DATABASE [EMP] SET RECOVERY BULK_LOGGED WITH NO_WAIT

GO

DROPE DATABASE:

USE [master]

GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'EMP')

DROP DATABASE [EMP]

HELP USER:

EXEC sp_helpuser (this will give list of all users In a database)

LIST ALL DATABASES:

sp_databases

LIST OF ALL TABLES IN THE CURRENT DATABASE:

EXEC sp_tables

CHECK CONSTRAINTS FOR TABLES:

This example shows all constraints for the authors table.

USE pubs

EXEC sp_helpconstraint authors

ADD SERVER:

sp_addserver 'ACCOUNTS'

sp_spaceused:

List out space allocation of a database

Sp_spaceused tablename:

List out space allocation of a table

Script for creating login with role membership:

Use master

go

create login basker with password= 'basker', default_database=[master], check_expiration= off, check_policy= off

go

use pubs

go

create user basker for login basker

go

use pubs

go

exec sp_addrolemember 'db_ddladmin', 'basker'

go

 CHECK DATABASE INTEGRITY:

The Check Database Integrity task performs internal consistency checks of the data and index pages within the database.

REORGANISE INDEX:

The Reorganize Index task defragments and compacts clustered and non-clustered indexes on tables and views. This will improve index-scanning performance.

REBUILD INDEX:

The Reorganize Index task defragments and compacts clustered and non-clustered indexes on tables and views. This will improve index-scanning performance.

UPDATED STATATICS:

The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.