SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other


Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):

1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives

There can be even more secnarios like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command

Let us take the below scenario for step-by-step Demo:

Database: AdventureWorks2012
Current Files Location: C:Disk1
NewTarget Files Location: C:Disk2

Step 1:Get the current database files Logical Name and Physical Location

USE master
SELECT name AS LogicalFileName, physical_name AS FileLocation
, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = 

View original post 229 more words


Rebuild indexes SQL Server 2000

Lets say you want to rebuild indexes for a particular table in SQL Server 2000. Then you can generate the script to rebuild the indexes using the below command in Query Analyzer. Where xxx is the name of the database who indexes you want to rebuildSELECT 'dbcc dbreindex(''xxx.DBO.' +name+ ''')' FROM sysobjects WHERE xtype='U' 

Sql server 2008 database mirroring manual failover

In case your mirroring has been suspended and you have tried everything else and nothing works. Your last option is to break the mirroring and restore the mirror database and connect the client application to the mirror site. Before proceeding with this please note that doing this will require you to rebuild the Data Mirroring … Continue reading Sql server 2008 database mirroring manual failover