April 7, 2011 at 9:43 am
Hi,
We have a SQL Server 2008 that was installed on a C:\ drive, and the person who installed it didn't point the Data to point to D:\ drive. I assuming that we have two options: One is to backup the DBs, reinstall SQL server and restore the DBs. Second is to backup the DB delete the DB and do a restre pointing the DB files to the D:\ Drive.
In looking though other SQL severs that is installed correctly which is pointing the data to the D:\ drive, the D:\ drive of the SQL Server directory has a lot of DLL files and other files.
My question is that what is the disadvantages of not reinstalling the SQL server and just pointing the Data file to the D:\ drive?
Thanks,
April 7, 2011 at 4:56 pm
what exactly do you want moved to the D drive? the system databases, all databases, or the binaries as well (e.g. the binn directory)
---------------------------------------------------------------------
April 7, 2011 at 10:41 pm
The disadvantage is that you have a server that is not installed like what appears to be the company standard. Also, some malware looks for items to be installed in default locations such as c:\program files. By changing that, sometimes you can avoid the harms of certain malware.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 8, 2011 at 1:55 am
EjSQLme (4/7/2011)
I assuming that we have two options: One is to backup the DBs, reinstall SQL server and restore the DBs. Second is to backup the DB delete the DB and do a restre pointing the DB files to the D:\ Drive.
stop right there!
EjSQLme (4/7/2011)
My question is that what is the disadvantages of not reinstalling the SQL server and just pointing the Data file to the D:\ drive?Thanks,
Why do you want to re install, pointing SQL Server to the D: drive is exactly what you need to do.
Regardless of where you install SQL Server a certain amount of the install will be performed to the system drive (C:).
Create your folder paths on the D: drive
Open SQL Server properties and on the Database tab set the default paths for data files and log files
For each file in each database you wish to move, execute an ALTER DATABASE MODIFY FILE statement. Query sys.master_files for the current locations
For example your c: drive path is
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
I would create
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Then use the following code to generate the move file commands for all the user databases
select 'ALTER DATABASE ' + QUOTENAME(DB_NAME(DATABASE_ID)) + ' MODIFY FILE (NAME = ' + NAME +
', FILENAME = ''' + REPLACE(PHYSICAL_NAME, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data', 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data') +
''')' from sys.master_files
WHERE DATABASE_ID > 4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 8, 2011 at 6:12 am
george sibbald (4/7/2011)
what exactly do you want moved to the D drive? the system databases, all databases, or the binaries as well (e.g. the binn directory)
I want to move all databases to the D drive, so if the binn directory and other SQL Server DLL files are left on the C drive, what is the disadvantage?
April 8, 2011 at 6:19 am
Perry, thank you for the instruction. I'll try that and report back the results.
April 8, 2011 at 7:48 am
EjSQLme (4/8/2011)
george sibbald (4/7/2011)
what exactly do you want moved to the D drive? the system databases, all databases, or the binaries as well (e.g. the binn directory)I want to move all databases to the D drive, so if the binn directory and other SQL Server DLL files are left on the C drive, what is the disadvantage?
sounds like you would have a non-standard install for your shop, SQL binaries would compete with OS, if your system databases are on the C (sounds like they are) definite performance issues and possible space issues.
If this installation has not gone live yet I would re-install.
If that is out of the question move the system databases, especially tempdb:
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.100).aspx
and then user databases as perry describes using alter database.
you should look at moving the sql errorlogs and sqlagent logs as well.
---------------------------------------------------------------------
April 8, 2011 at 3:01 pm
My advice: stick to the default settings for the install files.
We have had an issue with our cluster installations, where we were also installing on D:
The logship.exe file, used for transaction-log backups, did not get deployed properly: it was installed in C: in the passive node, because the sql install does not provide user the ability to specify an alternate location for the passive node install. We did not realize that until there was a failover and our tran-log backups started failing.
Definitely move tempdb out of the C: drive.
master, msdb can stay in C:, but it is better to place them on a data drive.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 8, 2011 at 3:17 pm
Marios Philippopoulos (4/8/2011)
We have had an issue with our cluster installations, where we were also installing on D:The logship.exe file, used for transaction-log backups, did not get deployed properly: it was installed in C: in the passive node, because the sql install does not provide user the ability to specify an alternate location for the passive node install. We did not realize that until there was a failover and our tran-log backups started failing.
This is fixed in an update for SQL Server 2008 😉
Slipstream the update at install time and you'll be golden
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply