BACKUP / RESTORE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jamie Longstreet » BACKUP / RESTORE

BACKUP / RESTORE

Rate Topic

Display Mode

Topic Options

Author

Message

Jamie Longstreet-481950

Posted Tuesday, August 11, 2009 8:34 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:48 AM
Points: 2,440, Visits: 714

Comments posted to this topic are about the item BACKUP / RESTORE

Jamie

Post #769067

kevriley

Posted Wednesday, August 12, 2009 4:24 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

makes no difference to the answer, but the question states that the backup location is 'F:\......', but al the answers refer to a location at 'C:\......'

probably just a typo

Kev

Post #769190

ChiragNS

Posted Wednesday, August 12, 2009 6:17 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

nice question

"Keep Trying"

Post #769237

Steve Jones - SSC Editor

Posted Wednesday, August 12, 2009 8:13 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

Corrected the path typo.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #769337

Eric Mamet

Posted Tuesday, August 25, 2009 7:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:41 AM
Points: 1,129, Visits: 685

Sorry to be a bit late but I don't see why the first one is incorrect.

Could someone explain, please? Ermm

Post #776655

Jamie Longstreet-481950

Posted Tuesday, September 01, 2009 7:50 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:48 AM
Points: 2,440, Visits: 714

This question was poorly written and since I first started writing them because I found poorly written questions could throw me off easily, I vowed to fix this. My resolve did not shine through on this one.

The goal here was to make note of the compression option on SQL backup. http://msdn.microsoft.com/en-us/library/bb933863.aspx

The fact is, there was a second more hidden goal here and this had to do with the LSN (Log Sequence Number). It is really simple to attach the database, but it gets more complicated if the database is not set to simple mode and there are trn files to consider for recovery purposes.

Basically the first one doesn't work because the LSN is not taken into consideration. You will get an error if you attempt to restore it in the manner suggested:

The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

To demonstrate this, backup adventureworks with compression:
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AdventureWorks.bak'
WITH COMPRESSION, INIT, STATS = 10

Run the first choice:
RESTORE DATABASE AdventureWorks
FROM DISK = N'C:\backup\AdventureWorks.bak'
WITH MOVE N'AdventureWorks' TO N'C:\Data\AdventureWorks.mdf',
MOVE N'AdventureWorks_Log' TO N'C:\LOG\AdventureWorks.ldf',
RECOVERY, STATS = 10;

Now use the second method (there is no mention of Recovery and thus no LSN required):

RESTORE DATABASE [Adventureworks]
FROM DISK = N'C:\Backup\Adventureworks.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

Jamie

Post #780708

« Prev Topic | Next Topic »

Permissions