MS SQL 2008 Standard does not support database compression

I thought is was a normal morning at the office . So, I check job history on all my sql instances and noticed that FULL backups were failing from the night before. Why you ask? Well , I added the compression as default to my sql server boxes backup plans the night before.

Microsoft doesnt even give you an error when adding compression to maintenance plans.

Moral of the story, dont trust that backups are working. Check them every day. You might say , just add a notification to task for failure. However, what if your SMTP is having issues also.

Check for errors in your jobs.

Change the database name

alter database tmsng1
set single_user
go
USE master;
GO
ALTER DATABASE tmsng1
Modify Name = tmsng
GO
use master
go
alter database tmsng
set multi_user

Create a timestamp updatable column

SQL DBA's --have you ever create a column that holds modified time. Yes. Well it probably held the inserted time. It didnt work for the updated time. This little script will work for you very well.

create TRIGGER [dbo].[Account_UTrig] ON [dbo].[Account]
FOR INSERT, UPDATE
AS

UPDATE Account
SET Account.ts = getdate()
FROM Account INNER JOIN INSERTED ON Account.[AccountID] = INSERTED.[AccountID]

Move a database path during a restore!!

SQL Dba's are you moving databases between PROD and DEV. They have different file structures right? Well try using the MOVE command with your restore. It will save time and effort.

restore database BuildingDepartment from disk =
'G:\backup\BuildingDepartment_backup_201112080500.bak'
with recovery,
MOVE 'BuildingDepartment' TO 'G:\Data\BuildingDepartment.mdf',
MOVE 'BuildingDepartment_Log' TO 'G:\Logs\BuildingDepartment.ldf'
go

Database diagram support objects can not be installed?

First make sure that there is a valid owner for the database. I.E. make sure owner is not blank.

If that doesnt fix your problem then try this command on the new query line.

ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login

Version error while executing SSIS package

To clarify a bit more, packages created in Visual Studio 2008 are SQL Server 2008 packages. If you have both 2005 and 2008 installed on your system, you'll need to provide the explicit path to the 2008 version of DTEXEC when you run the package:

MS SQL 2008 DTS package error

Description: The version number in the package is not valid. The version number cannot be greater than current version number.

you'll need to provide the explicit path to the 2008 version of DTEXEC when you run the package: