Post reply

Backup Log file failure

  • July 27, 2007 at 9:45 am

    #68870

    I am having trouble backing up my log file for an MSSQL 2000 database. Backing up the database itself works fine, but the job for the log file fails even though it is going to the same folder.

    I thought maybe is was because users might have been in the database so I tested on Northwind where I am absolutely possitive there are no users.

    Can anyone tell me what to look for. It is crucial that I get this to work as I have to restore the whole thing to another server.

  • July 27, 2007 at 9:51 am

    #722939

    What is the recover mode for the database?

  • July 27, 2007 at 10:15 am

    #722953

    Lynn has a great question, but you don't necessarily need the log for a restore. The data file backup is what you need for the restore. The log, if you're in Bulk or Full modes, gets the changes since the full, but isn't required to move the db.

    Are you moving the live db or is this moving to a test/dev server?

  • July 27, 2007 at 11:35 am

    #722988

    Thanks Steve and Lynn.

    If I don't need the log file for a restore, I'll skip it for now. But I would like to find out why I can't back up the log in the first place.

     

    And how would I find out what mode it's in?

  • July 27, 2007 at 11:58 am

    #722998

    Now i've done it!

    I ran this before I found out I didn't need to load the log file.

    RESTORE DATABASE FRS

     FROM DISK = '<path to new location>'

     WITH NORECOVERY

    RESTORE LOG FRS

     FROM DISK = '<path to new location>'

     WITH NORECOVERY

    Now the new database is hung. It's says it's loading. Is there a way to stop it with out having to bring down the SQL Server?

  • July 27, 2007 at 12:04 pm

    #723004

    "how would I find out what mode it's in?"

    In SQL Query Analyzer, run this SQL:

    select DATABASEPROPERTYEX( databasename , 'Status') as DBStatus

    , DATABASEPROPERTYEX( databasename , 'Recovery') as DBRecoveryMode

    go

    For DBStatus, the possible values are:

    ONLINE = database is available for query

    OFFLINE = database was explicitly taken offline

    RESTORING = database is being restored

    RECOVERING = database is recovering and not yet ready for queries

    SUSPECT = database cannot be recovered

    For DBRecoveryMode, the possible values are:

    FULL = full recovery model

    BULK_LOGGED = bulk logged model

    SIMPLE = simple recovery model

    To perform a transaction log backup, the DBStatus must be ONLINE and the DBRecoveryMode must be either FULL or BULK_LOGGED.

    A DBRecoveryMode of SIMPLE means that the transaction log is purged on a regular interval and cannot be backed-up. See Books OnLine under checkpoint for how "regular inteval" is determined.

    SQL = Scarcely Qualifies as a Language

  • July 27, 2007 at 12:21 pm

    #723009

    Thanks, Carl. i have a DBStatus of ONLINE and the mode is SIMPLE. I guess that explains why I can't back up the log.

    Things are finally starting to maek sense. I really appreciate all the help I've received in this thread.

  • July 27, 2007 at 12:35 pm

    #723013

    I solved my hung database with brute force. I deleted it. Because this is all a testing process before the real move, it doesn't matter what happens to the new database.

    I saved all the stored procedures as script and edited them where the server name was used so running those scripts doesn't take long. I just have to run some before others due to calls.

    I'm actually moving 2 databases. One is a reporting DB so Simple mode should be fine.

    The second DB receives live imports all day long plus users are inserting and editing records so I assume Full mode would be best here. Does this sound logical?

    Funny that the DBA (who is no longer here but is very competent) didn't set up the DB that way to begin with.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply