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 ««123»»

How to repair the table Expand / Collapse
Author
Message
Posted Friday, July 27, 2012 10:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 1,231, Visits: 2,865
use backup log with no_truncate


Post #1336618
Posted Friday, July 27, 2012 10:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 1,231, Visits: 2,865
sorry -- backup log dbname to disk with no_truncate,norecovery


Post #1336619
Posted Friday, July 27, 2012 10:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:44 PM
Points: 358, Visits: 901
"NORECOVERY", no underscore.
Post #1336620
Posted Friday, July 27, 2012 10:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:55 PM
Points: 16, Visits: 109
backup log PMSdb
to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\PMSdb_Log.bak'
with NO_TRUNCATE,NORECOVERY

Msg 3031, Level 16, State 1, Line 1
Option 'no_truncate' conflicts with option(s) 'norecovery'. Remove the conflicting option and reissue the statement.

backup log PMSdb
to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\PMSdb_Log.bak'
with NORECOVERY

Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Post #1336641
Posted Friday, July 27, 2012 11:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 23,000, Visits: 31,488
Scott D. Jacobson (7/27/2012)
"NORECOVERY", no underscore.


Nice catch, mistake in my typing. I should have just done a cut and paste.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1336666
Posted Friday, July 27, 2012 11:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
backup log PMSdb
to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\PMSdb_Log.trn'
with NO_TRUNCATE

You do not use NORECOVERY in a disaster situation (damaged or missing data file). You use it when backing up a working and online database in preparation for a restore.
Continue after error was added in SQL 2005.

Is the DB in full recovery and do you have log backups at all?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1336690
Posted Friday, July 27, 2012 7:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:55 PM
Points: 16, Visits: 109
when i run this...

backup log PMSdb
to disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\PMSdb_Log.trn'
with NO_TRUNCATE

Server: Msg 3033, Level 16, State 1, Line 1
BACKUP DATABASE cannot be used on a database opened in emergency mode.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

i have no backup log...

FYI, my database (PMSdb.mdf) was corrupted before this when i attach database and the result is...

Error823: I/O error (bad page ID) detected during read at offset 0x000000e1b78000 in file
'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\PMSdb.mdf'.

actually, what is the correct step?
Post #1336850
Posted Saturday, July 28, 2012 5:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
If you don't have any log backups, then you'll just have to restore the last full backup that you have and accept the data since then lost


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1336904
Posted Sunday, July 29, 2012 4:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:55 PM
Points: 16, Visits: 109
if i restore the last full backup, how about the data since then lost?
Post #1336978
Posted Sunday, July 29, 2012 7:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
Was the database in full recovery model?
Do you have log backups since that last full backup?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1336991
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse