April 6, 2013 at 3:27 am
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?
April 6, 2013 at 5:40 am
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
April 6, 2013 at 12:16 pm
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.
---------------------------------------------------------------------
April 8, 2013 at 7:28 am
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" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply