Friday, February 25, 2011

Issues in SQL Server 2005 installation

Here are some:

1) distmdl.mdf and dismdl.ldf are database files for distribution database used when we configure replication, donot delete them otherwise you wn't be able to install new service packs

2) you can move the system databases to any other drive very easily like this:

a) for tempdb, execute the below statements and then restart sql, that's it

alter database tempdb
modify file (name=tempdev,filename='f:\data\tempdb.mdf')

alter database tempdb
modify file (name=templog,filename='f:\data\tempdb_log.ldf')

 b) for msdb, model
execute the above alter db statements then stop sql and move the files to the new location and then start it that's it done


c) for master
stop sql server and copy the mdf,ldf files to the new location
In the startup parameters give the new paths for "-d" and "-l" options as for data and log files.
then start sql service, that's it

d) for resourcedb

stop sql server and copy the mdf,ldf files to the new location

start sql server with limited options like this in command prompt
"net start mssqlserver /f /T3608


Trace flag 3608 prevents SQL Server from recovering any database except the master database


then open query analyzer as only one connection is allowed and then execute the alter db statements


restart sql server and thta's it


Its actually very simple doing in 2005 where as in 2000 we have to start sql server in single user mode with options "-c" and "-m" and then detach and attach msdb,model dbs.

No comments:

Post a Comment