6

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.

11

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

5

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 suggest doing SINGLE USER mode, maybe at the end. or simply change the login password or disable logins Either way, backup will backup to the end of backup operation so all changes are captured (e.g. if you start at 1pm, takes 1 hour to backup finishing at 2 pm, your backup will contain everything until 2 pm anyway) – Jerry Hung Sep 24 at 8:44
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.

  • solution looks interesting, but are you sure no service account user or SQL agent user will not be able to login or update the database using SQL jobs even??? – Irfan Sep 29 at 10:44
  • @Irfan User accounts are pretty simple, if you don't want them in revoke or disable their access, BUT be sure to kill their existing connections, you are preventing them from making new connections. As for SQL Agent, you should have control over that. It only does things to the database that you tell it to. If there is a job scheduled to run during your backup, disable it or reschedule it. This can be done as step in your backup job, remember to re-enable as final steps in your backup job. – James Jenkins Sep 30 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.