Create Backup File with Password protection

Sometime it is necessary to set password in backup file. If backup is password protected than we cannot restore the database or see details without knowing the password. Now to add password protection we have to add "With MediaPassword='DBPassword' " at the end of TSQL where 'DBPassword' is password we set for the backup. Now during restore we have to add this at the end also.


TSQL to backup database with password
Backup Database DemoDB To Disk ='C:\Temp\DemoDB.BAK' With MediaPassword='DBPassword'  
GO


Now we cannot restore this database or get details of database from this backup without knowing the password.
We will get the below error:



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

Now to restore the database run below command:

Restore Database DemoData From Disk='C:\Temp\DemoDB.BAK' With MEDIAPASSWORD='DBPassword'  

To resore details of the backup run below command

Restore HeaderOnly From Disk='C:\Temp\DemoDB.BAK' With MEDIAPASSWORD='DBPassword'  

Change Recovery Model of all your SQL Server databases

In the below script, we use a system table to get all the database names on our server. Then we use a cursor to loop through all the records and then change the recovery model.


--Declaration of variables
DECLARE @DBName SysName, @sql VarChar(100)
-- Declare begin cursor to get the database names and get info from sys.databases catalog
DECLARE Cursor_db Cursor FOR SELECT Name From sys.Databases WHERE Name != 'TempDB'
-- Now using the cursor to loop through database names and change recovery model
OPEN Cursor_db Fetch NEXT FOM Cursor_db INTO @DBName 
--While Loop with Alter database command
WHILE @@fetch_status = 0
BEGIN--print 'database is ' + @DBName 
SET @sql='ALTER Database ' + @DBName + ' SET Recovery Simple'
PRINT 'sql is ' + @sql
exec (@sql)

Fetch Next FROM Cursor_db INTO @DBName 
END--clean up objects
CLOSE Cursor_db 
Deallocate Cursor_db 

SQL Server 2008 Database Mirroring

Check below videos for Database Mirroring

Improve SQL Server performance using profiler and tuning advisor

These videos shows a simple explanation of what is a SQL server profiler and performance tuning wizard.

Error: Cannot find the template file for the new query

Today morning I got this error in my computer when I was opening new Query Editor Window:

The SQLFile.sql is a template file for new query. This was accidentally removed from my system. This is a blank file. To solve this error I had created this file in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql Folder.