Restoring Differential backup to database

To restore a differential backup, you must first restore a full backup while ensuring that the databases is NOT recovered. The most recent differential backup is then applied to the database.

RESTORE DATABASE PUBS FROM DISK='C:\DEMO\BACKUP\PUBSFULL.BAK' WITH NORECOVERY

RESTORE DATABASE PUBS FROM DISK='C:\DEMO\BACKUP\PUBSDIFF.BAK' RECOVERY

The first command restore Full backup, leaving the database unrecovered. The second command applies a differential backup and then recovers from database.

List all unused indexes

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName
FROM sys.indexes I
WHERE -- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- limit our query only for the current db
AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName

Find Size all Database with Path

Find the Size of Ldf File
Total Size of All LDF File
SELECT sum(size*(8192/(1024.*1024)))/1024 FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =2
Total Size of Each LDF Files
SELECT NAME,((size*(8192/(1024.*1024))) /1024)*100, FILENAME FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =2
Find the Size of MDFFile
Total Size of All MDF File
SELECT sum(size*(8192/(1024.*1024)))/1024 FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =1
Total Size of Each MDF Files
SELECT NAME,((size*(8192/(1024.*1024))) /1024)*100, FILENAME FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =1

Create a User AND assign Permision

--Create a User
--Set DatabaseName as the current database
Use DatabaseName
Go
--Changed database context to 'DatabaseName'.
---- Create a new server login name: Amit
CREATE LOGIN Amit WITH PASSWORD=’password’
Go
-- Create a new database user linked to the login name
CREATE USER Amit FOR LOGIN Amit;
-- Grant database ALTER permision to the user
GRANT ALTER To Amit;
Go
-- Grant database CONTROL permision to the user
GRANT CONTROL To Amit;
Go

Grant EXECUTE Permissions on all Stored Procedures to a Single User

-- I've updated this with Aaron Bertrand's suggestions from the comments.
-- Thanks to Aaron for helping make this better!
DECLARE @sql AS NVARCHAR(MAX);
DECLARE @newline AS NVARCHAR(2);
DECLARE @user_name AS NVARCHAR(100);
DECLARE @sproc_name_pattern AS NVARCHAR(10);
SET @sql = N''
SET @newline = NCHAR(13) + NCHAR(10);
SET @user_name = N'jeremiah';
-- escaping _ prevents it from matching any single character
-- including the wildcard makes this much more portable between DBs
SET @sproc_name_pattern = N'sproc[_]%';
-- using QUOTENAME will properly escape any object names with spaces
-- or other funky characters
SELECT @sql = @sql
+ N'GRANT EXECUTE ON '
+ QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.'
+ QUOTENAME([name])
+ N' TO '
+ QUOTENAME(@user_name)
+ N';'
+ @newline
+ @newline
FROM sys.procedures
WHERE [name] LIKE @sproc_name_pattern;
-- this is my version of debug code, I usually run it once with the PRINT intact
-- before I actually use sp_executesql
--PRINT @sql;
EXEC sp_executesql @sql;

Reindex all database

declare objcur cursor for select name from sys.objects where type = 'u' order by name
declare @obj sysname
open objcur
fetch next from objcur into @obj
while @@fetch_status = 0begin print @obj dbcc dbreindex( @obj )
fetch next from objcur into @obj
end
deallocate objcur