Scheduling Backups in SQL Server 2005/2008 Express Edition

Express editions of both SQL Server 2005 and 2008 do not include the SQL Agent service (even though it is there in an un-enablable disabled state in the SQL Server 2008 edition). This makes it a challenge when one wants to perform a scheduled backup of an Express edition database.

I had to perform such a task today, and here is the solution shared for your benefit. I must warn you however, that this is but a simple solution in order to satisfy one of our simple in-house tasks. You can of course expand upon it to do more wonderful things.

The steps I performed were:

1. Wrote a stored procedure which would create a backup.
2. Wrote T-SQL code to execute the stored procedure and put it in a .sql file
3. Wrote a batch file to call the .sql file.
4. Schedule the batch file using the Windows scheduler.

Take a look at the Code Sample.

If you would like to schedule much more complex maintenance tasks on SQL Server 2005/2008 Express edition databases, here is something cool: ExpressMaint.

 

Search for text within Stored Procedure, View, Trigger and Function code

Was helping out a couple of colleagues to find a little piece of code from a plethora of stored procedures and triggers in the morning. Hence, thought of posting it here as part of my very infrequent Not-So-Hip-Snippets-Yet-Useful-At-Times series of code snippets.

This simple little snippet searched for pieces of text within a database object such as a stored procedure or trigger, and returns the name, type and last modified date of the object. You could search for pieces of text such as UPDATE dbo.Monsters or SET [Gender] = or some piece of code that you remember writing inside an SP or so.

 1 DECLARE @SearchPhrase varchar(100)
 2 DECLARE @Types TABLE ([type] varchar(5) COLLATE Latin1_General_CI_AS_KS_WS)
 3 
 4 -- ENTER TEXT TO SEARCH FOR:
 5 SET @SearchPhrase = ''
 6 
 7 -- The following statements include object types to be searched for.
 8 -- COMMENT OUT OBJECT TYPES THAT YOU DO NOT WISH TO BE INDLUDED IN THE SEARCH.
 9 INSERT INTO @Types
10 SELECT ''
11 UNION SELECT 'TR'-- Triggers
12 UNION SELECT 'P'-- Stored Procedures
13 UNION SELECT 'FN'-- Scalar Functions
14 UNION SELECT 'V'-- Views
15  
16 /* Search code */
17 SELECT
18     O.[name] AS [ObjectName],
19     O.[type_desc] AS [ObjectType],
20     O.[modify_date] AS [ModifiedDate]
21 FROM
22     sys.syscomments C
23     INNER JOIN sys.objects O
24     ON C.[id] = O.[object_id]
25 WHERE
26     C.[text] LIKE '%' + @SearchPhrase + '%'
27     AND O.[type] IN (SELECT [type] FROM @Types)

It’s a great way to search for a database object that you had written and cannot remember anymore, save for the little bit of code that you remember writing in it.

SQL Server 2008 vs. Windows 7

Installed the RTM of Windows 7 on my home PC a couple of days ago. Then I tried installing SQL Server 2008 on it. Found out that SQL Server 2008 is incompatible with Windows 7. To make it compatible you would need to install SQLServer Service Pack 1 or later.

This slideshow requires JavaScript.

On the same note, to make SQL Server 2005 compatible; you would need to install Service Pack 3 or later.

T-SQL Best Practices and Coding Standards for Developers – An open presentation

This post is an inspiration from a presentation I did at the SQL Server Sri Lanka User Group’s monthly meeting in July (2009). The target audience of the session were application developers (especially those who do not work with databases much or those who do not like the database part of development)

And, what inspired the presentation? Developers who hate database work. There are so many out there, no offence intended. But they do just that. Hate database work. And then there are those who do not bother much with database work. I’m not labeling them as bad guys or such. They all have their reasons and opinions, but there is just one problem due to this: bad databases. Databases are the foundation of an application. And so, just like the foundations of buildings; if the database is bad, the application comes crashing down. Not today, not next month, not during the next year… But eventually it will. I am in the process of witnessing one such situation these last couple of months.

The presentation intended to take these developers (by the hand) and introduce them to some simple yet important standards and best practices, which they could put into practice in their day to day work in order to build better databases and use the databases efficiently.

The presentation consists of tips along with slide notes of areas which I thought was important. The session at the user group meeting was quite an interactive one where a lot of ideas were shared. This brought about another inspiration: I have shared the PowerPoint file. It is open to all of you to download, add content and email me the updated PowerPoint for me to publish again.

You could email me at gogulaa [at] gmail [dot] com.

Presentation: <<T-SQL Coding Standards And Best Practices for Developers.pptx>>