Xjyuk

I have the scenario where I have to generate a backup of the database(MSSQL-2008) and restore into the new server (MSSQL-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)

1. 
   

   Set Read-Only database and restore into new DB server.

   
   
   

   → Is it possible to restore read-only DB in the new server? The destination server is already having a Read-Write(Online) database by the same name.

   
   


2. 
   

   Set Offline database and restore into new DB server.

   
   
   

   → Is it possible to restore offline DB in the new server? the destination server is already having Online (Read-Write) database by the same name.

   
   

If anyone can share his view on this, Thank you in advance.

→ 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 = '\sharereadonly.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 = '\sharereadonly.Bak'
WITH MOVE 'ReadOnlyDB' to 'DatalocationReadOnlyDB2.mdf',
 MOVE 'ReadOnlyDB_log' to 'LoglocationReadOnlyDB_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:tempreadonlyOffline.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.

Yoy have a couple of options in your situation:

1. Turn off applications that can change data in the database


2. Set the database in single user mode and back it up. Something like this:

ALTER DATABASE [<DBName>] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BACKUP DATABASE [<DBName>] TO DISK = N'<locationAndFilename>'

Then restore it and redirect the app to the new server

I am not fully understanding your situation. There are a couple of things not mentioned in the question or existing answers.

While taking backup data should not be changed in any case.

Why? - Any changes that are made during the backup are captured and included in the backup. So if it takes 2 hours for the backup to complete, all the changes made during that 2 hours will be included in the backup. Both will match as of the instant the backup completes.

Assuming (opposite of above) that you want a backup that is effective as of a start time, rather then an end time. And as you mentioned setting the database offline, I assume you are fine with no one having access during the backup.

If there is only one database on the server, use a job step to disable their logins at the server, when the backup is complete, re-enable.

 USE [master]
 EXEC asp_kill_user_connections [myDatabase] --This is sp that kills all user connects to a database, it is in house and I don't have the rights to share its code, but you can make your own. 

 USE master
 REVOKE CONNECT SQL TO [AD/SomeGroupAccount]
 ALTER LOGIN [SomeSQLAuthAccount] DISABLE
 GO

Do your backup, and when it is done give them access again.