Tag Archives: T-SQL - Page 3

Backup Script with Retention

Posted by on June 28, 2008 Comments Off

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

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)

How To Check SQL SERVER Uptime Through T-SQL

Posted by on June 21, 2008 No comments

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

VN:F [1.9.17_1161]
Rating: 4.0/5 (2 votes cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)

Diskspace Check via SQLServer

Posted by on June 13, 2008 No comments

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

Download diskspace.vbs

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)

Difference between TRUNCATE and DELETE command

Posted by on June 11, 2008 No comments

Lets see the difference between DELETE and TRUNCATE command as below

DELETE

TRUNCATE

DML Statement

DDL Statement

Data Rollback is possible, all the row by row data deletion will be recorded in transaction log

Rollback is possible for entire table and not at row level, since data page reallocation will be logged in transaction log

Eg.)

BEGIN TRAN

TRUNCATE Table Tablename

ROLLBACK

No Commit is performed neither before nor after. (Because it is a DML Statement)

It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)

Row level locking will happen while performing deletion

Table level locking will be happen while performing truncation

A delete does not move the High Water Mark of the table back to zero, it retains its original position.

A truncate moves the High Water Mark of the table back to zero

WHERE statement can be included

WHERE statement cannot be included

Activates DML trigger

Doesn’t activate DDL trigger

Able to execute the command on the table that contains foreign key

Not able to execute the command on the table that contains foreign key

Consume more resources since need to process row by row and all these are logged operation

Consume less resource since it will process the full table and its non-logged operation

Lets discuss the above points using an example

Read more »

VN:F [1.9.17_1161]
Rating: 4.3/5 (3 votes cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)