Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Intelligent Backup and Restore


More Intelligent Backup and Restore

Author
Message
Luis Cabrera-257608
Luis Cabrera-257608
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

Yup. It all works as expected now. Thank you very much.
Let me ask you one more question

When I run EXEC sp_ABRestoreDb 'Newluis', 'C:\temp\08-23-05-staffingdb' the database is created successfully and the DB files (MDF and LDF) are both created under the default backup directory.

How can I tell your script to use a different location for the physical files? In other words, instead of creating the following:

C:\Program Files\Microsoft SQL Server\MSSQL\Data\Newluis_data.MDF
C:\Program Files\Microsoft SQL Server\MSSQL\Data\Newluis_log.LDF

I want the following:

C:\temp\Newluis_data.MDF
C:\temp\Newluis_log.LDF

Where temp is a directory I pass as a parameter. Is this possible?


vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Not directly. My suggestion would be to use the @DryRun=1 parameter, which will generate the statements to restore the database but not execute them. You can then edit the statement to direct the files to your temp directory.

Hope that helps.



vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Just as an FYI, I sent Steve Jones an update to my .ZIP to fix the similarly-named but wrong file I included initially. Sorry for any troubles that oversight caused anyone.

Vince



Kevin Hammond
Kevin Hammond
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 7
I just ran across your set of stored procs when looking to upgrade our backup processes.

I also could use transaction log ability.

I stripped out the main components of the script to quickly generate a transaction log backup. Right now I am contemplating running this as a seperate stored proc or reintegrating it as another parameter.

I don't forsee needing to worry about modifying the restore procedure to be able to restore transaction logs, at lease in our case. The only way we will be doing transaction log restores is in a DR situation and we will be wearing kid gloves then so we won't be using automated scripts anyway.

Maybe someone doing log shipping could use that function, but I would be willing to bet there are plenty of log shipping scripts out there that already handle this...

If I do reintegrate the tlog backups, let me know if you want the updated stored procs...

Thank You,
Kevin
Kevin Hammond
Kevin Hammond
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 7
Another thought, we centrally locate all our SQL backups on one UNC server. We are in the stage where we have more than one SQL server, but don't have a SAN.

So in our backup enviornments we backup like so:
\\UNC\SHARE\SERVERNAME\DBNAME\DBNAME_db_YYYMMDDHHMM.bak

It would be nice to pull the {SERVERNAME} parameter automatically and stuff that in as another option. Most people probalby don't need that though, so that might be overkill.
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Kevin,

I'm working on updates to the procedures to meet these requests. I have the backup log stuff working, and the {ServerName} replacement as well. What I'm currently debating is how best to handle the asterisk when restoring transaction logs.

Applying just the latest file doesn't sound like the right answer, since it won't work if there was another log backup since the last database backup. I can't assume the database backup is in the same directory or conforms to a certain naming convention. What I need to do is either:

1. Find out the MinLSN that the database is using, and find the transaction logs that follow using the RESTORE HEADERONLY command. This is what I'd prefer, but I'm not certain how to read the MinLSN from the database boot page. I've asked a question on a forum, and I'll see if I get a good reply.

2. Start with the most recent transaction log and attempt to restore it. If it fails, attempt to restore the next most recent, etc., till it works, then restore all the ones that didn't. This may not work since I'm not certain I can capture the errors without it blowing up the batch.

I know I can skip these difficulties for the restore and just publish the backup that's working, but I really mean for these routines to be complimentary. Give me a little bit to see if I can work out a good answer.

Vince



Kevin Hammond
Kevin Hammond
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 7
Thank you

We will wait patiently.

Kevin
philcruz
philcruz
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 51
I update the scripts to work on SQL Server 2005.

http://philcruz.com/blog/2006/04/more-intelligent-backup-and-restore.htm



philcruz
philcruz
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 51
fyi, I updated the scripts to work on SQL Server 2005.

http://philcruz.com/blog/2006/04/more-intelligent-backup-and-restore.htm



Herman Hurtado
Herman Hurtado
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3

Came across scripts today and they work a treat. Also as per prevoius posts any update on transaction logs capability?

 

 


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search