Scripts which make you Database Hero

-- Create databsae SQLDBPool
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N'sqldbpool', FILENAME = N'C:\sqldbpool.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'sqldbpool_log', FILENAME = N'C:\sqldbpool_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

--Script to create schema
USE [sqldbpool]
GO
CREATE SCHEMA [mySQLDBPool] AUTHORIZATION [dbo]

-- Script to create table with constraints
create table mySQLDBPool.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 [SQLDBPool] SET RECOVERY FULL WITH NO_WAIT
GO

ALTER DATABASE [SQLDBPool] SET RECOVERY FULL
GO

-- Script to take the full backup of database
BACKUP DATABASE [SQLDBPool] TO DISK = N'D:\SQLDBPool.bak'
WITH NOFORMAT, INIT, NAME = N'SQLDBPool-Full Database Backup',
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

--Script to take the Differential Database backup
BACKUP DATABASE [SQLDBPool] TO DISK = N'D:\SQLDBPool.diff.bak'
WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N'SQLDBPool-Diff Backup',
NOREWIND, SKIP, NOUNLOAD, STATS = 10
GO

--Script to take the Transaction Log backup that truncates the log
BACKUP LOG [SQLDBPool] TO DISK = N'D:\SQLDBPoolTlog.trn'
WITH NOFORMAT, INIT, NAME = N'SQLDBPool-Transaction Log Backup',SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

-- Backup the tail of the log (not normal procedure)
BACKUP LOG [SQLDBPool] TO DISK = N'D:\SQLDBPoolLog.tailLog.trn'
WITH NO_TRUNCATE , NOFORMAT, INIT, NAME = N'SQLDBPool-Transaction Log Backup',NOREWIND,SKIP, NOUNLOAD, NORECOVERY , STATS = 10
GO

-- Script to Get the backup file properties
RESTORE FILELISTONLY FROM DISK = 'D:\SQLDBPool.bak'

-- Script to Restore Full Database Backup
RESTORE DATABASE [SQLDBPool1] FROM DISK = N'D:\SQLDBPool.bak'
WITH FILE = 1, MOVE N'sqldbpool' TO N'D:\SQLDBPooldata.mdf',
MOVE N'sqldbpool_log' TO N'D:\SQLDBPoollog_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'SQLDBPool1'
GO

-- Full restore with no recovery (status will be Restoring)
RESTORE DATABASE [SQLDBPool1] FROM DISK = N'D:\SQLDBPool.bak'
WITH FILE = 1, MOVE N'SQLDBPool' TO N'D:\SQLDBPooldata.mdf',
MOVE N'SQLDBPool_Log' TO N'D:\SQLDBPoolLog_1.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO

-- Restore transaction log with recovery
RESTORE LOG [SQLDBPool1] FROM DISK = N'D:\SQLDBPoolLog.trn'
WITH FILE = 1, NOUNLOAD, RECOVERY STATS = 10
GO

--Script to bring the database online without restoring log backup
restore database sqldbpool with recovery

--Script to detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLDBPool'
GO

-- Script to get the database information
sp_helpdb 'SQLDBPOOL'

--to Attach database
USE [master]
GO

CREATE DATABASE [SQLDBPool1] ON
( FILENAME = N'C:\SQLDBPool.mdf' ),
( FILENAME = N'C:\SQLDBPool_Log.ldf' )
FOR ATTACH
GO

USE SQLDBPool
GO

-- Get Fragmentation info for each non heap table in SQLDBPool 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('SQLDBPool'), 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'SQLDBPool'), OBJECT_ID(N'mySQLDBPool.Emp'), NULL, NULL , 'LIMITED');

--script to get the index information
exec sp_helpindex [mySQLDBPool.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('mySQLDBPool.Emp')
AND sc.column_id IS NULL
ORDER BY c.column_id

-- Create Statistics on DOB column
CREATE STATISTICS st_BirthDate
ON mySQLDBPool.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 mySQLDBPool.Emp
WITH FULLSCAN


-- Script to shrink database
DBCC SHRINKDATABASE(N'SQLDBPool' )
GO

-- Shrink data file (truncate only)
DBCC SHRINKFILE (N'SQLDBPool_Data' , 0, TRUNCATEONLY)
GO

-- Script to shrink Shrink data file - Very Slow and Enhances the fragmentation
DBCC SHRINKFILE (N'SQLDBPool_Data' , 10)
GO
-- Script Shrink transaction log file
DBCC SHRINKFILE (N'SQLDBPool_Log' , 0, TRUNCATEONLY)
GO

-- Script to create view
CREATE VIEW emp_view
AS
SELECT *
FROM mySQLDBPool.emp