T-SQL Query to get the list of files in a folder

Here is a T-SQL Query to list all the files in a folder. This uses a undocumented extended stored procedure to get the details.

DECLARE @Path nvarchar(500) = 'E:\Test' --Change the path

DECLARE @FindFile TABLE 
 (FileNames nvarchar(500)
  ,depth int
  ,isFile int)

INSERT INTO @FindFile 
EXEC xp_DirTree @Path,1,1

SELECT FileNames from @FindFile where isFile=1

T-SQL Query to find Index size of all tables

This query gives results in 2 parts by making use of a undocumented stored procedure sp_MSIndexSpace

1. The size of each individual index of a table
2. The total size of index on a table

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name='TempIndexSpace')
BEGIN
DROP TABLE tempdb..TempIndexSpace
END
CREATE TABLE tempdb..TempIndexSpace 
(ObjectName nvarchar(100)
 ,IndexID int
 ,IndexName nvarchar(100)
 ,[IndexSize(KB)] int
 ,Comments nvarchar(max))
exec sp_msforeachtable 'INSERT INTO tempdb..TempIndexSpace (IndexID,IndexName,[IndexSize(KB)],Comments) EXEC sp_MSIndexSpace [?];
UPDATE tempdb..TempIndexSpace SET ObjectName=''?'' WHERE ObjectName IS NULL'

-- This gives output per index
SELECT * FROM tempdb..TempIndexSpace

-- This gives output per table
SELECT  ObjectName,SUM([IndexSize(KB)]) AS [Index Size (KB)] FROM tempdb..TempIndexSpace
GROUP BY ObjectName

SQL Server IO - Continued...

Now that we understand what is "Transaction" and "ACID properties" of transaction... lets get moving on the next set...Write Ahead Log (WAL) :O

What!?... How is WAL related to ACID properties or transaction? Yes, they are related.

WAL is technique which helps to adhere to two of the four ACID properties "Atomicity" and "Durability". Now that we understand what is atomicity and durability... next is to understand "How WAL works" and also we need to peep into the advantages we get from it.

Let me just put my words as per SQL Books online which is very much easier to understand:
Write-ahead log (WAL) guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

To understand how the write-ahead log works, it is important for us to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification needs to be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

At the time when modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

I hope the above is very much clear in its explanation...

And this is it for now...

SQL Server IO

I had the opportunity to look back into the basics of SQL Server IO. To start off... first was to understand "What is Transaction" and then "Transaction properties".

What is Transaction?
There can be many ways of defining the same... however, the most simple could be as follows:
A transaction is a logical unit of work in which, all the steps must be performed or none.

Once we understand "what is transaction"... next is to understand "the properties" exhibited by transaction.
There are FOUR important properties of a transaction:
1. Atomicity
2. Consistency
3. Isolation and then
4. Durability

ATOMICITY - Any... for that matter any database modifications must follow an “all or nothing” rule... which means: If one part of the transaction fails, the complete transaction fails. Each transaction is said to be “ATOMIC”. It is critical that SQL Server maintains the atomic nature of transactions... not only SQL Server any DBMS, operating system or hardware failure.

CONSISTENCY - States that only valid data will be written to the database. In simple words if the database was consistent before the execution of the transaction then it should remain consistent after the complete execution of that transaction.

For some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.

On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

ISOLATION - First, let me put it in straight words: The transaction should not be interfered by any other transaction executing concurrently.

Now that means - multiple transactions occurring at the same time not impact each other’s execution. For example, if User A issues a transaction against a database at the same time that User B issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform User A's entire transaction before executing User B's or vice-versa.

This helps in preventing User A's transaction from reading intermediate data due to User B's transaction that will not eventually be committed to the database.

We  always get into confusion w.r.t the way we describe isolation. We should alyways remember that isolation property does not ensure which transaction will execute first, it is just that transactions will not interfere with each other.

DURABILITY - means any changes made by the transaction should be permanently committed in the database. This ensures that any transaction committed to the database will not be lost in spite of any subsequent software or hardware failures.

To ensure Durability is at its best we make use of database backups and transaction logs that facilitate the restoration of committed transactions.

I hope the above helps us to understand ACID properties in a simple way! 

Password Protect a backup file

Password protection of databases backups helps a lot in protecting the database backup from misuse.
Once such case is, when you are sending a backup of database physically through disks which has very sensitive and critical data to a different office or data center.

To password protect backup file, you have include WITH PASSWORD option when backing up the database

-- Full Backup with password
BACKUP DATABASE SansSQL TO DISK = 'D:\Backup\SansSQL_FullBackup.bak' 
WITH PASSWORD = 'Password123'

Now, once the backup is taken with a password, the same password has to be provided while restoring for matching the decryption sequence.

--Restore Full backup with no recovery
RESTORE DATABASE SansSQL FROM DISK = 'D:\Backup\SansSQL_FullBackup.bak' 
WITH RECOVERY, PASSWORD='Password123'

Question 1: What happens if you give a wrong password or try to restore without giving password?
Answer: The restoration will fail with the below error

Msg 3279, Level 16, State 2, Line 1  Access is denied due to a password failure  Msg 3013, Level 16, State 1, Line 1  RESTORE DATABASE is terminating abnormally.

Question 2: How will you come to know if a backup is password protected?
Answer: When you try to restore, it will give an error saying "Access is denied due to a password failure"
And when you try to execute RESTORE HEADERONLY, the file name will be shown as "*** PASSWORD PROTECTED ***"