Database Administration Scripts

 – Create databsae TEST

 CREATE DATABASE [TEST] ON PRIMARY
 ( NAME = N’TEST’, FILENAME = N’C:\TEST.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
 ( NAME = N’TEST_log’, FILENAME = N’C:\TEST_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
 GO

–Script to create schema

 USE [TEST]
 GO
 CREATE SCHEMA [myTEST] AUTHORIZATION [dbo]

– Script to create table with constraints

 create table myTEST.Emp
 (
 EmpID int Primary key identity(100,1),
 EmpName Varchar(20) Constraint UK1 Unique,
 DOB datetime Not Null,
 JoinDate datetime default getdate(),
 Age int Constraint Ck1 Check (Age > 18)
 )

– Script to change the recovery model of the databsae

 USE [master]
 GO
 ALTER DATABASE [TEST] SET RECOVERY FULL WITH NO_WAIT
 GO

ALTER DATABASE [TEST] SET RECOVERY FULL
 GO

– Script to take the full backup of database

 BACKUP DATABASE [TEST] TO DISK = N’D:\TEST.bak’
 WITH NOFORMAT, INIT, NAME = N’TEST-Full Database Backup’,
 NOREWIND, SKIP, NOUNLOAD, STATS = 10
 GO

–Script to take the Differential Database backup

 BACKUP DATABASE [TEST] TO DISK = N’D:\TEST.diff.bak’
 WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N’TEST-Diff Backup’,
 NOREWIND, SKIP, NOUNLOAD, STATS = 10
 GO

–Script to take the Transaction Log backup that truncates the log

 BACKUP LOG [TEST] TO DISK = N’D:\TESTTlog.trn’
 WITH NOFORMAT, INIT, NAME = N’TEST-Transaction Log Backup’,SKIP, NOREWIND, NOUNLOAD, STATS = 10
 GO

– Backup the tail of the log (not normal procedure)

 BACKUP LOG [TEST] TO DISK = N’D:\TESTLog.tailLog.trn’
 WITH NO_TRUNCATE , NOFORMAT, INIT, NAME = N’TEST-Transaction Log Backup’,NOREWIND,SKIP, NOUNLOAD, NORECOVERY , STATS = 10
 GO

– Script to Get the backup file properties

 RESTORE FILELISTONLY FROM DISK = ‘D:\TEST.bak’

– Script to Restore Full Database Backup

 RESTORE DATABASE [TEST1] FROM DISK = N’D:\TEST.bak’
 WITH FILE = 1, MOVE N’TEST’ TO N’D:\TESTdata.mdf’,
 MOVE N’TEST_log’ TO N’D:\TESTlog_1.ldf’,
 NOUNLOAD, STATS = 10
 GO

– Script to delete the backup history of the specific databsae

 EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’TEST1′
 GO

– Full restore with no recovery (status will be Restoring)

 RESTORE DATABASE [TEST1] FROM DISK = N’D:\TEST.bak’
 WITH FILE = 1, MOVE N’TEST’ TO N’D:\TESTdata.mdf’,
 MOVE N’TEST_Log’ TO N’D:\TESTLog_1.ldf’,
 NORECOVERY, NOUNLOAD, STATS = 10
 GO

– Restore transaction log with recovery

 RESTORE LOG [TEST1] FROM DISK = N’D:\TESTLog.trn’
 WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10
 GO

–Script to bring the database online without restoring log backup

 restore database TEST with recovery

–Script to detach database

 USE [master]
 GO
 EXEC master.dbo.sp_detach_db @dbname = N’TEST’
 GO

– Script to get the database information

 sp_helpdb ‘TEST’

–to Attach database

 USE [master]
 GO

CREATE DATABASE [TEST1] ON
 ( FILENAME = N’C:\TEST.mdf’ ),
 ( FILENAME = N’C:\TEST_Log.ldf’ )
 FOR ATTACH
 GO

USE TEST
 GO

– Get Fragmentation info for each non heap table in TEST database
 – Avg frag.in percent is External Fragmentation (above 10% is bad)
 – Avg page space used in percent is Internal Fragmention (below 75% is bad)

SELECT OBJECT_NAME(dt.object_id) AS ‘Table Name’ , si.name AS ‘Index Name’,
 dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
 FROM
 (SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
 FROM sys.dm_db_index_physical_stats (DB_ID(‘TEST’), NULL, NULL, NULL, ‘DETAILED’)
 WHERE index_id <> 0) AS dt
 INNER JOIN sys.indexes AS si
 ON si.object_id = dt.object_id
 AND si.index_id = dt.index_id
 ORDER BY OBJECT_NAME(dt.object_id)

– Script to Get Fragmention information for a single table

 SELECT TableName = object_name(object_id), database_id, index_id, index_type_desc, avg_fragmentation_in_percent, fragment_count, page_count
 FROM sys.dm_db_index_physical_stats (DB_ID(N’TEST’), OBJECT_ID(N’myTEST.Emp’), NULL, NULL , ‘LIMITED’);

–script to get the index information

 exec sp_helpindex [myTEST.Emp]

–Script to Reorganize an index

 ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
 REORGANIZE
 GO

– Rebuild an index (offline mode)

 ALTER INDEX ALL ON Production.Product
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);

–Script to find which columns don’t have statistics

 SELECT c.name AS ‘Column Name’
 FROM sys.columns AS c
 LEFT OUTER JOIN sys.stats_columns AS sc
 ON sc.[object_id] = c.[object_id]
 AND sc.column_id = c.column_id
 WHERE c.[object_id] = OBJECT_ID(‘myTEST.Emp’)
 AND sc.column_id IS NULL
 ORDER BY c.column_id

– Create Statistics on DOB column

 CREATE STATISTICS st_BirthDate
 ON myTEST.Emp(DOB)
 WITH FULLSCAN

– When were statistics on indexes last updated

 SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id)
 FROM sys.objects AS o WITH (NOLOCK)
 JOIN sys.indexes AS i WITH (NOLOCK)
 ON o.name = 'Emp'
 AND o.object_id = i.object_id
 ORDER BY STATS_DATE(i.object_id, i.index_id);

– Update statistics on all indexes in the table

 UPDATE STATISTICS myTEST.Emp
 WITH FULLSCAN

– Script to shrink database

 DBCC SHRINKDATABASE(N’TEST’ )
 GO

– Shrink data file (truncate only)

 DBCC SHRINKFILE (N’TEST_Data’ , 0, TRUNCATEONLY)
 GO

– Script to shrink Shrink data file – Very Slow and Enhances the fragmentation

 DBCC SHRINKFILE (N’TEST_Data’ , 10)
 GO
 – Script Shrink transaction log file

 DBCC SHRINKFILE (N’TEST_Log’ , 0, TRUNCATEONLY)
 GO

– Script to create view

 CREATE VIEW emp_view
 AS
 SELECT *
 FROM myTEST.emp