SQL Server, How to automate a database restore

Hi Guys,

Today a little but (i hope) useful post!

We will see how to automate the restore process of a database. 

 

Only with T-SQL command and scheduling a Job.

 

Enjoy!

 

 

Automate a database restore

First of all we must say that the T-SQL language has a command to perform a restore of a database. 

 

The command is simply:

RESTORE DATABASE database FROM DISK = 'nomefile' WITH REPLACE
 

However, sometimes the backup is created by adding the date, for example, to the name. 

 

This makes it a more difficult to automate the procedure. 

 

In addition, sometimes the backup of each database is stored in a different subfolder.

 

So, how to automate this process?

Suppose having a production database (product_db) and a test database (test_db)

We want to align the data into the test database with the data in the production database.

What we need is the name of the last backup we have done in the production database.

To do this you can read this information from the backup set table (backupset). You can find this table in the msdb database and it contain a row for each backup set. 

 

Each backup set has it own media_set_id value. 

 

With this information you can go into the table backupmediafamily.

Et voilà!

 

Now we are ready to create our script.

I will store the name of the backup in the backup_name variable:

DECLARE @backup_name varchar(80)
 

I use the following query to retrieve the backup_name:

 

       
SELECT
    top 1 @backup_name = mf.physical_device_name
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily mf ON b.media_set_id = mf.media_set_id
WHERE
    b.database_name = 'product_db' AND
    b.type = 'D' AND
    device_type = 2
ORDER BY backup_set_id DESC
       

Now we can execute the restore command (on the test_db)

ALTER DATABASE [test_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE test_db FROM DISK = @backup_name WITH  REPLACE
ALTER DATABASE [test_db] SET MULTI_USER

 

If you want you can put all the commands into a stored procedure:

 

CREATE PROCEDURE AutoRestore AS
BEGIN

	DECLARE @backup_name varchar(80)

	SELECT
		top 1 @backup_name = mf.physical_device_name
	FROM msdb.dbo.backupset b
		JOIN msdb.dbo.backupmediafamily mf ON b.media_set_id = mf.media_set_id
	WHERE
		b.database_name = 'production_db' AND
		b.type = 'D' AND
		device_type = 2
	ORDER BY backup_set_id DESC

	ALTER DATABASE [test_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	RESTORE DATABASE test_db FROM DISK = @backup_name WITH  REPLACE
	ALTER DATABASE [test_db] SET MULTI_USER                
END                

Now it is very simple to schedule a SQL Server JOB that will execute out procedure...

 

 

That' all for today,

And remember, if you find an advertisement that genuinely interests you simply click it from on my blog, please. You will help me to contine writing more and more interesting content. 

 ~Luke

 

 

 

 

 

 

 

 

 

 

 

 

Previous post: Access to data in SQL Server from the web! a little bit of ...Javascript, Python and SQL Server  

 

 

 

I recently bought the hardware to read directly the signal from the floppy disk drive head.

I recovered all the floppies I had that were no longer readable. 

 

If you have one or more disks that you care about and want to try to recover send me a message or an email (lucadrbiondi@gmail.com).

... It’s not a job but a hobby