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

BACKUP / RESTORE Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 8:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
Comments posted to this topic are about the item BACKUP / RESTORE

Jamie
Post #769067
Posted Wednesday, August 12, 2009 4:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:22 AM
Points: 2,684, Visits: 2,438
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
Posted Wednesday, August 12, 2009 6:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
nice question

"Keep Trying"
Post #769237
Posted Wednesday, August 12, 2009 8:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:02 PM
Points: 33,153, Visits: 15,284
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
Posted Tuesday, August 25, 2009 7:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:42 AM
Points: 1,191, Visits: 790
Sorry to be a bit late but I don't see why the first one is incorrect.

Could someone explain, please?
Post #776655
Posted Tuesday, September 1, 2009 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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 »

Add to briefcase

Permissions Expand / Collapse