Failed to update database "DATABASE" because the database is read-only.

The reasone may be any one of the following

1. The database is Read-Only (or)
2. The database is a Snapshot

How to identity whether the database is Read-Only, Snapshot and Source database of the snapshot ?

a) Read-Only:
SELECT DATABASEPROPERTYEX('DatabaseName','Updateability') 'Read Only'
Read Only

SELECT Is_Read_Only 'Read Only' FROM SYS.databases WHERE name = 'DatabaseName'
Read Only

b) Snapshot source:
SELECT CASE WHEN DB_NAME(source_database_id) IS NULL THEN 'Not a Snapshot' ELSE 'Snapshot Created from ' + '"' + DB_NAME(source_database_id) + '"' END 'Source DB Name' FROM SYS.databases
WHERE name = 'DatabaseName'

If the Database is not a Snapshot
Source DB Name
Not a Snapshot

If the Database is a Snapshot
Source DB Name
Snapshot Created from "SourceDatabaseName"

Note: Snapshot can not be created for Log file:
Log files, offline files, restoring files, and defunct files for database snapshots should not be specified. "Database_log" is not an eligible file for a database snapshot.

No comments:

Post a Comment