Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving MSDB database Expand / Collapse
Author
Message
Posted Saturday, April 6, 2013 3:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 269, Visits: 1,730
When i am trying to shift msdb database from it's default path(C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA) to another drive(E:\). After shifting the database i am getting error as shown below.(the error i am getting while expanding database node in SSMS an d now i am not able to access any database.)

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

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


The procedure i followed to shift msdb database is as follows:

1. For each file to be moved, run the following statement.

ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSDBData.mdf' )
ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBLog , FILENAME = 'E:\MSDBLog.ldf' )

2. Stop the instance of SQL Server to perform maintenance.
3. Move the file or files to the new location.
4. Restart the instance of SQL Server or the server.
Also i did confirm the path by running following query

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'msdb');

And output was showing current location of both MSDBdata and MSDBLog as E:\


This error happened while i did it for testing server. I want to do it for a production server. So please help me on this error. How exactly to shift msdb database?
Post #1439560
Posted Saturday, April 6, 2013 5:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 223, Visits: 1,725
Probably the point 3 is an issue "Check if the account which is trying to access the database has enough permission to perform operation." if not check other points
http://blog.sqlauthority.com/2007/08/02/sql-server-fix-error-945-database-cannot-be-opened-due-to-inaccessible-files-or-insufficient-memory-or-disk-space-see-the-sql-server-error-log-for-details/
Post #1439572
Posted Saturday, April 6, 2013 12:16 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
I'm with ed44 on this, very likely to be permissions (OS error 5).

check your SQL server errorlog for more information on the actual cause of the error.


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

Post #1439594
Posted Monday, April 8, 2013 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 6,350, Visits: 13,675
As a default, only administrators will have ACLs to read and write files to the root of a drive. Create a folder and give the database engine sufficient permission, then move the files using the sequence you have already performed

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1439818
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse