Azure SQL Database: Clean or restore database via SQL script

Question

We have a windows on-premise scheduled task that we need to migrate to Azure Paas.

The scope of the task is to restore a clean version of the same database daily to improve site's performance.

The task action is to run a batch file, with the below script.

SQLCMD -E -S server-name -Q "use master; alter database myDatabase set 
single_user with rollback immediate; alter database myDatabase set 
multi_user; RESTORE DATABASE myDatabase FROM DISK='C:\Dir\myDatabase.bak'; 
USE myDatabase; CREATE USER myUser FOR LOGIN myUser; USE myDatabase; ALTER 
ROLE db_owner ADD MEMBER myUser"

The requirement is to implement a similar functionality by creating an Azure C# WebJob project via Visual Studio and publish it as an Azure WebJob.
Only the schema of the database should remain, no data or logs.

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["AzureDBConnString"]))
    {
        connection.Open();

        var queryString_RestoreMyDatabase = @"?????"; // what to add here?

        using (SqlCommand cmd_RestoreMyDatabase = new SqlCommand(queryString_RestoreMyDatabase, connection))
        {
            cmd_RestoreMyDatabase.ExecuteNonQuery();
        }
    }

Is the above possible? What SQL commands can we use for queryString_RestoreMyDatabaseto achieve as per title of this post?

David Browne - Microsoft · Accepted Answer · 2018-11-24 21:04:06Z

1

Instead of RESTORE, you can copy a database:

-- Execute on the-- Execute on the master database.
-- Start copying.
CREATE DATABASE Database2 AS COPY OF Database1;

Copy an transactionally consistent copy of an Azure SQL database

To replace a database with a copy of an existing database you could start with something like this:

if  exists (select state from sys.databases where name = 'test_new')
begin
  drop database test_new;
end

create database test_new as copy of test_template;
while (0 != coalesce((select state from sys.databases where name = 'test_new'),-1 ))
begin
  waitfor delay '0:0:10'
end

alter database test modify name = test_old;
alter database test_new modify name = test;
drop database test_old;
print 'completed sucessfully'

edited Nov 24, 2018 at 21:04

answered Nov 24, 2018 at 18:43

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alex Over a year ago

Thank you David, what about replacing the existing database, will this require creating a new database that does not exist having a different name and what about the references in code to the current database?

alex Over a year ago

Thank you, David, for your time, it works. Noticed that the script takes ~ 6 minutes to complete, assuming this is normal? considering a new database needs to be created?

alex Over a year ago

Do you know what will the costs be in Azure to run this once every day. Asked a new question here: stackoverflow.com/questions/54460615/…

Collectives™ on Stack Overflow

Azure SQL Database: Clean or restore database via SQL script

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related