Showing posts with label move data file. Show all posts
Showing posts with label move data file. Show all posts

Thursday, June 7, 2018

Adding missing permission to a folder for the MSSQL$instance account

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

Friday, June 24, 2016

Move a user databases

To move a data or log file as part of a planned relocation, follow these steps:
  1. Run the following statement.
     
    ALTER DATABASE database_name SET OFFLINE;  
    
    
  2. Move the file or files to the new location.
  3. 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
    
  4. Run the following statement.
     
    ALTER DATABASE database_name SET ONLINE;  
    
    
  5. 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