Change Reovery Model During Backup

  • I have a SQL 2005 production database running with a Full recovery model. Once a week I have a SP script that restores a copy of that database to a test database (Sandbox) which has a Simple recovery model.

    RESTORE DATABASE [Sandbox]

    FROM DISK = @RestoreFile

    WITH FILE = 1,

    MOVE N'Production_Data' TO N'D:\SQLLog\Sandbox.mdf',

    MOVE N'Production_Log' TO N'E:\SQLLog\Sandbox_log.ldf',

    NOUNLOAD, REPLACE

    Are there any shortcuts that I can use since I do not need the full model? I suppose I can add the following code:

    ALTER DATABASE Sandbox SET RECOVERY SIMPLE

    DBCC SHRINKDATABASE(N'Sandbox')

    but it seems like a waste. Any thoughts?

  • When you restore a database, the restored copy will be in the same recovery model as the source database was when backed up.

    If you want the restored copy to be in a different recovery model, you should run the

    ALTER DATABASE [Sandbox] SET RECOVERY SIMPLE;

    command as you are already doing. I can't think of any better way to do this.

    Are you sure you need to run DBCC SHRINKDATABASE? I would avoid that at all costs - it will kill your performance on that restored copy.

  • try this instead of complete db if you want to clear the only the logs.

    dump tran dbNamewith no_log

    dbcc shrinkfile (dbName_logName,truncateonly)

    do you have any space issue to clear the log if it is not then leave it as the simple recocery will take care and avoild further log growth.

    Regards
    Durai Nagarajan

  • The truncate only option is only valid for data files, it's ignored completely when shrinking a log file.

    Dump tran has been deprecated for a long time and should not be used, neither should the no_log option, and it's not necessary anyway if the DB was just switched to simple recovery model.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • thanks gail

    i never been updated with that.

    Regards
    Durai Nagarajan

  • Great feedback. Thank you everyone.

    I do not have a space issue. I simply wanted to reduce the log file space used because the server is virtualized and there are nightly VM backups of all of the drives. And no, I dont want to get into a debate on VM backups - we handle VM and SQL backups differently. My thought was that there is no sense in backing up a dead logfile.

    It is a test database so performance is not a significant concern - at most there are 5 users accessing it. If there is a performance problem I will remove the shrink.

  • If you want to reduce the log size, just use ShrinkFile and shrink the log. Shrinking the data file does require log space, so if a little counter to your intended goal.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • As others have said, don't shrink files unless you really have no choice. I know this is development, but once you shrink the files, your indexes will generally get much more fragmented than they were before, and then--unless the developers aren't fussy about performance in dev environment or database is extremely small--you'd want to do a reorg or perhaps rebuild job for your indexes.

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

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