I had to move the data files from one driver to another. After moving the data files when I brought the database online it gave"Access denied" error and I found out that the reason was that the MSSQL$instance account did not have permission to the data file.
I right clicked on the folder and on the security tab tried to add the user to give the missing permission but could not locate the user. I had to do the following to find the user in the user list
To add the missing permissions
chose the local computer and type in "NT SERVICE\MSSQL$instance
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label move data file. Show all posts
Showing posts with label move data file. Show all posts
Thursday, June 7, 2018
Friday, June 24, 2016
Move a user databases
To move a data or log file as part of a planned relocation, follow these steps:
- Run the following statement.
ALTER DATABASE database_name SET OFFLINE;
- Move the file or files to the new location.
- For each file moved, run the following statement.
USE master GO ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Data, FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path USE master GO ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Log, FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path
- Run the following statement.
ALTER DATABASE database_name SET ONLINE;
- Verify the file change by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');
Wednesday, July 23, 2014
tempdb modify size, move file
Modify the size
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
The database will be resized when the server restarts next.
Move the file
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\xxxx\tempdb\file\tempdb.mdf' /*, SIZE = .... */ ) the file will be created in the new path when the server starts next
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
The database will be resized when the server restarts next.
Move the file
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\xxxx\tempdb\file\tempdb.mdf' /*, SIZE = .... */ ) the file will be created in the new path when the server starts next
Labels:
Administration,
filesize,
MODIFY FILE,
move data file,
tempdb
Subscribe to:
Posts (Atom)