Voice of the DBA

Tag: Backup/Recovery

  • No More Drive Extender – Windows Home Server

    I guess I might need to go back to RAID. From the Windows Team Blog, there is a decision to remove Drive Extender from the next version of Windows Home Server. Based on the comments, I’m not alone in thinking this is a stupid move.

    The big challenge for me was automatically protecting the data on my various computers. Windows Home Server gave me an easy way to copy the data, and also have it on multiple drives, so it was safe in the event of a drive failure. I have heard that the larger 1TB and 2TB drives are more likely to have corruption as well, so there’s more of an issue here.

    Putting RAID together for small systems is a hassle, though perhaps R1 isn’t a big deal if you have 2TB drives. In any case, I’ll be thinking about whether I need to stick with WHS in the future or migrate to some other solution.

  • You Don’t Need Log Backups

    You don’t need log backups. You don’t. They aren’t required for SQL Server to function.

    The place when you don’t need log backups is when you have a database operating in the Simple recovery model. In this model, your transaction log will clear out on a regular basis, every few minutes, and the space will be reused.

    I saw a post recently where someone said they had a database that only changed once a day. Data was loaded once a day and otherwise the database was read only. In that case, you:

    • Need log backups if you are in Full or Bulk-Logged recovery models
    • Do not need log backups if you are in simple mode.

    It doesn’t matter how many changes you have, if you don’t have log backups in full recovery, you’ll eventually have an out of control log growth situation.

  • Core DBA Skill – Backing up the tail log

    I’ve never had to do this in production, and I’ve only practiced it a few times, but I think this is a core DBA skill. Along with being able to backup and restore your databases, you should be able to recover to a point in time. That can mean a tail log backup.

    I read this in Paul Randal’s blog recently and decided to practice it. So I created my own new database and added a few transactions:

    create database db5
    go
    use db5
    go
    create table MyLog
    ( Txt varchar(max)
    , LogDate datetime default (getdate())
    )
    go
    insert MyLog select 'No backup', GETDATE()
    go
    backup database db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_full.bak' with init
    go
    insert MyLog select 'Full backup complete', GETDATE()
    insert MyLog select 'Misc Transaction', GETDATE()
    go
    backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn' with init
    go
    insert MyLog select 'Log Backup Complete', GETDATE()
    go

    I’ve added a couple backups here. If I were to restore this full, I ought to have 1 row in this table. If I add the log restore, I’ll have 3, but I’ll be missing the last line that says “Log Backup Complete”. Now I’ll wreck the database, as per Paul Randal.

    use master
    go
    alter database db5 set offline
    go

    I then rename the mdf file from db5.mdf to db5xxx.mdf, essentially “deleting” it from view by the SQL Server service. When I set this db online

    alter database db5 set online
    go

    I get

    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db5.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
    Msg 945, Level 14, State 2, Line 1
    Database 'db5' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    As expected, we have a problem. How do I backup the tail of the log? Remember that file is still visible. We’ll use Paul’s trick to add NO_TRUNCATE to the command:

    backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn' with init, no_truncate

    I get a successful backup, so let’s test. Here’s my restore script, restoring this db as a new database on this instance.

    RESTORE DATABASE [db7]
    FROM
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\db5_full.bak'
    WITH
        MOVE N'db5' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7.mdf'
    ,  MOVE N'db5_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7_1.LDF'
    ,  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7.BAK'
    ,  NOUNLOAD,  STATS = 10
    GO
    select * From db7.dbo.mylog
    go
    RESTORE log [db7]
    FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn'
    with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
    go
    select * From db7.dbo.mylog
    go
    RESTORE log [db7]
    FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn'
    with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
    go
    select * From db7.dbo.mylog

    When I go through this, I get result sets of 1, 3, and 4 rows respectively. I see all my inserts, so despite having a corrupted, destroyed, or renamed (in my case) MDF, I can get all my data back with the log.

    Learn and practice a tail log restore. It’s worth it.

    And don’t forget to set the db active:

    restore database db7 with recovery