Automatic restore

Question

Automatic restore

reynolddejesus100

Grasshopper

Points: 13
More actions
May 26, 2015 at 3:23 am

#298608

i have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
thanks in advance!

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

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

If I'm understanding the question, you just need to use RESTORE ... WITH MOVE. There are samples at the link. Using WITH MOVE will allow you to rename the data files and the database (you'll have to do both if it's going to be restored to the same server).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

reynolddejesus100 (5/26/2015)
i have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
thanks in advance!

As a bit of a sidebar, that leaves you wide open to possible data loss for a period of up to 48 hours on most days and 72 hours over the weekend. I strongly recommend you change your log backups to at least once per hour and, if your databases aren't monsters, that you do a full backup every night.

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

reynolddejesus100 Grasshopper Points: 13 More actions · Answer 3

sir i want to automatic restore the backup files using sql agent, how can i solve this using sql agent automation

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

reynolddejesus100 (5/28/2015)
sir i want to automatic restore the backup files using sql agent, how can i solve this using sql agent automation

SQL Agent can call to T-SQL. You can use the T-SQL command for RESTORE database. You'll have to use WITH REPLACE and MOVE to ensure that you rearrange the files on the new server (or copy them to a new on an existing server). The link I provided is to the command.

Jeff's comments are valid though.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

reynolddejesus100 Grasshopper Points: 13 More actions · Answer 5

yes sir i use that, but the problem is every-time the backup run another file-name created. so i need to edit the sql agent to rename the backup file name.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Well, you probably use some sort of standard for the file name I assume. Build that standard into your RESTORE statement. You'll probably have to use ad hoc T-SQL, but that's doable within the Agent too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning