Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Problem With Moving MSDB?
Add to briefcase

Problem With Moving MSDB? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Konuri Dinesh
Posted Saturday, October 06, 2012 3:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 06, 2012 3:38 AM
Points: 23, Visits: 20
Hey Pals!

I moved My MSDB database to another location. after that i tried to start my server but unfortunately it can't

i done it by following steps

step:1
SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

step:2
SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.ldf'
)
GO

step:3 Stoped SQL Server service
Step 4: moved MSDB database to new location
Step 5: started SQL Server service.

but i got error 1814 here



Guys could you please help me?
Post #1369402
bitbucket-25253
Posted Saturday, October 06, 2012 11:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 5,121, Visits: 20,359
Did you move the RESOURCE data base ?

Read this it may be of assistance:
http://www.mssqltips.com/sqlservertip/1544/sql-server-backup-and-restore-of-the-resource-database/

You can also try moving the master database on a different location without moving the Resource database together with it and you will not be able to start the service.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1369450
Perry Whittle
Posted Saturday, October 06, 2012 3:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 5,242, Visits: 11,255
Konuri Dinesh (10/6/2012)
Hey Pals!

I moved My MSDB database to another location. after that i tried to start my server but unfortunately it can't

i done it by following steps

step:1
SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

step:2
SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.ldf'
)
GO

step:3 Stoped SQL Server service
Step 4: moved MSDB database to new location
Step 5: started SQL Server service.

but i got error 1814 here



Guys could you please help me?

Can you post full details of the error messages

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1369471
Mac1986
Posted Sunday, October 07, 2012 1:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 180, Visits: 516
you might want to be a little careful while playing around with sys databases. If msdb is not online and you recycle the SQL services, your tempDB will also be not created because validations and procedures to build TempDB again are all in MSDB only.

Please let us know what were the error logs when you moved the mdf and ldf files.

I would say restore the .bak file with move option it would easy.
Post #1369506
chandan_jha18
Posted Tuesday, October 09, 2012 1:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 415, Visits: 1,689
Konuri Dinesh (10/6/2012)
Hey Pals!

I moved My MSDB database to another location. after that i tried to start my server but unfortunately it can't

i done it by following steps

step:1
SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

step:2
SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.ldf'
)
GO

step:3 Stoped SQL Server service
Step 4: moved MSDB database to new location
Step 5: started SQL Server service.

but i got error 1814 here



Guys could you please help me?


Please post entire error logs from eventviewer from that time stamp. Often this is related to security because the service account might not have permission on the new location. Please post all the logs from that time stamp.

Chandan
Post #1370208
Ratheesh.K.Nair
Posted Tuesday, October 09, 2012 2:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 2,425, Visits: 3,401
To which location you moved the files?? Are they the same location where tempdb files resides?

After moving to the new location are you sure you have sufficient space under that drive?
Post #1370212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.