Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Vince Iacoboni » More Intelligent Backup and Restore
Add to briefcase
45 posts, Page 2 of 5
««12345»»»

More Intelligent Backup and Restore Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Luis Cabrera-257608
Posted Tuesday, November 22, 2005 3:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 23, 2007 2:31 PM
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?
Post #238998
viacoboni
Posted Wednesday, November 23, 2005 6:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:04 AM
Points: 712, Visits: 411
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.


Post #239131
viacoboni
Posted Friday, December 09, 2005 11:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:04 AM
Points: 712, Visits: 411
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


Post #243300
Kevin Hammond
Posted Wednesday, February 08, 2006 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2008 2:26 PM
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
Post #256896
Kevin Hammond
Posted Wednesday, February 08, 2006 3:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2008 2:26 PM
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.
Post #256899
viacoboni
Posted Thursday, February 09, 2006 10:21 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:04 AM
Points: 712, Visits: 411
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


Post #257157
Kevin Hammond
Posted Thursday, February 09, 2006 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2008 2:26 PM
Points: 12, Visits: 7
Thank you

We will wait patiently.

Kevin
Post #257170
philcruz
Posted Wednesday, April 19, 2006 12:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 08, 2010 11:49 AM
Points: 30, Visits: 49
I update the scripts to work on SQL Server 2005.

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


Post #274001
philcruz
Posted Wednesday, April 19, 2006 12:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 08, 2010 11:49 AM
Points: 30, Visits: 49
fyi, I updated the scripts to work on SQL Server 2005.

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


Post #274003
Herman Hurtado
Posted Wednesday, July 19, 2006 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2008 11:10 PM
Points: 2, Visits: 3

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

 

 
Post #295478
« Prev Topic | Next Topic »

Add to briefcase
45 posts, Page 2 of 5
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.