Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

More Intelligent Backup and Restore Expand / Collapse
Author
Message
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
Posted Wednesday, November 23, 2005 6:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 716, Visits: 466
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
Posted Friday, December 9, 2005 11:27 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 716, Visits: 466
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
Posted Wednesday, February 8, 2006 3:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 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
Posted Wednesday, February 8, 2006 3:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 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
Posted Thursday, February 9, 2006 10:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 2:10 PM
Points: 716, Visits: 466
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
Posted Thursday, February 9, 2006 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

We will wait patiently.

Kevin
Post #257170
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: Tuesday, April 15, 2014 7:54 AM
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



Post #274001
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: Tuesday, April 15, 2014 7:54 AM
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



Post #274003
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 ««12345»»»

Permissions Expand / Collapse