Archive for the ‘MS SQL Server’ Category

« Older Entries

T-SQL: Backup All User Databases

Wednesday, August 18th, 2010

Here’s an easy, generic way to back up all user databases on a SQL Server instance:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\localBackups\' -- has to be a local path

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

Shrinking your Transaction Log with SQL Server 2008 to Free Disk Space

Friday, February 5th, 2010

If you have been used to clearing up disk space on your development and test SQL server instance file systems with SQL Server 2000 or SQL Server 2005 by using the famous TRUNCATEONLY option on your transaction logs, you might be disheartened that they have removed this option from SQL Server 2008 (with good reason some might say).

If you do try to use it, you will get the infamous:

‘TRUNCATEONLY’ is not a recognized BACKUP option.

To achieve the same effect with SQL Server 2008, you can toggle the recovery mode for the target database and then call your DBCC SHRINKFILE to clear up disk space hogged by your transaction log.

USE MyDatabase
GO
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE('MyDatabase_log',1)
GO
ALTER DATABASE MyDatabase SET RECOVERY FULL
GO

(Of course if you are already using simple recovery, you wouldn’t need to toggle.)

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

Don’t get caught by ANSI PADDING, VARCHAR, and trailing whitespace!

Thursday, January 28th, 2010

One thing to be aware of when you have VARCHAR columns and are using ANSI PADDING: trailing whitespace is trimmed and not counted in your equality ( = / <> / LIKE) statements, so you might not get the results you are looking to be returned from your query.

In the same vein, the len() function will return the same value for two strings if one has trailing whitespace.  If you need to compare those two fields including trailing whitespace values, use datalength() instead.

Here’s some example T-SQL:

DECLARE @TABLE TABLE (FIELD VARCHAR(50))

INSERT @TABLE VALUES ('some text')
INSERT @TABLE VALUES ('some text       ')
INSERT @TABLE VALUES ('    some text')

-- returns 2 :
-- trailing spaces trimmed,
-- leading spaces count
SELECT '"' + FIELD + '"'
FROM @TABLE
WHERE FIELD = 'some text'

-- also returns 2
SELECT '"' + FIELD + '"'
FROM @TABLE
WHERE FIELD LIKE 'some text'

-- shows the difference between len()
-- and datalength()
SELECT FIELD, LEN(FIELD) AS 'Length', DATALENGTH(FIELD) AS 'DataLength'
FROM @TABLE

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

December 2009 IT Toolbox for TechNet Magazine

Tuesday, December 8th, 2009

The December 2009 TechNet Magazine is on the website. Check out my IT Toolbox column here:

December 2009 TechNet Magazine IT Toolbox

In this issue I covered:

  • Eraser: Eradicate Sensitive Information
  • SQL Server Backup Pro from Red Gate: Streamline SQL Server Backups
  • MindManager: Map Out Ideas, Notes and Projects

Check it out and let me know what you think!

And if you have a tool you want to see me review, please suggest it to me here: tntools@microsoft.com.

Posted in MS SQL Server, Microsoft TechNet | No Comments »

T-SQL: Change Collation on your LIKE Clause Using COLLATE

Friday, July 24th, 2009

Ever have trouble matching unicode letter equivalents in your T-SQL queries? For example, when you want “Québec” and “Quebec” to be equivalent in your text search query?

This is where specifying Accent Insensative Collation (The “AI” in all those collation codes) comes to the rescue.  If your database is already set to an AI collation, then you are all set already, but if not, change your LIKE statement to use something like the following:

SELECT *
FROM MyTable (NOLOCK)
WHERE MyColumn COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%quebec%'

Hope this helps!

Posted in MS SQL Server, T-SQL | No Comments »

T-SQL Hack to Turn a Set of Row Values into a Delimited String

Thursday, February 26th, 2009

Ever want to turn multiple rows into a comma separated list when you are querying your database?

Here’s a quick way to get it done using “FOR XML PATH(”)”:

DECLARE @comma_delimited_list VARCHAR(6000)
SELECT @comma_delimited_list =
(
SELECT productName + ','
FROM Products
WHERE productName LIKE 'a%'
FOR XML PATH('')
)

IF LEN(@comma_delimited_list) &gt; 0
SELECT @comma_delimited_list = STUFF(@comma_delimited_list ,LEN(@comma_delimited_list), 1, '')

A couple things to remember: 1) you have set your path to ” and 2) you can’t name your selection or it will put the name into your xml adding more information than you want.

Posted in MS SQL Server | No Comments »

Quick Method of Finding Out What Version and Service Pack your SQL Server Instance is Running

Saturday, January 24th, 2009

If you are looking for a quick way to verify the service pack and version applied to your SQL server instance, try the following T-SQL:

SELECT
SERVERPROPERTY('productversion') AS productversion,
SERVERPROPERTY('productlevel') AS productlevel,
SERVERPROPERTY('edition') AS edition

Hope this helps!

Posted in MS SQL Server | No Comments »

How to put a database into Single User Mode with T-SQL

Saturday, October 11th, 2008

You can easily put a database into or take it out of single user mode with the following T-SQL:

ALTER DATABASE  [MyDatabase] SET SINGLE_USER WITH NO_WAIT
GO

ALTER DATABASE  [MyDatabase] SET MULTI_USER WITH NO_WAIT
GO

Posted in MS SQL Server | No Comments »

Time Your T-SQL Queries

Sunday, August 17th, 2008

Here’s a quick way to get some timing statistics on your T-SQL:

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS TIME ON
GO

-- Insert your query here

SET STATISTICS TIME OFF
GO

After you run the statements in query analyzer, check the messages tab to see how it went.

Posted in MS SQL Server | No Comments »

How to Shrink Your Database Transaction Log

Saturday, July 19th, 2008

How to Shrink Your Database Transaction Log

If you have noticed that your transaction log on your SQL Server database has grown incredibly large after a long transactional process, you can try this to shrink it back down:

DBCC SHRINKFILE(mydatabase_log, 2)
GO

BACKUP LOG mydatabase WITH TRUNCATE_ONLY
GO

DBCC SHRINKFILE(mydatabase_log, 2)
GO

Of course, you’ll want to replace “mydatabase” with the name of the database you wish to shrink and “mydatabase_log” with the name of the log for that database.

Posted in MS SQL Server | No Comments »

« Older Entries