Restore script help

  • hi all,

    Im trying to write a little script for people to use for doing restores.

    We have 3rd party apps where they take our latest backup then do edits and give it back. we then restore to the live db. Ive found that we keep ending up with a 10+gb log file and all the indexes are fragged to hell.

    does anyone see anything foolish that im doing? or have a simieral script they care to share?

    /*------------------------------------------------------------------------------------------------

    Restore Database Template

    */------------------------------------------------------------------------------------------------

    -- Set to single user mode @ time listed to users.

    USE master;

    GO

    ALTER DATABASE [Test DB]

    SET SINGLE_USER

    WITH ROLLBACK IMMEDIATE;

    GO

    -- Restore database with replace and keep in single user mode

    RESTORE DATABASE [Test DB]

    FROM DISK = N'C:\NoBackup\Restorefile.bak'

    WITH REPLACE, RESTRICTED_USER

    -- Change database to SIMPLE recovery mode to alter Log size and run checks

    ALTER DATABASE [Test DB]

    SET RECOVERY='SIMPLE'

    -- Get current Log Size / Update to Documented (See xxxx) log size

    DBCC SQLPERF (Logspace)

    -- Get Log Name

    SP_HELPDB [Test DB]

    -- Update default listed = 1gb log 100mb increase and unlimited size

    USE [master]

    GO

    ALTER DATABASE [Test DB] MODIFY FILE ( NAME = N'testdb_log', SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )

    GO

    -- Run CheckBD to make sure date isnt currpt

    DBCC CHECKDB ([Test DB])

    -- run Ola Script to Rebuild/ReOrg indexes

    EXECUTE dbo.IndexOptimize @databases = 'Test DB',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30,

    @UpdateStatistics = 'ALL',

    @OnlyModifiedStatistics = 'Y'

    --Change ownership to SA

    EXEC [MyDB].dbo.sp_changedbowner @loginame = N'sa', @map = false

    GO

    -- Restore Maintenance is completede now to get the DB ready for Users

    -- Change database to SIMPLE recovery mode to alter Log size and run checks

    ALTER DATABASE [Test DB]

    SET RECOVERY='FULL'

  • might be worth adding in a backup database command at the end - you've set the db back to FULL recovery, so any t-log backups will proboably fail until you issue the first full backup

    MVDBA

  • ohh Thanks..

    adding now..

  • That means the live database is out of action all the time the third party is looking at the copy of it. Do your users accept that? If you could persuade the vendor to supply you with a script to effect the changes, that would be much more efficient.

    John

  • to add on from John's comment

    or restore the db to a different name and use something like SQL Compare to see what the vendor has changed

  • That's another option, but it has the disadvantage that you still need the vendor to send you back the whole database instead of just a script. I'd be interested to find out whether it would be possible to distinguish between the changes made by the vendor and those made by users in the normal course of business.

    John

Viewing 6 posts - 1 through 5 (of 5 total)

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