September 2011 | SansSQL

Tuesday, September 27, 2011

Backup Database to multiple locations simultaneously - Mirror Backups

Database backup is one the regular activity a DBA would perform. Some times you might come across a situation where in you need to backup the database to different location. When I say backup database to different locations, it means that a copy of backup file needs to be placed on a different location as well and this is different from the Split Backups.
This is can be achieved by different methods,
  1. Take backup and then copy to multiple location
  2. Take backup of the same database multiple times pointing to different locations
  3. Use "MIRROR TO" Option in the Backup command 
Using the option "MIRROR TO" is very simple, you just need to mention "MIRROR TO" and "WITH FORMAT" options in the normal BACKUP DATABASE Statement and you are done. The backup database statement with these two options will take the backup of the same database to multiple locations at the same time.
This option "MIRROR TO" is introduced in SQL Server 2005 and this works only in SQL Server 2005 Enterprise Edition and later versions.
This can be used for all backup types and the Maximum number of "MIRROR TO" clauses that you can specify is three.
Example
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks_Full.bak'
MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Full.bak'
WITH STATS=10, FORMAT

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks_Differential.bak'
MIRROR TO DISK = 'C:\Mirror\AdventureWorks_Differential.bak'
WITH STATS=10, DIFFERENTIAL, FORMAT

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks_log.trn'
MIRROR TO DISK = 'C:\Mirror\AdventureWorks_log.trn'
WITH STATS=10, FORMAT



When it comes to restoring the database, we can use either of the backup copies to restore or recover the database.

Friday, September 16, 2011

Hide an Instance of SQL Server Database Engine

In one of my previous post "List of SQL Server instances currently installed in your network", I explained how to get the list of SQL server instances installed in your network.
Sometimes you might require an SQL Server instance to be not exposed to others due to some security reason. This can be done by using the "Hide" option available for an SQL Server instance.
Before Hiding an Instance:

Steps to Hide an Instance of SQL Server Database Engine
  1. Go to "SQL Server Configuration Manager"
  2. In the Left Pane Expand "SQL Server Network Configuration"
  3. Now Right Click on "Protocols for <ServerName>" and go to Properties for the server you need to hide.
  4. In the "Flags" Tab, change the option to "Yes" for "Hide Instance"
  5. Click on "Apply" and "OK"
  6. Now re-start the SQL Server Service for this Instance and you are done. This instance from now will be hidden.
After Hiding an Instance:

Friday, September 2, 2011

Get last backup details of all databases in a server

This stored procedure give you the information about latest backups happened on all databases in a server.
This SP works for SQL server 2005 and up.
Create Proc sp_BackupDetails
AS
DECLARE @BackupDetails table
([Server Name] nvarchar(500),
[Database Name] nvarchar(500),
[Last Full Backup] nvarchar(500),
[Last Differential Backup] nvarchar(500),
[Last Log Backup] nvarchar(500),
[Last File or filegroup Backup] nvarchar(500),
[Last Differential file Backup] nvarchar(500),
[Last Partial Backup] nvarchar(500),
[Last Differential Partial Backup] nvarchar(500)
)

DECLARE @DBName nvarchar(500)
Declare DBName Cursor for
Select name from sys.databases
Open DBName
Fetch Next from DBName into @DBName
While @@fetch_status = 0
BEGIN
Insert into @BackupDetails
select @@ServerName as [Server Name]
       ,SDB.name AS [Database Name]
       ,(select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='D') AS [Last Full Backup]
       ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='I') AS [Last Differential Backup]
       ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='L') AS [Last Log Backup]
       ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='F') AS [Last File or filegroup Backup]
       ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='G') AS [Last Differential file Backup]
       ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='P') AS [Last Partial Backup]
       ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') from msdb..backupset where database_name=@DBName and type='Q') AS [Last Differential Partial Backup]
from sys.databases SDB
where SDB.name =@DBName
Fetch Next from DBName into @DBName
END
Close DBName
DEALLOCATE DBName

Select * from @BackupDetails
GO

Usage:
Exec sp_BackupDetails
go

Results:

Thursday, September 1, 2011

Denali CTP 3 - Show\Hide Results Pane keyboard shortcut not working - Ctrl+R not working

Recently I installed Denali CTP 3 version and was writing a code which is coming next in this blog. During that time I attempted to hide the results pane using the keyboard shortcut Ctrl+R and was surprised that the keyboard shortcut Ctrl+R was not working. When I investigated further I found that that the keyboard shortcut was not assigned to the command "Hide Results Pane".
To fix this issue or to assign the keyboard shortcut to the command "Hide Results Pane", follow the below steps.
  1. In a SSMS window, go to "Tools" menu and choose "Options".
  2. In the left pane of "Options" window, expand "Environment" and then click on "Keyboard".
  3. In the right pane, Search for the command "Window.ShowResultsPane" Under "Show Commands Containing:" section and select that command
  4. Now, Under "Shortcuts for selected command:" section choose "SQL Query Editor" from the dropdown list of "Use new shortcut in:" sub-section.
  5. In the "Press shortcut keys:" sub-section place the cursor and press the keys "Ctrl" followed by "R" 
  6. Click on "Assign" button and click "OK" to exit the "Options" menu and start using the keyboard shortcut "Ctrl+R" to Hide\Show Results Pane.