How do you do a Get Latest from a database? (How to restore a SQL Server DB with Latest version)
Wednesday, November 07, 2012 10:51 PM
Are you a developer using a shared development database but are making potentially dangerous, data threatening changes? Want a GREAT way to make a backup and restore that db to your own personal sandbox? Then I have a GREAT script to help you out!
Do you develop using a db backend? We do, in fact, I’m PREEEEEETTTTTTTTY sure you probably do too! But, when you’re making schema changes, or important data changes, or even playing with stored procs, how do you do it? Do you do it live on the prod server?! Shame on you for that! Ok, ok, you’ve done that ONCE a looooong time ago, you got your knuckles wrapped and you don’t do that anymore. But do you do it live on the dev db where you could hose your coworkers if you screwup the parameter list for that proc? So how DO you do it?
Well, I have a suggestion, why don’t you just whip out a new db?! That’s right, if you could just run ONE script to backup your current DB and then restore it to your own personal DB, then you could play with that new personal one until you get all your “bugs” worked out and THEN once you’re confident, run it on the dev/prod servers. That way you’ll look like a hero by “measuring twice and cutting once!” (Why YES I do watch Holmes on Homes haha)
There is ONE hiccup with doing this and I want to share it with you. If you’re an astute developer and looked into this before, you’ve found the RESTORE DATABASE command, easy right? WHOA there Flash Gordon, not so fast! The problem with the RESTORE DATABASE command is, by default it will restore the FIRST “position” it has in it’s collection of backups. You have to explicitly find the last version it has, then restore that version.
Ok, let’s cut to the chase!
declare @doBackup int; set @doBackup = 1
if(@doBackup = 1)
print 'Backing up YourDatabaseName'
BACKUP DATABASE [YourDatabaseName] TO DISK = N'D:\Backup\YourDatabaseName_CurrDev.bak' WITH NOFORMAT, NOINIT, NAME = N'YourDatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
print 'Backed up YourDatabaseName'
print 'Skipped backing up YourDatabaseName db.'
print 'Restoring to YourDatabaseName_Peter'
Create Table #BackupDBData (BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed bit, Position smallint, DeviceType tinyint, UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0), FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime, SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int, SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier, Collation nvarchar(128), FamilyGUID uniqueidentifier, HasBulkLoggedData bit, IsSnapshot bit, IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit, HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier, ForkPointLSN numeric(25,0) NULL, RecoveryModel nvarchar(60), DifferentialBaseLSN numeric(25,0) NULL, DifferentialBaseGUID uniqueidentifier, BackupTypeDescription nvarchar(60), BackupSetGUID uniqueidentifier NULL, CompressedBackupSize bigint null)
insert #BackupDBData Exec ('RESTORE HEADERONLY FROM DISK = N''D:\Backup\YourDatabaseName_CurrDev.bak''')
declare @lastBackupFilePosition int; select @lastBackupFilePosition = max(Position) from #BackupDBData
print 'restoring from file position = ' + cast(@lastBackupFilePosition as varchar)
drop table #BackupDBData
ALTER DATABASE [YourDatabaseName_Peter] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [YourDatabaseName_Peter] FROM DISK = N'D:\Backup\YourDatabaseName_CurrDev.bak' WITH FILE = @lastBackupFilePosition, MOVE N'YourDatabaseName' TO N'D:\Database\YourDatabaseName_Peter.mdf', MOVE N'YourDatabaseName_log' TO N'L:\Logs\YourDatabaseName_Peter.ldf', NOUNLOAD, REPLACE, STATS = 10
ALTER DATABASE [YourDatabaseName_Peter] SET MULTI_USER
print 'Restored to YourDatabaseName_Peter'
It only LOOKS nasty right now because of the table creation, but you can cut’n’paste that part!!!!!!
First off, you need to switch away from the db you want to backup, that’s the first line to goto the master db. Next there’s a bit of logic I use to manage if you indeed want to perform a backup of the current db. It’s an int of 1 to do the backup and 0 to skip it. Why that? Cause sometimes you just want the lastest cause you’ve done a backup recently and don’t want to take the time hit to do it again.
PS Please check out the function parameters and change what’s appropriate for your own DB (ie change ‘YourDatabaseName’ to YOUR db name)
Next is part of the restore magic. To get the “backup file position” you need to know to get the LAST position, you need to have a variable to pump that data into, in this case it’s a table, that’s' the long part there! I create a temp table, run a proc, insert some data into that temp table, get the one int value I need (last backup file position) then clean up that temp table.
Next is the restore magic. You will need to have the proper SQL Server schema creation privileges to make this work. If you don’t have that…uh….please don’t email me, I can’t help you out there, you’ll need to ask your DBA. But if you’re a dev, you probably have this now? Maybe? Hopefully?! haha
Ok, so let’s assume you do, you’ll need to do some db admin stuff (prep it for DBA work with set single_user), then restore the backed up version (which you made a new one or not, but either way, you’re getting the latest version with using the file position value), then you turn your db on for public access.
BINGO! Now you’re able to confidently write schema updates, stored procs changes, etc without fear of harming anyone else’s user acceptance testing! HAHA
Now that we know how to create a copy of the LATEST dev db for your own purposes, it’s time to grab a coffee and get coding!
PS Don’t forget to change your web.config or your app.config’s connection strings to point to your new db so you can USE your newly created jewel!