About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Tuesday, June 21, 2011

Identify SQL Server's Port Number

Just copy and paste the T-SQL code and execute to get the port number used by SQL server.
This is how it works.

•Determine which version of SQL server you are running
•Look at the specific location in the registery
•Display the port number

I have tested this script on the following versions
•SQL 2000 - 32 bit
•SQL 2005 - 32 and 64 bit
•SQL 2008 - 32 and 64 bit
•SQL 2008R2 - 32 and 64 bit

It is quick, simple and could be executed on develoopment and production servers as there
are not changes being made to SQL server and/or the Window's registery.
You will need to ensure you have rights to read the register in order for the script to
execute properly and must be executed on each instance (this version) to get the port number)

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DWGdOMWRZYXRGYjg/edit?usp=sharing
Posted by at No comments:

Friday, June 10, 2011

Rebuild SQL Server Master Databases in Less than 5 Minutes

Have you ever has an issues with a SQL server (2000, 2005, 2008 and so on) that you needed to rebuilt the master database? I've had that problem not too long ago and had to spend some time researching how this can be done. Here is a good site with the information you will need to rebuild the master database (http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx). But with a little work you can rebuild the master and the other system databases in less than 5 minutes. Let's see how this can be done.

It's All Proactive

To quickly rebuild the master and system database you need to have done some proactive work before you need to do a rebuild. Here is a quick and easy list of task to do proactively.
  1. Stop all SQL server services
  2. Copy the master.mdf and master.ldf files from the SQL server's data folder to another folder
  3. Restart all SQL server services
Now you have performed all you proactive tasks. A backup of these files exists in their native format. This is not a backup done via SQL server but a command line level or file level backup.

Time to Rebuild

So now what? Now when a master database has to be rebuilt due to corruption of some kind you can quick get back to a good state by performing the following.
  1. Stop all SQL server services
  2. Rename the master.mdf to master.mdf.old and master.ldf to master.ldf.old
  3. Copy the master.mdf and master.ldf files from your folder to the SQL server's data folder
  4. Restart all SQL server services
  5. Log into SQL server to ensure you can connect
  6. Perform a restore of the master database from your backups
At this point your SQL server will start up as normal and you can then continue to perform your additional tasks as needed.

Real World Example

Here is my real world example. At a disaster recovery test I had to build a SQL server 2008 environment and restore all of the SQL server's databases. This included all user and system databases. After the initial installation of SQL server 2008 I copied the master database files to a new folder I called 'D:\OrgFileBackup'. Then I performed the restore of the master database from our tape backups. During the restore an error occurred and the master database became corrupted. I could no longer log into SQL server nor restore any database. My only choice was to rebuild the master database. I performed the steps above and was able to connect to SQL server and perform my restores.
If I had to rebuild the master database or worse uninstall SQL server and reinstall SQL server completely the restore time for the whole environment would have taken a lot longer.

Rebuild Completed

This process is not intended to replace your backups but to enhance it. Now that you can log into SQL server you can perform a restore of the master database. You must restore from backups to get all the latest information back into the master database.
This process will not cure everything, but it is a faster way to rebuild your master database.

Hope you find this helpful,

Rudy
Posted by at No comments:
Subscribe to: Posts (Atom)