Restoring SYSTEM databases

I had the opportunity to restore a few system databases due to disk inconsistency/corruption. Actually my first time in a long time and was it fun. I have a few tips to share

All below were done on a SQL Server 2005 instance

Restoring MASTER Database!!
- start SQL Server in Single User mode (add -m to the startup parameter)
- You can Google this with lots of results. Microsoft How to on SQL 2008 R2

Restoring MSDB Database
- This is easy, just restore like a user database

Restoring MODEL Database!
- Restore model in RECOVERY mode, never in NORECOVERY mode as SQL Server will stop (due to tempdb unable to create, without a model database)
- If you did NORECOVERY, the only way to get in is manual work.
For me, I started it and detach-and-attach the model database that I copy from another location, as one cannot issue RESTORE command in Single-User mode

net start MSSQLSERVER /c /m /T3608 (you can go into SQL BINN and run sqlserver -c -m -T3608 as well)

sqlcmd -E (to connect to the SQL Server)

Detach and Re-attach model databasesp_detach_db 'model'
go
sp_attach_db 'model', 'DATA.mdf', 'LOG.ldf'
go
use model
go

Command line parameter	Use
-c	Forces an instance of SQL Server not to start as a service.
-m	Allows updates to the system catalog.
-T3608	Recovers the master database only.

There is a ($50 USD) benefit to Read and Comment Simple Talk articles!

Today is my lucky day as I unexpectedly found a $50 USD Amazon.com gift card in my email inbox, courtesy of simple-talk, all because I read and commented on Tony's "Cloud Backup: Getting the Users' Backs Up" article

You can subscribe to Simple-Talk's SQL and RSS, and usually each newsletter will end with this comment
Reply to my blog post to be in with the chance of winning a $50 Amazon voucher.

I just love Red Gate more and more (as Simple-Talk is owned by Red Gate)

Change the [Change Connection] shortcut in SSMS

I recently found this shortcut and it has been a lifesaver to manage multiple SQL servers in the [New Query] window

I can now paste/type the SQL statement first, ALT+G, enter new SQL name, F5 to execute

http://narfle.com/blog/2006/07/ssms-keyboard-shortcut-for-change.asp

1.Open a Query Editor window connected to your favorite server.

2.Right click on the Change Connection toolbar button on the Query Toolbar button and select the Customize command at the bottom of the menu.

3.Right click on Change Connection button again and then change the command name "C&hange Connection..." to "Chan&ge Connection...". This will change the hot key from Alt-H (which would conflict with the Help menu hot key) to Alt-G.

4.Finally, in the Right Click menu for the Change Connection button, you need to select the command option to display both "Image and Text". This exposes the Alt-G command to the Query Editor.

5.You can now close the Customize dialog and use Alt-G as your hot key!

Is DBA Enough?

IT is a very broad field filled with every position and every dream

I thought my transition from a Database Developer, then a Developer/DBA, and finally becoming a true DBA with MCITP certification will be the finish line. Now I am asking myself - is this it?

Let's ignore the shift from Junior DBA to Senior DBA with salary increase for the moment, I sometimes wonder if SQL is all I can talk about for my whole life. This thought freightened me, just as I do not like settling my life and always wanting to do more, at my job, at my personal life, at my goals

I have found the answer to my question, which means more efforts for the next few years, but I know it will be worthwhile, to challenge myself to be all I can be.

RowGUID

I learned something new today, regarding the RowGUID property in the SSMS Table Design view

In short, to use GUID as Primary Key (not recommended in general), set the column data type to uniqueidentifier, and default value of newid(), or better newsequentialid()

CREATE TABLE Product( 
ProductID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (newsequentialid()), 
ProductName VARCHAR(50), 
ListPrice money 
)    

Only set the RowGuid property to Yes if you are using Replication

Source
The setting of RowGuid=No is correct for a GUID pk. The RowGuid is used by SQL Server replication to uniquely identify rows across replicated databases (i.e. you have have two records with the same PK on different servers and when you replicate one database into the other, SQL Server will handle it). So, the RowGuid property of a column is independent from the primary key. Generally, when you use replication and a GUID pk, you can make them the same column.


Another good read - Can Sequential_GUID be the PK?