Tag Archives: Restore

by

I got lucky with an Azure database restore…

Recently, the wrong table got dropped and we needed to bring it back. I had never done a restore in an Azure Managed Database before so I learned something really fast.

First, I log into the Azure Portal (portal.azure.com)

I select “SQL servers”:

I pick the right server that my managed database is on (it has the type of “SQL server”) and I click on the name of the server (it is a link to where I need to go)

In the next screen under Settings, I select SQL databases:

I click on the name of the database that I need to restore. It will take me to another screen.

It gives me this list of options at the top (this is also where I would go to create a copy of a database for another environment like dev).

I select “Restore” and it takes me to another screen where it has most of the restore ready to go. Here is where I can pick a point in time recovery or the most recent possible. Since I knew about the time the delete had happened, I am going to restore to just a few minutes before the delete. (It will be in UTC time) Most recent would have not have what I need because it took the user a little bit of time to let me know what had happened.

I can name what the database is going to be, I can add a date or time to this name so I know when it was created.

I set my storage for the cheapest options because this is just a restore that I am going to pull data from back into my current database. I won’t be keeping it long.

I select review + create at the bottom and it will start restoring my database. Depending on the size, it might take time, but it was pretty quick for me.

I pull the data back into the table that I need and then go back to the SQL Databases screen and find my newly created database name. I click on the newly created database name and this time, instead of “Restore”, I select “Delete” and follow the prompts to remove it.

This was the easiest point in time restore I have EVER done. It was fast and I didn’t have to keep adding each log to roll forward.

The song for this post is Lucky by Elle King.

by

All of my friends say, “How much longer will it run?”

I have frequently needed to see how much longer a backup was going to run, or how much longer a restore was going to run.  This little bit of code is something I use to help me know how to plan.  I replace “Backup” with “Restore” if I want to see how much longer to plan for a restore.  I also use it for when I am tracking a rollback.  I remove the where clause and get a large list, the one that has a percentage and not a clear explanation is usually the one I want.   Sometimes the time remaining is lie.  I have had it imply 4 hours, when it took 13.  The comfort is that I can tell it is still working.


SELECT command, percent_complete,
'elapsed' = total_elapsed_time / 60000.0,
'remaining' = estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like 'BACKUP%'

The song for this post is “Just Another Girl” by The Killers

by

You must not know about me, I can kill connections in a minute

It is still snowy and cold outside so short post today about killing connections.  I love this quick little script and can’t remember where I got it from, so huge Thank You to the person that created it.  I use it when I need to restore a database that still has active connections.  This will first print a list of the connections it wants to kill for the database in the where clause, if you uncomment the “EXEC” part, you will get the fastest connection killer you have ever used. 

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('DatabaseName')

PRINT @SQL
--EXEC(@SQL) /*Replace the print statement with exec to execute*/

Enjoy and Have a Magical Day!