Recover Jobs from a crashed SQL Server
I recently came across the scenario where a SQL server had crashed (with Windows) but the file system was intact, so we started restore of databases which was straight forward as easy as attaching to the new instance on different server. But we also had lot of jobs scheduled on the old server, and wanted to recover them as well, so after a doing a bit of search figured out that the jobs are saved in “msdb” system database.
So to restore
- Create a dummy instance of SQL Server.
- Stop the instance.
- Replace the .mdf & .ldf files from the crashed server of the msdb database.
- Start the Server Instance as well as agent.
- voila, you have your jobs, now script those jobs and execute on new server
and you are good to go..
Nifty Little script to check identity columns
I had to recently check for identity specification on all the tables in a database and this nifty little script helped me in doing this.
Thanks Akshay for writing this for me.
CREATE PROC dbo.CheckIdentities AS BEGIN SET NOCOUNT ON SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName, c.name AS ColumnName, CASE c.system_type_id WHEN 127 THEN 'bigint' WHEN 56 THEN 'int' WHEN 52 THEN 'smallint' WHEN 48 THEN 'tinyint' END AS 'DataType', IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue, CASE c.system_type_id WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807 WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647 WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767 WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255 END AS 'PercentageUsed' FROM sys.COLUMNS AS c INNER JOIN sys.TABLES AS t ON t.[object_id] = c.[object_id] WHERE c.is_identity = 1 ORDER BY PercentageUsed DESC END