Archive | April 2014

Script fun!

I wanted to post some of my newer scripts in hopes that they give people a new way to think about doing less manual work.

Most of these came about due to server migrations and not wanting to repeat tasks for each database on servers with a large number of databases.

;with BUS as (
SELECT *, row_Number() over (partition by database_name order by backup_start_date desc) as rn
FROM msdb.dbo.backupset
WHERE type=’D’ –D = Full; I = Diff; L = Log
and database_name not in (‘model’, ‘msdb’, ‘master’)
) select database_name, bus.media_set_id, ‘RESTORE DATABASE [‘ + database_name + ‘] FROM DISK = N”’ + physical_device_name + ”’ WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 1′
from bus
inner join msdb.dbo.backupmediafamily bmf on bus.media_set_id = bmf.media_set_id
where rn = 1

*note*:  Change the D to an I and run again to get the diff listing
select ‘RESTORE DATABASE [‘ + name + ‘] WITH NORECOVERY’,*
from sys.databases
where name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’)
and state_desc = ‘Restoring’

select ‘ALTER DATABASE [‘ + name + ‘] SET COMPATIBILITY_LEVEL = 110’,*
from sys.databases
where name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’)
and [compatibility_level] < 110

Anyway — I hope this helps you think about new ways to do things more efficiently!