how to move resource database

Question

how to move resource database

crazy4sql

SSCoach

Points: 19590
More actions
July 22, 2011 at 8:07 am

#390034

hi all,
due to space constraint I need to move all the database to new disk and it included system database as well.
I am planning to do this activity by detach/move or copy/attach.
Can someone please help me how to move resource database in sql 2008. So far I am aware that resource database cant be moved. But my concern is what will happen to resource database if it cant be moved and disk is replaced with new disk? Is the only option left for me to do fresh sql installation and then restore system database from latest backup?
----------
Ashish

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Don't. Move it and SQL will break. In 2008 it has to be treated like any of the program dlls.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy4sql SSCoach Points: 19590 More actions · Answer 2

Thanks Gail, so what the best approach you suggest?

----------
Ashish

Lowell SSC Guru Points: 323518 More actions · Answer 3

resource database is small to begin with;

i'd look at whatever is eating up your disk space.

where are the backup files being created?

look for log files from traces you forgot about (i've seem c2 Tracing get enabled and forgotten, producing gigs of log files).

did you already move all your user databases? did you move tempdb?

spacemonger is a cute program that can help you visually see what eats up dis space...i've had left over unzipped files from ETL's eat space up as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

crazy4sql (7/22/2011)
Thanks Gail, so what the best approach you suggest?

Move the user and system databases where ever you like.

Unless you're planning to move things like sqlservr.exe and it's related executable files and resource files, leave resource DB. It should be treated exactly like one of the application dll files (and no one who knows anything about windows goes randomly moving dll files around)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy4sql SSCoach Points: 19590 More actions · Answer 5

Unless you're planning to move things like sqlservr.exe and it's related executable files and resource files, leave resource DB. It should be treated exactly like one of the application dll files (and no one who knows anything about windows goes randomly moving dll files around)

Thanks. So if team move all the data from old disk to new disk, the resource database will still behave like it was working earlier?

PS:- System database with resource are on same disk which need to replaced. Disk is not getting expanded but being replaced with new more storage disk.

----------
Ashish

crazy4sql SSCoach Points: 19590 More actions · Answer 6

i'd look at whatever is eating up your disk space............

Thanks Lowell, the issue is not because of performance but because of having data and log file spread across the system. Need to maintain them in their dedicated data, log drive. This is Quiet old system, I am just giving new look to look better.

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

crazy4sql (7/22/2011)
PS:- System database with resource are on same disk which need to replaced. Disk is not getting expanded but being replaced with new more storage disk.

In 2008 the resource DB does not sit with the system databases.

Books Online:

Moving the Resource Database
In SQL Server 2008, the location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. The database cannot be moved

http://msdn.microsoft.com/en-us/library/ms345408%28v=SQL.100%29.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy4sql SSCoach Points: 19590 More actions · Answer 8

In 2008 the resource DB does not sit with the system databases.

that's correct, just verified it.I should have checked it first.

Thanks Gail for all your help and reply. You saved one more life today. 🙂

your reply are more accurate than BOL for me

----------
Ashish

crazy4sql SSCoach Points: 19590 More actions · Answer 9

I have got much simpler process for moving the data files from old disk to new disk.

1) Stop the sql services.

2) Copy the files from old disk(a) to new disk(b)

3) rename the old disk to something else(c)

4) rename the new disk(b) to exact of your old disk(a)

5) start the sql service.

PS :- there might be more than one disk invlived if data/log/backup/tempdb are configured on separate drive. but after movement new disk must match with old name before starting the sql service.

----------
Ashish