Is it possible to take a database offline when doing a backup using an SQL job?

I have the scenario where I have to generate a backup of the database (SQL Server 2008) and restore into the new server (SQL Server 2016). While taking backup data should not be changed in any case. So I have two options to do that, but I am not sure how it will work. (Everything is using SQL Job only.)

-
Set a read-only database and restore into the new DB server.

→ Is it possible to restore a read-only DB on the new server? The destination server is already having a read-write (online) database by the same name.

-
Set an offline database and restore onto the new DB server.

→ Is it possible to restore an offline DB in the new server? The destination server already has an online (read-write) database by the same name.

→ Is it possible to restore read-only DB in the new server?

Yes

An example:

CREATE DATABASE ReadOnlyDB;
GO

ALTER DATABASE ReadOnlyDB SET READ_ONLY;

BACKUP DATABASE ReadOnlyDB TO  disk = '\\share\readonly.Bak';

destination server is already having a Read-Write(Online) database by
the same name.

Not entirely sure what you mean by this, but you could restore the database with a different name if you would like to, remember to remove the read only property afterwards.

RESTORE DATABASE ReadOnlyDB2
FROM DISK = '\\share\readonly.Bak'
WITH MOVE 'ReadOnlyDB' to '\Datalocation\ReadOnlyDB2.mdf',
 MOVE 'ReadOnlyDB_log' to '\Loglocation\ReadOnlyDB_log2.ldf',
STATS = 5;
GO
ALTER DATABASE ReadOnlyDB2 SET READ_WRITE;

You could also replace the existing database with the REPLACE keyword.


→ Is it possible to restore offline DB in the new server?

No, this is not possible as sql server removes all handles from the database files and will not be able to access it while it is offline.

ALTER DATABASE ReadOnlyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
BACKUP DATABASE ReadOnlyDB TO  disk = 'C:\temp\readonlyOffline.Bak';

Msg 942, Level 14, State 4, Line 10 Database 'ReadOnlyDB' cannot be
opened because it is offline. Msg 3013, Level 16, State 1, Line 10
BACKUP DATABASE is terminating abnormally.

You could look into detach / attach but backup restore is preferable.

StackExchange Database Administrators Q#249465, answer score: 11