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

Question

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.)

1. 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.

2. 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.

Answer 1

→ 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.

Answer 2

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

Answer 3

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.