Search
Sunday, November 18, 2018 ..:: Home ::.. Register  Login

Peter Henry on Facebook  Peter Henry on LinkedIn  Peter Henry on Twitter

   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

How do you do a Get Latest from a database? (How to restore a SQL Server DB with Latest version)

Nov 7

Written by:
Wednesday, November 07, 2012 10:51 PM  RssIcon

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!

use master 
declare @doBackup int; set @doBackup = 1 
if(@doBackup = 1) 
begin 
        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' 
end 
else 
        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 
GO 
use YourDatabaseName_Peter 
go 
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!

Copyright ©2012

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement