SQL Server – Taking Database Offline During Backup with SQL Job

backupsql serversql-server-2008-r2sql-server-2016

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.

Best Answer

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

Related Question