Cannot change the state of non-data files or files in the primary filegroup.

When we try to set a particular Data file OFFLINE, The following Error occurred

Msg 5077, Level 16, State 2, Line 1
Cannot change the state of non-data files or files in the primary filegroup.


Why the Err ?
1. The file should be a Data file.
2. The file should be there in seperate Filegroup otherthan PRIMARY.
3. Log file can not be set OFFLINE as It can not be placed in seperate filegroup otherthan PRIMARY.

To set Data file OFFLINE:
ALTER DATABASE <Database Name> MODIFY FILE(NAME='<Data File Name>',OFFLINE)

To identify the file state(OFFLINE/ONLINE):
USE <Database Name>
GO
SELECT
Name [File Name], Type_Desc [Data File Type],State_Desc [State] FROM SYS.MASTER_FILES WHERE NAME LIKE DB_NAME() + '%'

How to make the Data file ONLINE:
- Restore the backup file is the way to set the data file ONLINE
- Restore all the backup files (Full, Differential, Log(If any))

USE master
GO
RESTORE DATABASE <Database Name>
FILE
= N'<Data File1>',
FILE = N'<Data File2>' FROM DISK = N'<Backup file Path.Bak>'
WITH FILE = 1,
NORECOVERY,
REPLACE
GO
--
--
--
RESTORE DATABASE <Database Name>
FILE
= N'<Data File1>',
FILE = N'<Data File2>' FROM DISK = N'<Backup file Path.Bak>'
WITH FILE = 1,
REPLACE
GO

- All the Data file(s) changed to ONLINE.

2 comments:

  1. hi Pandian,
    i wan to print 1 to 100 in sql server with out using loops,tables and Union,
    please can you help me...

    ReplyDelete
  2. Hi

    You have to use UNION ALL instead...

    ;WITH CTEs
    AS
    (
    SELECT 1 [Sequence]
    UNION ALL
    SELECT [Sequence] + 1 FROM CTEs WHERE [Sequence] <100
    )
    SELECT * FROM CTEs

    Cheers

    ReplyDelete