SP to find the Drive Space,free and percentage Using T-SQL

use master

go

CREATE PROCEDURE sp_diskspace

AS

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20)

DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize=@TotalSize/@MB

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,

FreeSpace as 'Free(MB)',

TotalSize as 'Total(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'

FROM #drives

ORDER BY drive

DROP TABLE #drives

RETURN

go

You will find output like

Grant Execute Permission on all SP for a database

Following method can be used to create statement to grant Execute permission on all SP for a Database.

Use DatabaseName

select 'Grant Execute on [' + name + '] to [LoginName]'

from sysobjects where xtype in ('P')

Create a user/assign role for a Single Database

We DBA have a great responsibility to avoid access to unauthorized access to users. So we can give access to users for Required Databases only. Following are the method to create/assign used for a single DB.

USE [DatabaseName]

GO

CREATE USER [LoginName] FOR LOGIN [LoginName]

GO

USE [DatabaseName]

GO

ALTER USER [LoginName] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [DatabaseName]

GO

EXEC sp_addrolemember N'db_owner', N'LoginName'

GO

Rename Database from T-SQL

Rename Database

Sometime we rename database as per business Logic.

USE master

ALTER DATABASE DatabaseName

SET single_user

GO

ALTER DATABASE DatabaseName

MODIFY NAME = NewDatabaseName

GO

USE master

ALTER DATABASE NewDatabaseName

SET multi_user

GO

Backup & Restore from TSQL

Database Backup and Restore from TSQL

For Native Backup

Database MYDatabase has full backup DatabaseName.bak. The database can be restored using following two steps.


Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY

FROM DISK = 'D:\Backup\DatabaseName.bak'

GO

Step 2: Use the values in the LogicalName Column in following Step.
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE MYDatabase
FROM DISK = 'D:\Backup\DatabaseName.bak'
WITH MOVE 'MDF_LogicalName' TO 'D:\MSSQL\Data\DBFileName_Data.mdf',
MOVE 'LDF_LogicalName' TO 'D:\MSSQL\Log\DBFileName_Log.ldf'

ALTER DATABASE YourDB SET MULTI_USER
GO

To backup database

Backup database Database_Name to Disk='D:\Backup\DatabaseName.bak' with stats=10
===============================================================
For LiteSpeed

--To Check the logical name of the file

exec master.dbo.xp_restore_filelistonly @filename = 'path of the database backup.bak'

i.e logical name like- Database_Data, Database_Log

--To Find the path where mdf and ndf resides for the said database.

--select * from sysaltfiles

sp_helpdb 'DatabaseName'

--To Restore database from given backup file

exec master.dbo.xp_restore_database @database = N'DatabaseName',

@filename = N'path of the database backup.bak',

@with = N'RECOVERY', @with = N'NOUNLOAD', @with = N'STATS = 10',

@with = N'MOVE N'' Database_Data '' TO N''N:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DatabaseName.mdf''',

@with = N'MOVE N'' Database_Log '' TO N''K:\Microsoft SQL Server\MSSQL\Logs\ DatabaseName.ldf'''

===============================================================
For SQL Safe

Backup Database

exec master..xp_ss_backup

@database = 'DatabaseName', @filename = 'D:\Backup\DatabaseName_FUll_Date.safe', @init = 1
, @compressionlevel = 'isize', @threads = 3

To verify backup file
xp_ss_listfiles @filename='D:\DB_Backup\DatabaseName_FUll_Date.safe'

Restore Database

exec master..xp_ss_restore
@database = 'Database_Name',
@filename='D:\DB_Backup\DatabaseName_FUll_Date.safe',
@withmove = 'DB_MDF_LOgicalName D:\MS SQL Server\Data\DBName.mdf'
,@withmove = 'DB_LDF_logicalName_Log F:\MS SQL Server\Logs\DBName.ldf'
,@withmove = 'DB_LDF_logicalName_Log1 F:\MS SQL Server\Logs\DBName1.ldf'
,@withmove = 'DB_Catalog_FT E:\MS SQL Server\FText\DB_Catalog_Production_FT'
,@replace = 1

==================================================================
Below are method to find orphan User and Way to fix it
--To Verify User SID matches system SID

sp_change_users_login 'report'

--If you find any Orphen user with SYSTEM SID, then we can fix with Following Command

sp_change_users_login 'update_one','username','username'

==================================================================

Query to find out Estimated Time, Percentage, Elapsed time of a RESTORE DATABASE/BACKUP DATABASE

SELECT r.session_id ,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]

,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2

,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r

WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')