SansSQL

Tuesday, April 19, 2011

SQL Server 2011 - Zoom Query Window and Results pane - New Feature

SQL Server 2011 comes with lots of new features and here is one such new feature.
SQL Server 2011 has introduced zooming of Query window and results pane.
Query Window - Normal

Query Window - Zoomed

Results Pane - Normal

Results Pane - Zoomed
Posted by Sandesh Segu 2 comments
Labels: , , ,

Thursday, April 7, 2011

T-SQL Query to find the SQL Server Protocols Status

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.
DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @value_Out VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

CREATE TABLE #SQLServerProtocols
(ProtocolName nvarchar(25),
Value nvarchar(10),
Data bit)

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null


SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null
END

SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled' 
ELSE 'Disabled' END FROM #SQLServerProtocols

DROP TABLE #SQLServerProtocols

Download this script from here.
Posted by Sandesh Segu 4 comments
Labels: , , , , , , , , , ,

Saturday, March 26, 2011

Page Level Restoration

Sometimes you might come across a situation where a particular Page of a SQL Server database gets corrupted due to various reasons. In such cases only that corrupted page can be recovered using a backup.
To explain how to recover a page from the backup, I will first need to corrupt a page on a database.
By using the below method I am going to simulate the page corruption and then recover using the backup taken before corruption.
First I am going to create a database by name "TestPageLevelRestore" and Set its recovery Model to FULL.
USE master;
GO
CREATE DATABASE TestPageLevelRestore
ON
( NAME = TestPageLevelRestore,
    FILENAME = 'D:\TestPageLevelRestore.mdf',
    SIZE = 10)
LOG ON
( NAME = TestPageLevelRestore_log,
    FILENAME = 'D:\TestPageLevelRestore_log.ldf',
    SIZE = 5MB) ;
GO
Print 'Database TestPageLevelRestore Created'
ALTER DATABASE TestPageLevelRestore SET RECOVERY FULL
Print 'Recovery Model of database TestPageLevelRestore has been changed to FULL'


Now Create a table and insert data into that table.
Use TestPageLevelRestore
GO
CREATE TABLE [Shift](
      [ShiftID] tinyint IDENTITY(1,1) NOT NULL,
      [Name] nvarchar(50) NOT NULL,
      [StartTime] datetime NOT NULL,
      [EndTime] datetime NOT NULL,
      [ModifiedDate] datetime NOT NULL,
 CONSTRAINT [PK_Shift_ShiftID] PRIMARY KEY CLUSTERED ([ShiftID] ASC)
)
Print 'Creation of Table "Shift" Completed'

SET IDENTITY_INSERT [Shift] ON
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (1, N'Day', '1900-01-01 07:00:00.000', '1900-01-01 15:00:00.000', '1998-06-01 00:00:00.000')
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (2, N'Evening', '1900-01-01 15:00:00.000', '1900-01-01 23:00:00.000', '1998-06-01 00:00:00.000')
INSERT [Shift] ([ShiftID], [Name], [StartTime], [EndTime], [ModifiedDate]) VALUES (3, N'Night', '1900-01-01 23:00:00.000', '1900-01-01 07:00:00.000', '1998-06-01 00:00:00.000')
SET IDENTITY_INSERT [Shift] OFF

Print 'Data Insertion to table "Shift" Completed'


Now take a FULL backup of the database
BACKUP DATABASE TestPageLevelRestore TO DISK='D:\TestPageLevelRestore_FullBackup.bak' WITH STATS=10
Print 'Full Backup Completed'


After the backup is completed, get the list of index ID's from which you can choose one to corrupt
--To get the list of index ID's from which you can choose one to corrupt
Use TestPageLevelRestore
Select * from sys.indexes where OBJECT_NAME(object_id)='Shift'


Now get the list of pages in that index.
--To get the list of pages
DBCC IND ('TestPageLevelRestore', 'Shift',1)


Now you can get the page level details using the below query
-- To display the contents
DBCC TRACEON (3604);
GO
--TO get the page level data details
DBCC PAGE('TestPageLevelRestore',1,147,3);


For corrupting a particular page using a hex editor, you need to get the offset value, to obtain the offset value of a page simply multiply the PageID with 8192
--Get the Offset Value. This can be obtained by multiplying the page ID with 8192.
--Once you get the result copy the result and set the database to offline
SELECT 147*8192 AS [OffSetValue]


Once you get the offset Value, just copy it and take the database Offline.
USE MASTER
ALTER DATABASE TestPageLevelRestore SET OFFLINE
Print 'Database TestPageLevelRestore is set to Offline. Now Open the TestPageLevelRestore.mdf file in the hex editor and press ctrl+g to go the page where the index data is located.
Choose Decimal and paste the offset value.
once you go to the location, then manuplate the value and save the file and exit hex editor.
After manuplating data bring database online.'


Now Open the data file of the database "TestPageLevelRestore.mdf" file in the hex editor and press ctrl+g to go the page where the index data is located.

Then choose Decimal option and paste the offset value.
Once you go to the location, then manuplate the value and save the file and exit hex editor.



You can now see that I have edited the page and saved the file.

Once you edit the file and saved it, exit the Hex Editor and bring back the database to Online state.
USE MASTER
ALTER DATABASE TestPageLevelRestore SET ONLINE
Print 'Database TestPageLevelRestore is set to Online'


Now try to read data from the table and you will get error which states that the read failed at page (x:xxxx)
--Select the data and you will get error stating that the read failed at page (x:xxxx)
USE TestPageLevelRestore
Select * from shift
select * from sys.master_files where DB_NAME(database_id)='TestPageLevelRestore'


So this means that the page is now corrupted. By this the simulation of page corruption is completed.
Now we need to start looking on how to recover the page using page level restore.
Before we start the recovery process, we need to backup the tail of the log.
USE master
BACKUP LOG TestPageLevelRestore TO DISK = 'D:\TestPageLevelRestore_log.bak' WITH INIT, NORECOVERY;
GO

After backing up the tail log, restore the corrupted page using the below command.
Restore DATABASE TestPageLevelRestore Page='1:147' FROM DISK='D:\TestPageLevelRestore_FullBackup.bak'


After the page restoration is completed, Restore the tail log backup.
RESTORE LOG TestPageLevelRestore FROM DISK = 'D:\TestPageLevelRestore_log.bak';
GO


Now you have restored the corrupted page from a good backup and this can be verified by selecting the data from the table and you will be able to retrieve the data.
USE TestPageLevelRestore
Select * from shift


The complete demo script that I have used in this post can be downloaded from here.
Posted by Sandesh Segu 9 comments
Labels: , , , , , , , , ,

Sunday, March 20, 2011

Automating SQL Server Express Backups

As we all know that many of the third party applications uses SQL Server Express Edition to store their backend data. And also these application will be used in Live environments which requires backing up of the databases to recover data during a disaster.
Since there is no SQL Server agent in Express Edition, we cannot schedule SQL Backups or any other DB Maintenance activities using SQL Scheduler. So for this purpose, we have to make use of the windows scheduler.
For Automating the backup process, i have developed the below query which can be used for multiple purpose and can be scheduled using windows scheduler as well as SQL Agent.

Script:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BackupDatabases]') AND type in (N'P', N'PC'))
BEGIN
PRINT 'Stored Procedure "sp_BackupDatabases" already exists in the database. Dropping the SP to create a newer Version.'
DROP PROCEDURE [dbo].[sp_BackupDatabases]
END
GO

CREATE PROC sp_BackupDatabases (@BackupDBType nvarchar(10)='Help', @DBName nvarchar(max)=NULL, @BackupPath nvarchar(max)=NULL )
AS
/*
Author: Sandesh Segu
Website: http://www.SansSQL.com
*/
SET NOCOUNT ON

DECLARE @DateTime nvarchar(25)
SET @DateTime=LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),120),':',''),'-',''),' ',''),12)

IF @BackupDBType not in ('ALL','System','User','Specific','Help')
BEGIN
RAISERROR ('Incorrect Parameter Value Passed. @BackupDBType Parameters should be ''ALL'',''System'',''User'',''Specific''',16,1)
END

IF @BackupDBType<> 'HELP' and @BackupPath IS NULL
BEGIN
RAISERROR ('Specify the path to backup databases. @BackupPath must be specified',16,1)
END
IF (RIGHT(@BackupPath,1))<>'\'
BEGIN
Select @BackupPath=@BackupPath+'\'
END
ELSE
BEGIN
Select @BackupPath=@BackupPath
END

IF @BackupDBType='Help' AND @DBName IS NULL AND @BackupPath IS NULL
BEGIN
Print 'Usage of this Stored Proc can be in any of the below format.'
Print '-------------------------------------------------------------------------'
Print '1. To Backup All Databases in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''ALL'', @BackupPath = ''C:\Backup''' + Char(10)
Print '2. To Backup only System Databases in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''System'', @BackupPath = ''C:\Backup''' + Char(10)
Print '3. To Backup only User Databases in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''User'', @BackupPath = ''C:\Backup''' + Char(10)
Print '4. To Backup specific (One) Database(s) in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks'', @BackupPath = ''C:\Backup''' + Char(10)
Print '5. To Backup specific (more than One) Database(s) in a instance'
Print '   Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks,master,msdb'', @BackupPath = ''C:\Backup'''
Print '-------------------------------------------------------------------------'
END

IF exists (select * from sys.objects where name='BackupDatabases')
DROP TABLE BackupDatabases
CREATE TABLE BackupDatabases
(DBName nvarchar(100),
DatabaseID int,
[BackupStatement] nvarchar(max))

IF @BackupDBType='ALL' AND @DBName IS NULL AND @BackupPath IS NOT NULL
BEGIN
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name <>'tempdb'
END

IF @BackupDBType='System' AND @DBName IS NULL AND @BackupPath IS NOT NULL
BEGIN
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name in ('master', 'model','msdb')
END

IF @BackupDBType='User' AND @DBName IS NULL AND @BackupPath IS NOT NULL
BEGIN
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE database_id>4
END

IF @BackupDBType='Specific' AND @DBName IS NULL
BEGIN
RAISERROR ('Specify the Database Name(s) to Backup. @DBName must be specified.',16,1)
END

IF @BackupDBType='Specific' AND @DBName IS NOT NULL AND @BackupPath IS NOT NULL
BEGIN
DECLARE @DelimiterPos int
-- Find the first comma
SET @DelimiterPos = PATINDEX( '%,%', @DBName)
-- If a delimiter was found, @DelimiterPos will be > 0.
WHILE @DelimiterPos > 0
BEGIN
-- Insert the value between the start of the string and the first delimiter, into the table variable.
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((SUBSTRING(@DBName, 1, @DelimiterPos -1)))) AS nvarchar      ))                  
-- Trim the string of the first value and delimiter.
SET @DBName = SUBSTRING(@DBName, @DelimiterPos +1, LEN(@DBName) - @DelimiterPos)
                       
-- Look for the next delimiter in the string.
SET @DelimiterPos = PATINDEX( '%,%', @DBName)
END
INSERT INTO BackupDatabases
SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((@DBName))) AS nvarchar))
END

UPDATE BackupDatabases SET BackupStatement= 'Print ''Backup of Database '+DBName+' Started''; Backup Database ['+DBName+'] TO DISK='''+@BackupPath+DBName+'_Backup_'+@DateTime+'.bak'' WITH INIT, STATS=10'
Print 'The Requested database(s) are being backed up to the location "'+@BackupPath+'"'
WHILE (Select COUNT(*) from BackupDatabases)>0
BEGIN
DECLARE @BackupStatement nvarchar(max)
SELECT @BackupStatement= [BackupStatement] FROM BackupDatabases
Exec sp_executesql @BackupStatement
Delete from BackupDatabases where [BackupStatement]=@BackupStatement
END
DROP TABLE BackupDatabases

SET NOCOUNT OFF
GO

Usage:
Usage of this Stored Proc can be in any of the below format.
-------------------------------------------------------------------------
1. To Backup All Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'ALL', @BackupPath = 'C:\Backup'

2. To Backup only System Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'System', @BackupPath = 'C:\Backup'

3. To Backup only User Databases in a instance
   Exec sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup'

4. To Backup specific (One) Database(s) in a instance
   Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks', @BackupPath = 'C:\Backup'

5. To Backup specific (more than One) Database(s) in a instance
   Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks,master,msdb', @BackupPath = 'C:\Backup'

To Delete the old backups I use the below query.
DECLARE @TwoDaysOld VARCHAR(50)
Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR)
Select @TwoDaysOld
Exec master.dbo.xp_delete_file 0,N'C:\Backups\',N'bak',@TwoDaysOld
This will delete any .bak files in the given location which are older than 2 days.

So after creating the sp_BackupDatabases stored proc, if we want to schedule the backups using windows scheduler than we have create a batch file (.bat file) which will be called in the windows scheduler at the scheduled time.
sqlcmd -S(local)\SQLEXPRESS -E -Q"DECLARE @TwoDaysOld VARCHAR(50) Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR)
Exec master.dbo.sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup'
Exec master.dbo.xp_delete_file 0,N'D:\Backups\',N'bak',@TwoDaysOld"

Scripts can be downloaded from the below locations

Posted by Sandesh Segu 6 comments
Labels: , , , , , , , , , ,
Subscribe to: Posts (Atom)

Ads