Sql-server – Move database to another drive in same server

sql server

I have installed MSSQL server 2008 in my server in C: drive and I want to move it to the D: drive completely. That means, database locations, installed location to move another drive with data. Is it possible to done this?

Note: Detach method is only for move database. but I want to move whole installation and DB files to another drive.

Best Answer

It is easy and well documented to move data files (including master, model, tempdb,...) to other locations in MS-SQL Server. There is no supported approach to move the instalation (SQL Server binaries) to another drive / folder. I think even with manual manipulation of registry entries the SQL Server wouldn't come up again.

You should consider uninstall and new installation.

backup all databases including system databases as master, model ( SSISDB)
uninstall SQL Server using SQL Server installation center
uninstall SQL Server 2008 Browser component by using the Add or Remove Programs
install SQL Server with same instance name using SQL Server installation center; select new locations for binaries and data
restore backups

This article describes how to uninstall a stand-alone instance of Microsoft SQL Server 2008. When you follow the steps in this article, you also prepare the system so that you can reinstall SQL Server 2008

Related Solutions

Sql-server – Moving database file locations in SQL Server 2008

It's likely that all you need to do is issue the following command for each user database that was moved:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

I grabbed that line of code from these instructions published by Microsoft. So read those too.

Sql-server – Move named instance to another server

You are not moving instances, you are copying databases via backup restore. All you need is to get the syntax right, as per RESTORE spec:

<general_WITH_options> [ ,...n ]::= 
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
          [ ,...n ]

Each WITH option is separated from the preceding with a comma. What you're missing is the , and you need to move the files into the destination location:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE
 , MOVE 'Backup_Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test.mdf',
 , MOVE 'Backup_Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test_log.ldf'

Judging from your folder names (MSSQL10 vs. MSSQL10_50) your source server is SQL Server 2008 (not R2) and only the destination is SQL Server 2008 R2. Which means that the copy will upgrade the database and the database will never be possible to copy/restore on the original non-R2 instance.

Best Answer

Related Solutions

Sql-server – Moving database file locations in SQL Server 2008

Sql-server – Move named instance to another server

Related Question