Changing databases default locations

By default the location of SQL Server 2005 databases files is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

Changing user databases default locations

After installing SQL Server 2005 you can change the default location for all user databases that will be created on the server, to do it you have just to modify the database default locations on the server property page.

or by executing the code above :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'E:\UserDB'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'E:\UserLOG'
GO

Changing system databases default locations

master database

To move the master database files you have to perform the following steps:

1. Change the startup parameters of SQL Server service using SQL Server Configuration Manager
2. 
3. change the path of master.mdf to the new path inside the –d flag
4. change the path of mastlog.ldf to the new path inside the –l flag
5. net stop mssqlserver
6. move the files from the old location to the new location
7. net start mssqlserver       

msdb database

To move the master database files you have to perform the following steps:

1. run a command prompt (cmd)
2. net stop mssqlserver
3. net start msqlserver /c /m /T3608
4. run SQLCMD (be sure that all other SQL Server Services are stopped and that there is no application that will try to connect to SQL Server) 
5. on the Sqlcmd prompt execute the following script : 

   use master
   go
   sp_detach_db 'msdb'
   go




6. move the msdbdata.mdf and msdblog.ldf files to the new location



7. net stop mssqlserver



8. net start mssqlserver



9. execute the script
   
   
   

   use master
   go 
   sp_attach_db 'msdb','<new location>\msdbdata.mdf','<new location>\msdblog.ldf' 
   go

tempdb database

To move tempdb database  :

1. Execute the following SQL script :   USE master
   
   GO
   
   
   ALTER DATABASE tempdb
   
   
   MODIFY FILE (NAME = 'tempdev',FILENAME = '<new location>\tempdb.mdf')
   
   
   GO
   
   
   ALTER DATABASE tempdb
   
   
   MODIFY FILE (NAME = 'templog',FILENAME = '<new location>\templog.ldf')
   
   
   GO
   



2. net stop mssqlserver



3. move tempdb.mdf and templog.ldf files to the new location



4. net start mssqlserver

model database

to move model database :

1. run a command prompt



2. net stop mssqlserver



3. net start mssqlserver /c /m /T3608



4. run SQLCMD



5. detach the model database : 

   use master
   go
   sp_detach_db 'model'
   go




6. move the model.mdf and modellog.ldf files to the new location



7. attach the model database :
   

   use master
   go
   sp_attach_db 'model','<new location>\model.mdf','<new location>\modellog.ldf'
   go




8. net stop mssqlserver



9. net start mssqlserver

SSRS 2005 Password Updates

When you change the AD password of the user running the SQL Server Reporting Services 2005 you will eperience probelm to restart the SSRS 2005.
What you have to di is :
- Update the password on the service properties under windows services console
- Update the password by using the SSRS configuration tool because the password is also stored internally on the SSRS configuration.

SQL 2008 with Visual Studio 2008 setup failed !!!

Some SQL Server 2008 features install components of Visual Studio 2008 SP1 and because VS 2008 does not support the execution of VS 2008 and VS 2008 SP1 on the same computer; the setup program of SQL Server 2008 will not be able to complete due to this rule.
to resolve this issue you have to do one of the following actions :

• install Visual Studio SP1 before SQL Server 2008
• uninstall Visual Studio 2008, install SQL Server 2008 than Visual Studio 2008 and Visual Studio 2008 SP1
• install SQL Server 2008 without the following components
• Management Tools
• Integration Services
• BI Development Studio

for more details click here.

Can’t Enable Service Broker On MSDB !

After restoring MSDB database to a different server, you may experience a problem configuring Database Mail feature due to the fact that Service Broker is not enabled on MSDB database and you will not be able to enable it.
Resolution :
to resolve this issue you have to create a new broker by executing the script below :
ALTER DATABASE [MSDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE
Go
ALTER DATABASE [MSDB] SET NEW_BROKER
Go
ALTER DATABASE [MSDB] SET ENABLE_BROKER
Go

Windows 2008 R2 Support

Before upgrading your operating systems to Windows 2008 R2 you have to know which versions of products are supported on this platform.

1. SQL Server
   1. SQL Server 2005 Service Pack 3 and later
   2. SQL Server 2008 Service Pack 1 and later
   3. SQL Server 2005 Express Edition Service Pack 2
   4. SQL Server 2008 Express RTM
   5. SQL Server 2008 R2 will be supported in H1 2010
2. Exchange
   1. Exchange 2010 and later version will be supported Q4 2009
3. Office Servers
   1. Forms Server 2007 Service Pack 2 and later
   2. Groove Server 2007 Service Pack 2 and later
   3. PerformancePoint Server 2007 Service Pack 2 and later will be supported Q1 2010
   4. Project Server 2007 Service Pack 2 and later
   5. SharePoint Server 2007 Service Pack 2 and later              
   6. Search Server 2008 Service Pack 2 and later
   7. Search Server 2008 Express Service Pack 2 and later

for a complete lit of supported products see Microsoft Server Applications Supported on Windows Server 2008 R2

SQL Server 2008 : Using Alerts to prevent Log disks to go out of space

The size of transaction log file will increase indefinitely if the recovery model of the database is set to Full and we don’t perform Log backups periodically.
With SQL Server Alerts and Jobs we can automate log backups.

• Create a backup device to store backups
• From object explorer develop Server Objects
• Right click Backup Devices
• Click New Backup Device

 

• Give a name and a filename to the backup device

• Click Ok
• Create an operator to receive notification
• Right click Operators folder under SQL Server Agent
• Click New Operator

• Fill the fields of the General tab with the required information

 

• Click Ok
• Create a new Job to backup the transaction log
• Right click Jibs folder under SQL Server Agent
• Click New Job

• Name the job on the General tab

• Create a Transact-SQL Script step on the Job with the script : BACKUP LOG ‘DataBaseName’ TO ‘BackupDeviceName’

• On the Notification tab choose the operator to be notified

• Create an Alert based on the performance counter Percent Log Used
• Right click Alerts folder
• Click New Alert        

• Configure the Alert with the following values
• Name : ‘AlertName’
• Type : SQL Server Performance Counter Alert
• Object : SQLServer:Databases
• Counter: Percent Log Used
• Instance: ‘Database Name’
• Alert if counter : Is greater than
• Value : ‘Type a value < 100’

• On the Response tab choose Execute Job as response, select the Job created on the previous steps and select the operator to be notified.