Every once in a great while I need to just return the date of a date time and forget about the time part of it.

SELECT CAST(FLOOR(CAST(getDate() AS FLOAT)) AS DATETIME) as MyDate

Obviously you can replace getDate() with a column.

Yesterday we had a freak outage of database mail. Our SQL Server 2005 on our Server 2008 machine decided to just queue up all the mail, and not send anything out for several hours.

There were a few errors in the database mail log, like Mail Server not responding, but I verified at the time the mail server was fine. Plus the errors ended hours prior but no mail was going out.

Here’s my simple fix. Using query anaylizer I checked the items in the mail queue by running the following on the MSDB database:

exec sysmail_help_queue_sp @queue_type = ‘Mail’ ;

To double check the actual items, I ran the following query:

SELECT top 500 * FROM sysmail_allitems where sent_date > DATEADD(day, -1, getDate()) order by sent_date DESC

Once I was satisified that it was the database mail’s failure and not my application or the webservice, I did the scary task of hitting CTRL-ALT-DEL on the server, and killing “DatabaseMail90.exe”. It instantly restarted itself and proceeded to send out all items in the queue. It’s been fine ever since.

I did the end process because no matter what restart command I could find it would not respond, it was just hang there. So, this seemed to work quite well.

I needed to make a stored procedure that could make a copy of a database on a SQL 2005 server. I wanted to do it entirely through T-SQL.

I found this excellent guide at: http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx

So, thank you Michael’s blog for the start, but the problem was it did not seem to work on SQL2005. So I had to make a few changes:

So starting with Michael’s original code I made the following modifications:

1.  Added INIT to the backup database. This tells the sql server not to append to the backup file but to overwrite it. I was going to run this fairly often so I did not need a ever growing backup file.

2. I got rid of the drop. Instead the procedure only runs if the database does not already exist. I did not feel comfortable with this.

3. RESTORE FILELISTONLY FROM DISK appears to return a different result set than it did in SQL2000, so I had to update the temptable.

4. @@ROWCOUNT and RESTORE HEADERONLY FROM DISK were not playing nicely together in 2005. It always returned 0 records. Instead of fight with it, I did away with the file argument in the BACKUP DATABASE. So this does concern me that if the backup fails it will attempt to restore it. But it’s a risk I’m willing to live with at this time.

So here’s my changes to Michael’s code. All credit to him for sharing the original method:

USE master
GO

– the original database (use ‘SET @DB = NULL’ to disable backup)
DECLARE @DB varchar(200)
SET @DB = ‘PcTopp’

– the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = ‘c:\pctopp\sqlserver\backup.dat’

– the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = ‘TestDB’

– the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = ‘c:\pctopp\sqlserver\backup’
– ****************************************************************
–                    no change below this line
– ****************************************************************

DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + ‘.mdf’
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + ‘.ldf’
IF @DB IS NOT NULL
BEGIN
SET @query = ‘BACKUP DATABASE ‘ + @DB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””) + ‘ WITH INIT’
EXEC (@query)
END

IF NOT EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
DECLARE @File int
RESTORE HEADERONLY FROM DISK = @BackupFile
SET @File = @@ROWCOUNT
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””)
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint,
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = ‘D’
SELECT @Log = LogicalName FROM #restoretemp WHERE type = ‘L’
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

SET @query = ‘RESTORE DATABASE ‘ + @TestDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +
‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +
QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””)
EXEC (@query)

END