DBA and SysAdmin World

December 9, 2008

How to Get SQL Server Version?

Filed under: SQL Server — Tags: , — solihinho @ 3:34 AM
Computer_time_to_upgrade Last time I faced problem with Sharepoint 2003 search. It is related to SQL Server 2000 Full Text Catalog. When I tried to get support from Microsoft, they do not support SQL Server 2000 SP3 anymore. Because of this cause, finally we upgraded our SQL Server 2000 to SP4. Yes, there are some problems after upgrading but my Sharepoint problem resolved by this upgrading and I am able to get support from Microsoft again if there is an issue on my database server in the future.

Nerd Before apply Service Pack, do not do it directly on production environment. Do it on development and testing environment first if possible. Find out other DBA issues from forum and prepare the solution. Prepare the worst case to downgrade the Service Pack ( usually the SQL Server need to reinstall)

Below script is to get version of the SQL Server

   SERVERPROPERTY('servername') AS ServerName
,  SERVERPROPERTY('edition') AS Edition
,  SERVERPROPERTY('ProductVersion') AS ProductionVersion
,  SERVERPROPERTY('ProductLevel') AS ProductLevel

Values return on ProductLevel:

1. ‘RTM’ : Original Release Version
2. ‘SPn’ : Service Pack Version
3. ‘Bn’ : Beta Version

December 8, 2008

Moving Database File To Another Disk

Filed under: Script, SQL Server — Tags: , , — solihinho @ 3:09 PM
moveAll our databases server using SAN (Storage Area Network) for the disk storage. Last time we used DELL AX100 for the storage. After that we upgraded to DELL MD3000.  DBA was asked to move the databases to the new disk.

We have hundred of databases on one server. The best way to do this is using detach and attach method. In this case, we want to move all of user databases, so what we did is just detach all databases, stop the SQL Server, move the data file to the new disk, start the SQL Server and attach all the data files. We need downtime quite long to copy the files to new storage. If you does not allow to downtime quite long, just detach and attach the databases one by one. In my case, it is fine to downtime about 1 hour Winking.

What I want to share is a script to generate T-SQL for detaching and attaching the database. Can you imagine if we use Enterprise Manager to detach and attach thousand or hundred databases ?

Step by step to move the data file

1. Before you stop the SQL Server, run below script to generate attach script for all user databases.


DECLARE @dbname         SYSNAME
DECLARE @sqlstr         VARCHAR(4000)
DECLARE @filename       VARCHAR(4000)
DECLARE @result         VARCHAR(8000)
DECLARE @Counter        TINYINT

IF OBJECT_ID('tempdb..#db') IS NOT NULL

--exclude system databases
SELECT name INTO #db
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','tempdb','msdb')

IF OBJECT_ID('tempdb..#files') IS NOT NULL
   DROP TABLE #files

   DbName       SYSNAME,
   LogicalName  SYSNAME,
   FileId       INT,
   FileName     VARCHAR(1000),

IF OBJECT_ID('tempdb..#result') IS NOT NULL
   DROP TABLE #result

   sql VARCHAR(8000)

--get all files
WHILE (1 = 1)
   SET @dbname = NULL

   SELECT TOP 1 @dbname = name FROM #db	

   IF @dbname IS NULL

   SET @sqlstr =  ' USE ' + @dbname
                + ' INSERT INTO #files (DbName, LogicalName, FileId, FileName)'
                + ' SELECT  db_name(), name,  fileid, filename '
                + ' FROM sysfiles  '
                + ' ORDER BY fileid ' 

   EXEC (@sqlstr)

   DELETE FROM #db WHERE name = @dbname


--generate attach script
WHILE (1 = 1)
   SET @dbname = NULL

   SELECT TOP 1 @dbname = DbName
   FROM #files
   ORDER BY DbName 

   IF @dbname IS NULL

      SET @Counter = 1
      SELECT @NFiles = COUNT(*) FROM #files WHERE DbName = @dbname

      SET @result = 'exec sp_attach_db N''' + @dbname + ''''
      WHILE (@Counter <= @NFiles)
         SELECT @Filename = FileName
         FROM #files
         WHERE DbName = @DbName AND FileId = @Counter

         SET @result = @result + ',@filename' + CAST(@counter as VARCHAR)
                               + ' = N''' + RTRIM(@FileName) + ''''		

         SET @Counter = @Counter + 1

      INSERT INTO #result
         SELECT @result

      DELETE FROM #files WHERE DbName = @dbname

      SELECT * FROM #result

      DROP TABLE #db


This is an example of the result of above script for AdventureWorks and AdventureWorksDW.

exec sp_attach_db N'AdventureWorks',@filename1 = N'D:DataAdventureWorks_Data.mdf'
                                   ,@filename2 = N'E:LogAdventureWorks_Log.ldf'
exec sp_attach_db N'AdventureWorksDW',@filename1 = N'D:DataAdventureWorksDW_Data.mdf'
                                     ,@filename2 = N'E:LogAdventureWorksDW_Log.ldf'
Pumpkin In my local PC, I put all data files (*.mdf, *.ndf) in D:DATA and log files (*.ndf) in E:LOG

Keep this result to another text file

2. Detach All User Databases
Run below query than run the result of this query to detach all user databases. To remainder, detach process will fail if there is an application that using this database. Just make sure there is no process hold this database if detach process is failed. If you detach database using Enterprise Manager, you will see how many connection that holding specified the database. Just clear the connection if you want to detach that database immediately without want to wait the process is done.

--exclude system databases
SELECT replace('exec sp_detach_db ''@dbname'',''true''','@dbname', name) AS result
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')

3. Stop SQL Server Database Engine

4. Move all data files and log files to the new disk

5. Start SQL Server Database Engine

6. From result of step 1, change old path with new path e.g.: D:DATA be F:SQLDATA than run this script to attach back all databases.

November 5, 2008

How to Change Mac Address on Windows

Filed under: System Administrator — Tags: , — solihinho @ 11:55 PM
ipconfig What is MAC Address?
Media Access Control Address (MAC Address) according to wikipedia is a quasi-unique identifier assigned to most Network Interface Card (NIC). This address is commonly written as 12 digits Hexadecimal e.g. : 00-12-3F-3B-B2-51. To check your mac address, from Windows Command Prompt type ipconfig/all. Look at on Physical Address section. You will find 12 digits hexadecimal.

Why I Need to Do This?
In my case, I need to change my computer MAC Address because the Internet Service Provider only permit computer with MAC Address already registered able to connect to Internet. If I need to swap between my computers, I have to call the call center and ask them to flush it. It would be a lot easier if I have the same MAC Addresses between my PC and laptop.

Step by Step
To be able change the MAC Address, it depend on the type of the NIC that you have. I can do it on my laptop but not on my PC. The friend of mine told me the step by step to do this.

  1. go to Control Panel
  2. double click on Network Connections
  3. right click on the NIC that you want to change (in case you have more than 1 NIC), click Properties
  4. Under General tab, click Configure button
  5. click on Advanced tab
  6. on the Property list, just look for Network Address item
    If this item doesn’t appear, probably the NIC doesn’t support to change the MAC Address value.
    But if you can see this item, click on it. Just type the value that you want on the value textbox without “-” between MAC Address number e.g.: 00123F3BB251 for 00-12-3F-3B-B2-51
  7. check the MAC Address with ipconfig/all
  8. if successful, reboot your computer

Blog at WordPress.com.