May 21, 2012 at 3:57 am
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'
May 21, 2012 at 4:16 am
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
May 21, 2012 at 4:18 am
ohh Thanks..
adding now..
May 21, 2012 at 4:34 am
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
May 21, 2012 at 4:45 am
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
May 21, 2012 at 4:51 am
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