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:
–1
TRUNCATE TABLE admin..tablenameforyourdbnameondiskgoeshere
–2 run some POSH in a SQL agent job
set-location c:\
$wtf
$wtf = gci \\pathtoyourbackupsgoeshere \ -name -include dbname_* | select -last 1
$wtf
$query
$query= “INSERT INTO tablenameforyourdbnameondiskgoeshere VALUES (‘” + $wtf + “‘)”
invoke-sqlcmd -serverinstance “instancenamegoeshere” -database admin $query
EXIT
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.
