Backup Script with Retention
Introduction:
SQL Backup script is available in all online resources. I couldn't find a script to take care of retention also, so I'm writing this script as procedure to take care of retention too. This script will take full backup of all the databases.
Applies to:
- SQL Server 2000
- SQL Server 2005
- SQL Server 2008
Pre-requisites:
- Xp_cmdshell procedure should be enabled both in SQL 2005 & SQL 2008
Script:
Dowload the script HERE
Usage:
EXEC master.dbo.usp_backup 'F:\Bkup\',1
Output:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$ SERVERNAME: SAGARSYS -- DATE: Jun 28 2008 10:00AM $
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Retention
^^^^^^^^^
Deleting the below backup files as part of retention plan......
output
---------------------------------------------------------
master_20080627.bak
model_20080627.bak
msdb_20080627.bak
NULL
output
----------------------------------------------------------
NULL
Database Backups Started
^^^^^^^^^^^^^^^^^^^^^^^^
************Processing master Backup... **************
Processed 352 pages for database 'master', file 'master' on file 1.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 355 pages in 0.747 seconds (3.887 MB/sec).
Backed up to F:\Bkup\master_20080628.bak
********************************************************
************Processing model Backup... **************
Processed 152 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.344 seconds (3.655 MB/sec).
Backed up to F:\Bkup\model_20080628.bak
********************************************************
************Processing msdb Backup... **************
Processed 608 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 2 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 610 pages in 0.874 seconds (5.712 MB/sec).
Backed up to F:\Bkup\msdb_20080628.bak
********************************************************
============Backup Completed Successfully============
Discussion:
All your comments are highly appreciated, please post your comments @ FORUMS section
How To Check SQL SERVER Uptime Through T-SQL
Introduction
Services uptime can be checked through WMI scripts and other methods also. As a DBA most of us would like to know the uptime of SQL Server, i.e how much time is SQL Server running till the server is on. You can do almost all the stuffs in T-SQL, hence I'm writing the script in T-SQL to find out SQL Server Uptime. You can also use this script to check SQLServer service and SQLAgent server are running status!
SQLScript:
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
Output:
SQL Server "SAGARSYS" is Online for the past 2 hours & 48 minutes
SQL Server and SQL Server Agent both are running
Diskspace Check via SQLServer
I'm writing this script since most of them are looking a way to find total diskspace available in a drives through sql server. I hope there is no extended procedure for this. I've used WMI script to do this, download the vbs script and save it to a location and use this location in the sql script to get the result.
SQL Script
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp')
DROP TABLE ##tmp
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell 'cscript C:\diskspace.vbs' -- change the path here
SET ROWCOUNT 3
DELETE ##tmp
SET ROWCOUNT 0
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp2')
DROP TABLE ##tmp2
CREATE TABLE ##tmp2(Driveletter VARCHAR(2),TotalDiskSpace_in_MB FLOAT, Freespace_in_MB FLOAT)
INSERT ##tmp2
SELECT SUBSTRING(diskspace,1,3) , CONVERT(FLOAT,SUBSTRING(diskspace,4,10)),
CONVERT(FLOAT,SUBSTRING(diskspace,15,10)) FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2
Sample Output
Driveletter |
TotalDiskSpace_in_MB |
Freespace_in_MB |
|
C: |
12644.8789 |
1153.70312 |
|
D: |
45504.3945 |
29603.9921 |
|
E: |
10001.371 |
450.316406 |
Finding Out SQL Server CPU usage Using T-SQL Code
Hi Friends,
In many companies developers won't be given administrator privilege in OS on their test or development machines. When they execute a query and consider its consuming high CPU usage, since they won't have admin privilege they are not able to confirm that the CPU is consumed by sqlservice or not. I've written a T-SQL Code to check the CPU usage consumed by Sqlservice.
Check out the below script.
Code:
DECLARE @CPU_BUSY int, @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 by sqlsrvr.exe'
Output:
CPU Utilization by sqlsrvr.exe
---------------------------------------
0.850000000000000
Hope this small script will help you to check out sqlservice usage via T-SQL Statement!
How to find TCP\IP port via T-SQL
In most of the forums I've seen that users are not able to find on which port does their sql server is listening. Hence I decided to write a script to check TCP\IP port on which sql server is listening. Checkout the below script.
Script
DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)
IF (SERVERPROPERTY('INSTANCENAME')) IS NULL
BEGIN
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
END
ELSE
BEGIN
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME')) + '\MSSQLServer\SuperSocketNetLib\Tcp\'
END
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'
Output
| Value | Data |
| tcpPort | 1433 |