SQL-Articles

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

SQL Server 2005, SQL Server 2008, Scripts Backup, T-SQL

Introduction

Backup compression is one of the feature expected by every administrator to minimize the backup size and to squeeze the time taken for backup. In previous versions of SQL Server most of us are going for 3rd party tools like Quest’s Litespeed, Redgate’s SQL Backup etc. for which we need to pay additional amount. SQL Server 2008 adds an additional feature of taking compressed backup through native backup command itself.

Edition supporting Backup Compression

• SQL Server 2008 Enterprise Edition

Pros of taking compressed backups

• Backup size gets reduced
• Less time taken to take backup
• Less tape is required if you feed this backup file to tape
• Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, every SQL Server 2008 or later edition can restore a compressed backup.
• IO will be less compared to normal backup

Restrictions

• Compressed and uncompressed backups cannot co-exist in a media set.
• Previous versions of SQL Server cannot read compressed backups.
• NTbackups cannot share a tape with compressed SQL Server backups.
• CPU usage will be significantly higher compared to normal backup(but lasts for few minutes), this might affect the concurrent processes

Read more…

General, SQL Server 2008 Administration

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

Scripts, T-SQL SQL Server, T-SQL

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

Download diskspace.vbs

Scripts, T-SQL, Uncategorized Diskspace, SQL Server, T-SQL

What is copy database wizard?

Copy Database Wizard is a new feature from SQL Server 2005 onwards. You can make use of this feature to copy \ move databases between different instances of SQL Server. It can be used for the below purposes

• Transfer a database when the database is still available to users by using the SQL Server Management Objects (SMO) method.
• Transfer a database by the faster detach-and-attach method with the database unavailable during the transfer.
• Transfer databases between different instances of SQL Server 2005.
• Upgrade databases from SQL Server 2000 to SQL Server 2005.

Note: The Server from which you are running CDW should be patched with minimum SQL Server SP2 (better update with latest SP) for Copy Database Wizard (CDW) to work properly

Read more…

General, SQL Server 2005, SQL Server 2008 Copy Database Wizard