Had to automate a restore

I needed to automate a restore of a database from prod down to test. This seemed simple enough, except the database backs up to 12 files, I can’t use a linked server, and the time is included in the filename of the backup. All these parts made it more fun than I expected. Fun. Right.


So here’s what I used to do the job figured that if you can’t us your own tools or dbatools then this might help you out. I built a table in our admin database with one field to house the name of the file on disk. To get yesterday’s backup today I run the following code:



TRUNCATE TABLE admin..tablenameforyourdbnameondiskgoeshere


–2 run some POSH in a SQL agent job


set-location c:\


$wtf = gci \\pathtoyourbackupsgoeshere \ -name -include dbname_* |  select -last 1



$query= “INSERT INTO tablenameforyourdbnameondiskgoeshere VALUES (‘” + $wtf + “‘)”

invoke-sqlcmd -serverinstance “instancenamegoeshere” -database admin $query



The gci (get childitem) line finds you the last database backup, one of the 12 parts, and that way I have the full name and date and time to insert into a table to use later. There might be a better posh way to do this but I suck at posh. I run this in a SQL agent job under the powershell drop down item. You have to set the C:\ path to get it to find your stuff because that’s what Stackoverflow told me to do.


–3 I needed the left command because in my restore I had 12 lines for each file, and just left those hardcoded


declare @file varchar(50)

select @file=db from admin..[restoreworksforreporting]

set @file = LEFT(@file,20)

select @file


DECLARE @sql VARCHAR(max) = ‘restore statement goes here, had to turn the name of the db into a parm so I had to use dynamic SQL’ so here’s a brief example of that

‘RESTORE database dbname from DISK = ”\\pathofrestore\’+@file+’File1.bak”,

exec (@sql)

I had 12 files to restore from, the DB is 1.7TB on disk and 7 TB expanded and 23 odd files.  Hopefully this helps someone else out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s