Dynamically Automate SQL database restore script

Question

Dynamically Automate SQL database restore script

SREENIVAS

Valued Member

Points: 62
More actions
September 18, 2019 at 8:12 am

#3680998

Hi All,
In my environment, we are getting huge account of refresh activities coming from to production to development(with different database names) so that I am thing to do automation. Could you please help on this any one.
I want TSQL script like below format
Put database is in single user mode -> restore database ->fix the orphan users.
Note : In my development environment, we have multiple ndf files available.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 1

To be honest, you should already have a script that does this because you should be testing your backups on a regular basis.

There are a lot of scripts out there that does this... do a search for "automatically restore database sql server" and pick the one that suits your needs.

A better way to do this might be to use something like RedGate's SQL Clone product. Yep... it costs some money but, for most people, it's well worth it especially in the disk savings on the Dev Box and it's nasty fast.

The only thing is, it doesn't do things from backups. You still need to test your backups on a regular basis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

I recently posted an article on here about this

we used Redgate sql backup and there are a few controls that let you schedule a restore to a remote server

it generates a script that looks like this

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [mydatabase_Restored] FROM DISK = ''D:\restoretest\mydatabase.sqb'' WITH PASSWORD = ''xxxxxxxxxx'', RECOVERY, DISCONNECT_EXISTING, MOVE ''AfricaTravel'' TO ''D:\sqldata\mydatabase_Restored.mdf'', MOVE ''mydatabase_log'' TO ''D:\sqldata\mydatabase_Restored_log.ldf'', REPLACE"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

then you could use a loop and replace the database name.... or create a sql agent job for each database (which is what we did for Our DR testing) and Jeff is 100% correct , restore testing is very important

https://www.sqlservercentral.com/articles/memories-of-dr-testing

MVDBA