Database restore automate

Question

Database restore automate

Diyas

SSCarpal Tunnel

Points: 4851
More actions
January 22, 2016 at 2:59 pm

#315310

hi,
i am planning automate my database new from shared backup location,
the requirement is need to auto pick up the full backup from shared location and do the restores.
can anyone suggest how to start with this

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Sree Divya (1/22/2016)
hi,
i am planning automate my database new from shared backup location,
the requirement is need to auto pick up the full backup from shared location and do the restores.
can anyone suggest how to start with this

If the file names are consistent and contain a date/time stamp, the you can use the undocumented xp_DirTree path/unc,1,1 extended stored procedure to get the names of files from a shared folder. You could also have prod push the data from MSDB to a "maintenance table" on the server that you're restoring to.

Before you restore from prod, though, you should make sure that things like SSNs and other severe PII is encrypted.

--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)

Diyas SSCarpal Tunnel Points: 4851 More actions · Answer 2

Diyas

SSCarpal Tunnel

Points: 4851

January 25, 2016 at 6:16 pm

#1854344

hi,

in my shared location db backup is .lbak

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

Sree Divya (1/25/2016)
hi,
in my shared location db backup is .lbak

Like I said, use xp_DirTree. Here's an example...

EXEC xp_DirTree 'C:\',1,1

Replace the "C:\" with either the drive and path to your backups or the UNC (\\MachineName\share\path) to pickup your backups. The command does NOT take file names or wild cards so you need to route the output into a temp table to use it. Something like this...

--===== Create the table to store the file names in.

CREATE TABLE #FileInfo

(

RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

,ObjectName VARCHAR(500)

,Depth TINYINT

,IsFile TINYINT --is a file if this column has a 1 in it

)

;

--===== Get the file names, which may include Directory names.

-- IsFile will contain a 0 for directories.

-- IsFile will contain a 1 for files.

INSERT INTO #FileInfo

(ObjectName, Depth, IsFile)

EXEC xp_DirTree 'C:\',1,1 --Again, change the "C:\" to the drive:path or UNC of your BAK files.

;

--===== Put your routine here to step through the files in the table.

--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)

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 4

That will possible though SSIS package If you have any idea about SSIS it is easy to implement the same

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

New persopn (1/26/2016)
That will possible though SSIS package If you have any idea about SSIS it is easy to implement the same

Now's your chance... how do you do it through SSIS?

--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)

Sgar... SSCarpal Tunnel Points: 4409 More actions · Answer 6

You can create Stored procedure for restoring, with parameters , dbname and path. Best way, you need to pass paramaters and execute SP.

Sagar Sonawane
** Every DBA has his day!!:cool:

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I have an SSIS package that does all this. It chooses ten databases at random from the whole estate and restores them one by one from the latest full, differential and log backups. It then does a DBCC check on each, before putting an F at the start of the name of each database that failed the DBCC, sending out a failure report if appropriate, and deleting all databases that restored and DBCCed successfully. When if finishes, it does the same thing again, excluding any database that's been randomly selected in the last 24 hours.

John