Arrivederci W.

Dis4ea is dead, long live WesleyB!

Last week my favorite colleague - you know the one :) - left his company to launch his career into a whole new, interesting direction.
His old blog tells the story of his four years at our company as a database architect. It won't get updated anymore but many posts are still as relevant now as at the time they were posted - it's his long term memory after all. And there is the excellent blogroll of course.
The content of his new blog will surely treat topics differently (the host's always watching), but I hope he will continue to produce quality.

Enough said... success!

Dive into msdb: BackupSet & RestoreHistory

Simple question today: can we find out how recent the backup is that has been used to restore our production copy?

select top 1 b.backup_finish_date

from msdb..backupset b

join msdb..restorehistory r on b.backup_set_id = r.backup_set_id

where r.destination_database_name = 'MyDb'

order by r.restore_date desc

Transaction log files after reverting from snapshot

Watch out with the transaction log file(s) when you revert a database from a snapshot! A snapshot database has no transaction log itself and when you revert the original database to the snapshot database, it is documented that Sql Server rebuilds the log. It does however not reuse the characteristics of the original transaction log file(s) as one could expect. The actual and maximum size, the autogrowth property and even the number of files are not necessary the same as before reverting. This could get you easily into trouble.

Lets illustrate this with an example, by creating a database SnapshotTest with 2 transaction log files, which will probably not be the same number as is the case for the model database. A snapshot of SnapshotTest with the name SnapshotTest_ss gets created.
Now for the interesting stuff: we revert the database from the snapshot and we investigate the transaction log files as we did earlier. Only the first transaction log file will be present for the SnapshotTest database but not with the same characteristics as before and not even the ones of model's transaction log. The second file is no longer part of the database. Is this desired behavior?

USE [master]
GO
CREATE DATABASE SnapshotTest
ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest.MDF')
LOG ON (NAME = SnapshotTestLog1, FILENAME = 'C:\TEMP\SnapshotTestLog1.LDF')
, (NAME = SnapshotTestLog2, FILENAME = 'C:\TEMP\SnapshotTestLog2.LDF')
GO
EXEC sp_helpdb SnapshotTest
GO
CREATE DATABASE SnapshotTest_ss ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest_ss.MDF')
AS SNAPSHOT OF SnapshotTest
GO

RESTORE DATABASE SnapshotTest
FROM DATABASE_SNAPSHOT = 'SnapshotTest_ss'
GO
EXEC sp_helpdb SnapshotTest
GO
USE [master]
GO
DROP DATABASE SnapshotTest_ss
GO
DROP DATABASE SnapshotTest

Don't forget to clean up the database file that is left behind on the file system - it's no longer part of a database so it's not removed when you drop the database.