Very useful SQL Script that make our life a lot easier
/*Back up to Multiple Files
Databases are becoming larger and larger. You can tune the performance of SQL Server backup compression, by using multiple backup files, and the BUFFERCOUNT and MAXTRANSFERSIZE options. The DatabaseBackup procedure supports these options:*/
EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup, D:\Backup, E:\Backup, F:\Backup’,
@BackupType = ‘FULL’,
@Compress = ‘Y’,
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 4,
@CleanupTime = 24
———————————————————
/*Solve “No Current Database” Issues
Most DBAs have experienced the error message “BACKUP LOG cannot be performed because there is no current database backup” or “Cannot perform a differential backup for database, because a current database backup does not exist”. These errors usually occur when you have created a new database or changed the database recovery model from Simple to Full. The answer is to determine, before you run the backup, whether a differential or transaction log backup can be performed. You can use the DatabaseBackup procedure’s @ChangeBackupType option to change the backup type dynamically if a differential or transaction log backup cannot be performed.*/
–Here’s an example of how to use the @ChangeBackupType option:
EXECUTE dbo.DatabaseBackup
@Databases = ‘USER_DATABASES’,
@Directory = ‘C:\Backup’,
@BackupType = ‘LOG’,
@Verify = ‘Y’,
@ChangeBackupType = ‘Y’,
@CleanupTime = 24
———————————————————–
/*Intelligent Index Maintenance
The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Take a look at this code:*/
/*In this example, indexes that have a high fragmentation level will be rebuilt, online if possible. Indexes that have a medium fragmentation level will be reorganized. Indexes that have a low fragmentation level will remain untouched.*/
EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
/*Update Statistics
The IndexOptimize procedure can also be used to update statistics. You can choose to update all statistics, statistics on indexes only, or statistics on columns only. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.*/
EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’
—————————————————————–
/*Create a DB Snapshot, when you name make sure you put date or time, should be very descrptive*/
use master
go
create database adventureworks_snapshot040809 on
(name = N’adventureworks_data’, filename = N’c:\db\aw_040809.sis’)
as snapshot of adventureworks
go
–verify that the snapshot contains same info as source DB
select *
from Adventureworks2008_snapshot040809.sales.salesorderDetail
order by SalesorderID
Go
Select *
From Adventureworks2008.Sales.SalesOrderDetail
Order by SalesOrderID
go
–Revert the DB to the Snapsot and restore it from snapshot
Drop Table adventureworks.Sales.SalesOrderDetail
GO
use master
Restore Database Adventureworks
From Database_Snapshot = ‘Adventureworks_Snapshot040809’
go
———————————————————————–
–To look for suspect pages to restore
select * from msdb..suspect_pages
where (event_type =1 or event_type = 2 or event_type = 3)
go
/*Restore certain page IDS, backups is the path or the device and ‘1:265’ is the one we found when we run the above code to look for the suspect page*/
use master
go
restore database adventureworks2008
page = ‘1:265′
from backups
go
—————————————————————————-
/*You can also move full-text catalogs by their logical name. But note that when you specify the new catalog location, you specify only new_path rather than new_path/file_name.To move a full-text catalog file to a new location, follow these steps:*/
/*1. Take the database you want to work with offline by typing the following:*/
ALTER DATABASE database_name
SET offline
GO
/*2. Move one file at a time to the new location by typing these commands:*/
ALTER DATABASE database_name
MODIFY FILE ( NAME = logical_name, FILENAME = “new_path”.
GO
/*3. Repeat the previous step to move other full-text catalog files as necessary.*/
–4. Put the database online by typing the following:
ALTER DATABASE database_name
SET online
GO
—————————————————————————–
/*To move data or log files to a new location, follow these steps:*/
/*1. Get the logical name of the data and log files associated with the database by typing the following:*/
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(“Personnel”);
/*2. Take the database you want to work with offline by typing these commands:*/
ALTER DATABASE Personnel
SET offline
GO
/*3. Move one file at a time to the new location by typing the following:*/
ALTER DATABASE Personnel
MODIFY FILE ( NAME = Personnel_Data, FILENAME =
“C:\Data\Personnel_Data.mdf”)
GO
/*4. Repeat the previous step to move other data and log files.*/
/*5. Put the database online by typing the following commands:*/
ALTER DATABASE Personnel
SET online
GO
–You can verify the change or changes by typing this:
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(“Personnel”);
——————————————————————————-
/*
WAITFOR
–Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a
specified statement modifies or returns at least one row.
–USING A WHILE LOOP TO SELECT DATA FROM SALES.CUSTOMER_ALL WITH A 5 min 1 SEC DELAY
— READ INTENSIVE
*/
use AdventureWorks2008
go
DECLARE
@COUNT INT
SET @COUNT = 1
WHILE (@COUNT <= 500)
BEGIN
WAITFOR DELAY ’00:05:01’
select GETDATE()
SET @COUNT = @COUNT + 1
END
————————————————————————-
–how to configure Service Broker and send and receive messages.
——————————————————————-
CREATE DATABASE ServiceBrokerTest
GO
USE ServiceBrokerTest
GO
— Enable Service Broker
ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
GO
— Create Message Type
CREATE MESSAGE TYPE SBMessage
VALIDATION = NONE
GO
— Create Contract
CREATE CONTRACT SBContract
(SBMessage SENT BY INITIATOR)
GO
— Create Send Queue
CREATE QUEUE SBSendQueue
GO
— Create Receive Queue
CREATE QUEUE SBReceiveQueue
GO
— Create Send Service on Send Queue
CREATE SERVICE SBSendService
ON QUEUE SBSendQueue (SBContract)
GO
— Create Receive Service on Recieve Queue
CREATE SERVICE SBReceiveService
ON QUEUE SBReceiveQueue (SBContract)
GO
— Begin Dialog using service on contract
DECLARE @SBDialog uniqueidentifier
DECLARE @Message NVARCHAR(128)
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE SBSendService
TO SERVICE ‘SBReceiveService’
ON CONTRACT SBContract
WITH ENCRYPTION = OFF
— Send messages on Dialog
SET @Message = N’Very First Message’;
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
SET @Message = N’Second Message’;
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
SET @Message = N’Third Message’;
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
GO
— View messages from Receive Queue
SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
— Receive messages from Receive Queue
RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
— Receive messages from Receive Queue
RECEIVE CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
— Clean Up
USE master
GO
DROP DATABASE ServiceBrokerTest
GO
————————————————————————
/*—————————————————————————–
This script captures CPU Utilization Percent using TSQL. It uses system functions @@CPU_BUSY and @@IDLE
to see what is the CPU Utilization % at a particular instant. You can then use this number in a table or variable
to trigger other events.
@@CPU_BUSY shows the time SQL Server has been busy
@@IDLE shows the time SQL Server has been idle
————————————————————————————————————*/
DECLARE @CPU_BUSY int
DECLARE @IDLE int
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE
WAITFOR DELAY ‘000:00:01’
SELECT
(@@CPU_BUSY – @CPU_BUSY)/((@@IDLE – @IDLE + @@CPU_BUSY – @CPU_BUSY) * 1.00) *100 AS CPU_Utilization_Pct
About the Author: Abubeker Refaw
If you get a requirements needed message click ok so that it can install them (updated .NET framework and Windows Installer)Microsoft .NET Framework 3.5 SP1 update
Abubeker A. Refaw | Microsoft Certified Professional (MCITP) 