Removal of SQL Server 2008 R1

The day after I installed two instances of SQL Server 2008 R1 on my old Windows-2003 test-server I decided to remove them, here’s what I did …

– rebooted 😉
– start/ settings/ control panel/ add or remove programs/ Microsoft SQL 2008/ remove
– from the instance screen I chose the last instance installed
– from the component screen I ticked All Components
– rebooted and repeated the above steps for the other instance
– used the Add or Remove screen to remove the SQL 2008 browser then native client
– removed the base folders for the two instances from my c-drive
– used Start/Search to find any other folders modified yesterday (happily there were none)
– rebooted then fired up SQL Server 2005 to test it still worked – no problems 🙂

An example of a sql Restore

An example of restoring database Liath using sql-script.

If database exists make it single-user-mode

Alter database liath set single_user with rollback immediate

If possible backup the tail IE: unbacked up data

Backup log liath to disk = N’\’ with init, norecovery

Restore the last full backup EG:-

Restore database liath from disk = N’\’ with norecovery

Restore the latest differential backup EG:-

Restore database liath from disk = N’\’ with norecovery

Restore all the transactional logs in sequence from the last diff backup EG:-

Restore log liath from disk = N’\’ with norecovery

Restore log liath from disk = N’\’ with norecovery

Restore the final tail backup

Restore log liath from disk  = N’\’ with recovery

Change database to mult-user mode

Alter database liath set multi-user

Remove Completely Unused Indexes

Here’s a great little script I found that lists indexes in the current database with no stats at all. That is, indexes that have never been read or even written to (since the last reboot, note). I’m not sure how much overhead these will have on the system – but it’s probably worth clearing them out anyway.

From the results I wouldn’t delete the ‘system’ indexes (that is ones starting with ‘MS~’ or ‘sys~’ and the like), or Clustered Indexes 🙂

I was about to work-up a script to delete them, then (phew) remembered … “dba-rule #23: never automate deletes”

SELECT object_name(i.object_id) as tableName, as indexName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = db_id()
WHERE objectproperty(i.object_id,'IsUserTable') = 1 and i.index_id> 0
AND s.object_id IS NULL
AND i.is_Primary_Key = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0
--DROP INDEX [index-name] ON [dbo].[table-name]

Record db Growth

To get my own metrics in place quickly at a new sql2008r1 site I created a ‘dba’ database with a table called ‘dbsizes. Then a job scheduled to run every 10pm with the first step called ‘record sizes’ containing this code …

set nocount on

create table #sizes (
name varchar(50),
db_size varchar(50),
[owner] varchar(50),
[dbid] char(1),
created smalldatetime,
[status] varchar(500),
compatability_level char(2))

insert #sizes exec sp_helpdb

insert into dba.dbo.dbsizes
select name, getdate() dbdate, db_size
from #sizes
order by name desc

drop table #sizes