Tuesday, December 24, 2013

SQL - Recover System Databases

  So we had one of those unfortunate things happen that you hope never happens - our SQL server database drive died. Not the drive running SQL, the drive that held the databases. Luckily we had backups of the databases. The drive was repaired but the drive also held the SQL system tables. Without the system tables SQL server will not run. I had the backups, the BAK files for the system tables, but to restore them you need something to restore them to.
  To recover the five system databases that I needed, master, model, msdb, tempdb, and distribution, I used another database server. I created five databases and restored the backups to the newly created databases. Then I copied the database files, the MDFs and LDFs, to the location that my crashed server was expecting to find them and renamed them to the file names that I knew the server would be looking for. When everything was in place I clicked start on the sql service in the server manager and the server started!
  The next day was spent recovering around forty databases. The databases were listed in SQL Server Management Studio (SSMS) but remember because of the crash there were no database files. For each one I used SSMS to create a new database and rename the database file, the MDFs and LDFs, to the names that the server was expecting. Then using SSMS I restored the backup.

Friday, December 13, 2013

TSQL - Dynamic Update

In a database I needed to search every table that began with 'afx' and find any date field that could be set to NULL and replace any dates that were 1/1/1900 with NULL. Also, the fields couldn't be 'date_from' or 'date_to'. I did a TSQL query to search all tables and fieds and found 83 fields that needed to be changed. This was far too many to do idividualy so I wrote a script based off of my original search to do it.

Here is the TSQL I used:

Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name
On T.TABLE_NAME like 'afx%' and T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
and c.Column_Name <> 'date_from' and c.Column_Name <> 'date_to' and C.IS_NULLABLE = 'YES'
And C.Data_Type = 'datetime'
Open myCursor
Fetch Next From myCursor Into @TN, @CN
While @@Fetch_Status <> -1
Set @SQL = N'Update ' + @TN + ' set ' + @CN + ' = NULL Where [' + @CN + '] = ''1/1/1900'''
Exec sp_executesql @SQL
Fetch Next From myCursor Into @TN, @CN
Close myCursor
Deallocate myCursor