PowerShell for SQL Restores

jump in if there were any questions that particularly need answering otherwise we can wait to the end anybody can ask questions in the go-to meeting questions window or in the slack Channel sequel a sequel PS VC underscore QA and if you could press the start recording button store it once you're already recording now we're up there and going minimize it so I can see my screen there we go I hide some bits right good afternoon or good morning or good evening everyone depending where you are yep don't we talk about backups restores and DBA tools and also some general basics behind sequel server backups just to make sure it will knows the issues you can come up with when you trying to make me storing PowerPoint working just my me slide I've been doing this about 20 odd years I run the East Midlands passed chapter in the UK I help run the annual sequel relay series of conferences that happened in the UK once a year my contact details are there and feel free to tweet or mail me if you get any questions after I don't see your screen I was just about to say that I'm on a very very slow internet connection so now you know I don't ah sorry that when drop handed over sorry can you pick now I can see it now yes get back to the mean spy sorry I thought when you handed control that gave everything yes I've been banging on about passion backups for quite a while now so just getting more and more advanced it goes on so backups aren't they a little bit dull well yes and no a good backup should be simple to perform anything that's overly complex tends not to be that stable it gets very fragile they should be reliable and they should be trustworthy but a good backup protects your job in my opinion the worst thing a DBA can do is to not be able to recover data if it breaks it's an unbound outage if you can get it back and all the data is safe the minute your business can't trust the fact they put data into this expensive database and they can't guarantee they can put it out at some point in the future that should make them very very nervous in my mind that's worse than it being down for a day at least it's down for a day you know you're too safe you'll carry on if you've lost something you can never trust it so I'm a big believer in having good backups and having regularly tested backups so just quick overview sequel servers backup modes there's three recovery models with sequel server masoom poor model your selling secrets if you don't care about point in time and cover ability basically your transaction can just wrap around itself on a few rules it has about when it can read use them but generally simple mode it will just get into transaction log go back to the start and loop around it in full mode your tenancy was if you do care about point in time recoverability single transaction log won't be reusing to you taking a backup of it because you've told secrets or it's important to you and sequel server will do exactly what you tell it to so it gets the end of the log and it can't grow and you've not backed it up it won't wrap around and reuse the log so this is why your database hangs when it's in full recovery model and you fill your transaction locks o´clock mode is exactly the same was full the difference is that some operations and these are very specific operations there's a list of them on MSDN are minimally locked this means that there is only a minimal amount of data written in the transaction log but it's not everything it will still you have to do it for instance if you do particularly inserts you have to use a very specific syntax to guarantee there will be minimally logged so it may work for you may not work for you but you still have all the transaction log overhead so with those you have three backup types you can use with sequel server you've got the full or a database backup in this one all data in your database is written out to disk all dating the database will includes all the system tables that we have column definitions index definition everything there's a full writing out of your database a differential backup just writes out the data that has changed in your database since the last full backup these are cumulative so if you did a fall back up on Sunday night and took a differential yesterday on Monday that would that differential contain all the changes since the full backup on Sunday if you took another differential backup today that will contain all the changes since Sunday till today so you don't need yesterday's differential backup so they can grow quite large over the over a week's worth of data changes the log backup backs up your transaction locks does what it says on the tin so full and differential backups only can work with simple mode log backups only work with full recovery model and bulk Ricoh clock recovery model - otherwise you're not storing anything in the transaction log you were just wrapping around so to restore to a point in time you need the last full backup before that point in time the last differential taken between that full backup and the point in time you're restoring - and then all the transaction locks since the differential backup and if your recovery the database is failed you also need to tell log backup of what remains that database transaction log that's a little about the scope of this presentation it may come as shooting you go running database if you don't have the differential backup you can still run all the transaction look forwards it's just you might have a lot of transaction logs to run forward if you're running them every 10 minutes every day and you've got a resource every day's worth of them it'll take quite a long time I've seen people struggle with that one one of the biggest things people don't see about backups until they dig under the hood is the SSN or log sequence number as far as the sequel server backup goes sequel server never trusts the file name or the bright times on the files it just doesn't bother every single transaction that modifies data within sequel server is allocated a log sequence number this is a very large number I believe it's a number 25 if you're looking at c-sharp data types so huge it never really runs out I've never heard of anyone wrapping it and while it's a number the only numeric comparison that makes any sense is if LS n a is greater than LS MB then a happened after B that's it comparing the difference in Allison numbers doesn't tell you how much works the database has done you can't compare the numbers between different databases means absolutely nothing all sequel server uses and forced to say this number is greater than that this happened after that you will get gaps because the other sins is initially given out when you start transaction if it doesn't commit or doesn't get any further it's never written into the log so you don't have it but this is the one thing sequel server will use to make sure everything is written in order and that's important because when you restore a database sequel server can only restore as far as it has an unbroken LSN chain so as you can see there and then SH chain is exactly what we said it was before but it really defines which backups you can have you need a full backup that full backup will have what's known as a check point LSN this is the editor in the sequence service as the backup brand and that is then tattooed into every log backup and differential backup that's taken after that fall backup as this database backup Ellison this is why if someone takes a full backup erroneously during your backups like what's one of your devs takes a full backup at lunchtime they can rent your entire restore chain for the rest of the day because every transaction over for that work absolutely fine but you can't restore it because you won't have the full backup they took and smoke it off to their machine this is why your dev really really need to be talked about copy only backups if you're gonna let them take their own they don't reset this checkpoint or database backup as a Ellison they are purely a copy and if you have a differential that exists you can just use to skip all the transaction logs that have occurred before its first LS M value and if I quickly drop to manage to know and show you some of these so this is the I'll be using in demo in a bit mini mint and this happens to be a log backup so here we can see the error s ends within the backup so you have the first Ellison this is Ellis in the backup started last Ellison is the other sim with a backup completed and then a space backup is that large number there yes no this is a transaction log backup the checkpoint medicine is the same because the world crate to the database but this transaction all can only be restored on to the full database backup with that LSN if you have one with a different one then it can't be restored that's pair breaks oops the only way to reliably guarantee you can read this header is using sequel servers restore header owner funk functionality I've tried to find some shortcuts I broke quite a long blog post on this just before Christmas basically if you're not using compression or encryption you can sort of do it there in a known place you read the file in bytes then you can pick out these LS ends but the minute it's compressed or encrypted then you haven't got a chance so it's not reliable enough so I didn't put it into the code I'm going to show in a minute so without the way I'm gonna move on to some demos so going to start with some backups using the new DBA tools functionality so make sure so I have multiple copies of the module on my machines I'm using the one I want to so just to keep things nice and clean one running through the backup examples I'm gonna splatting with the variables if you're not met this before it's a really handy neat way of passing in parameters to a function so here I've just created a hash table with some key value pairs so a key of sequel instance this is gonna run us into the local sequel Express instant one laptop a database this is adventureworks 2012 i've run java inca houses make big script on just made up about half it half a gig I think it's size so actually take some time to back up and the director I'm gonna throw the backups in just so I know where stuff is to delete it so if we just set that you can see some output so it's simplest you can see we just run a backup you get nice progress bar it tells us where it's backing up to and now we'll just runs away and then we return you some information about the backup so you can see we're talking the backups completed which database it was what the sequence this was named with a file it's folder the full path to that file and we also give you the script so if you wanted to repeat this or what to keep a check of when things ran you can do that so that's all fairly simple and you so it's quite slow so one thing we offer you is striping sequel server allows you to write out your backup files to multiple files so rather than just writing to one large back file you can write it to a number of files I think it's a limit of 64 and we offer you the way of doing it either with a file count parameter or by specifying multiple backup paths not a backup paths are quite common you'll write to multiple storage arrays for instance or multiple drives on your machine this gives you a much higher throughput because you're hitting multiple disk controllers so I'm Justin do here you use the PowerShell measure command to see how long it takes to do a plain backup add the parameter file count equals four to my backup and then measure it again to see how long that takes they have a quick comparison and the first run took fourteen point three seconds the second one took eleven point two so that's not a huge increase but this is quite a small database this can make a big difference on a large database when you push stuff out and we return when you show you this when you stripe with the file count we automatically append one of X to all your backup files so it's easy to see that they're all from the same backup and which ones they are because we store from a stripe backup you need all the files you can't get away with three out of four you need four out of four that's a hard and fast rule so while it improves your speed it can also cause you problems just dropping out of splatting now because you can't splat an array which is an annoying oversight I didn't find any until today so now what we're going to do is just backup the backup directory and we're gonna split into two file locations and that'll do much the same but now we're actually just specifying different locations so this could be separate Drive letters for your backups the other problem with striping is that it goes the speed of the slowest disk and if you have a missing disk as well it will fail sequel server won't go oh I've got two out of three discs you told me to use I'll use those it will bomb out because it can't see the third disk this one backs up to a USB Drive and I've had problems with this because my machine keeps my USB Drive and it fails so that's there if you want to run through these scripts I'll pull the scripts up online afterwards if you want to run this yourself feel free based if my laptop sleeps my USB stick that's it so that's for backups what about the other types well we can take types of diffs we can do a differential backup okay and we now have a differential backup that's my fault I didn't take a fall back sorry I've not reset a machine that should have failed and we just shown you that we were taking no copy only backups by default backup DBA database takes copy only databases backups because to stock that promote saying where someone you use this tool to take a copy at your data to use to populate another instance and then wreck your production restore chain which would really ruin your day things then failed so what you have to do if you want to take a proper backup run the copy only backup you just take no copy only true and that will then take it yeah the different work but it would be very fast there's no modifications and data in that size if we want to take a log back up that one failed a very simple recovery mode disk and run there so we could reset that up so it will stop you it won't allow you to take a backup that's not supported by the unbind database the other ox when we use is that you can verify your backups sequencer will write your backups out quite happily for you but you have to tell it to then go back and check it so with the verify switch here passing and verify true it will do the back up and then we'll restore verify only against that backup it doesn't restore the database back to disk it just goes through and checks logically that all the data structure within the files and the backup data make sense and this will then confirm back to you that it's actually been checked we run this I am trying to look you won't do and that red is because I tried to add a hash key value back in someone already existed okay it now goes to do a restore database but verify only if we now look at backup output you'll see that we have some extra fields upon two things it is verified and the verify was successful so again if you were running this as part of another script you get the information back so you can store it some of your auditors or for someone else to see it so using another piece of the DBA tools it's all set there's a function called get DB back up history we let you see as it says the backup history on a per database or per server basis this will just show me all the backups taken on adventureworks so you go look for backups differential backups Falls Falls and that's just the one who playing around with this now it gives you some information about how much was backed up where it was backed up to so if we go here you can see our stripe back up where it's backed up to four different files and we can use get things like the last full backup so that's all quite handy to get an idea of when the last backup around on a database but quite often a more interesting question for a DBA is to show me all the databases that don't have a full backup so that is coming in get DBA database we're just adding some functionality that adds the no for backup switch and won't run against an instance this would return any database that doesn't have a full backup at the minute everything coming this instance has a full backup so let's just create something there we go we now have a database with no backup call no backup just to make life really simple so if you came across that you could fix that quite easily with making everything worth the PowerShell way will you take the output from one command --let pipe into another command you don't have to save it or do any work with it so here we just take get DBA statement and pipe it straight into backup database so you just send put the backup didn't deceive DBA tools backup create a folder with the database name and to do a proper full backup so no copy only so there we go we've got a backup no backup folder containing a full backup if we now look for the things without before no for backup we're back to a good place everything on there's got a full backup now that only counts for things that have never had a full backup however you might have something with a stale backup somewhere in your estate so to me estelle backup is one that was taken longer ago than you'd like so for instance I don't like seeing databases under a certain size so database every certain sites that don't have a full backup within the last 48 hours because that attention to mean the restores can take a very long time so we lose our recovery time objective so we've added the no full backup since parameter that lets you pass in a date time so here he's going to pass in tell it to look from five minutes ago so this will return some databases so ago databases because they've not been backup for an hour since I last ran through my demos but obviously you could quite easily change this to add days - seven - if anything there's not had a backup in the last week and again you can pipe that output straight through to backup DBA database so that all that check if you find something to bits out of the day and you want to quickly get it okay that end is backups and now I'll just quickly go back to parish off a couple of slides so restore problems I've tried and written many restore automation tools over the years you started off trying it in T sequel as the description this event said I've been there done that got the t-shirt and you run into lots of very fragile t sequel statements to build themselves an XP command shell hell as things don't work quite the way you expect so powers show when it came out was a real happy place for me to find out how to use these and everyone writing things over the years I found it a couple of issues that a lot of scripts don't take account of and you need to handle because they do happen you need to ensure you've got a valid restore chain it's a waste of time for the person running the restore if you restore the first 20 of 40 files and then find out you're missing the transaction log file in the middle they're probably wasted a lot of time restoring all that lot you could have stopped at the beginning maybe 10 minutes to find the file on tape put it back in its place and rerun the restore you should cope with the database already existing in a nice way basically if it already exists and the users not told you to clobber it don't cobber it should be able to cope with file moves and renames quite often you may have files on a server restoring from server to server B the file names may already be in use on server B and you don't want to go over the top of those especially if you're using this to repopulate test instances for example or for fault finding on a separate server you need to cope with new files appearing when you're rolling through database backups especially using transactional backup restores the sequel server is actually replaying all the transactions so files will shrink and grow because that's a locked event and also if you add a new file that will suddenly appear doing a log backup restore I've seen some ones whether you look at the very first backup all those all the files I got to deal with and then when another data files added halfway through the day it has a little bit of a problem she had to perform point-in-time restores quite often people want to say 'amen restore to an hour ago before my problem occurred and see what was happening she don't worry about backup file names just because everyone has a slightly different naming convention depending on the tool they're using and sometimes people mock them up by mistake I've seen people try and copy data files and the names are no longer useful to human should cope with striping it's very common for people to use that now for performance and it should cope with multiple backups in a single file I've seen a number of cases over my time where someone's pinched a backup script from somewhere on internet and they've been running it and not realized it's been backing up all the backups into one humongous backup file and I mean they'll have full backups differential backups and transactional backups going to use one huge file and I've seen those one I remember someone only found out about it because many years ago and it hit the file system limit because back in the days of early NTFS and then they realized what was going on so you need to cope with that because they still want to go to restore their data so it with that in mind I'll go back to the demos and show you where we're trying to do things with the DBA tools to cope with every one of those so first off what's pretty quite common to a lot of people just a folder with some boxes and CRN's this particular folder happens to be a two full backups and transaction logs I do a quick digression onto those all hi - Rob are all the database I'm using in here are very simple crazy database put into full recovery mode I create a quick table good steps and with an integer and a time column declare an integer and then step through in every 30 seconds I write in vintage account and the timestamp into the table and then every five minutes I take a backup of some sorts so this means I have a range of backups I always do restore point in time I can check that the database thinks is at the right time and I've just created a couple of different ones so Oh restore clean it's just a very simple - for backups and some transaction logs that restore time diff has a definite and there's some others that we'll go through to go through again I'll let you have all the scripts create these at the end so we have the files so it's simplest we just can restore the very first back file so this is just returning a simple back we're gonna start - my sequel Express trace exceeds a localhost we're gonna restore it to the database name test restore so we don't cover the other one and we're gonna redirect all the database files in to see DBA tools test rest to again avoid any clobbering of my running database yeah it's a nice progress windows and again we return you some useful information you have this equal incidents database name my username whether you told it to not recover whether you told it to replace an existing database which stores completed its restored one backup file is restored to data files backup file was restore clean bak the restored files were all cleaned up MDF and restore clean on scroll dog de lef we're sorting to see DBA taller stressed and also we give you the T sequel script to do same thing again so if you were to repeat this but not use PowerShell you could do with the T sequel we also offer you the option to just have the T sequel so if you want to see what cents gonna do if you say output script only it will go through all the checks of the files and all the validation of your backups it just won't write anything to disk I'm not it does that check as well because it puts the replaced statement into the T sequel so it has to check the you actually wanted to replace it otherwise it would be an error that's just a nice handy that off the thing if you want to see what's going under the hood and just check this logical if you're doing a complex restore so now we're just going to pass in rather than the file we're just going to give it the directory so what this will do buddy fault can scan all the backup files in that folder and work out which ones it needs to do to restore to get to the latest point in time those backup to consist of a quick look here it's worked I need to restore the number to backup file the twenty to one transaction lock the two to transaction log and the two three transaction log which is what you needed to do as you can see for everything apart the last vault it's been saying no recovery is true so knows not to do recovery until gets their very end that's quite nice and simple so what about if you want to restore to exact point in time so in this state with these examples I know this database was being backed up between 1258 and 1313 today so yesterday so I'm going to tell it to do exact the same as before except longer put restore time on and just pass in a date time parameter here and here I'm just gonna run invoke people come on to to get the maximum date for my steps table just to show those restored it to the point in time so there we go that would be the closest as it got because it drops him in every drops a row in every 30 seconds so the next one would have been three seconds past my wrist or time if I drunk that two-second resolution on restore time I'd have got the next one so you can see that's in early one month this was just restored the first full backup and then the first transaction log because that's all it needed well what happens if we picked a slightly later date again the time marries up through the time I'd asked it for but this time it's known to skip past the earlier backups just caught up to start she's known to skip through those and just come through and restore enough to get it through to the last point in time so now I've got a exactly the same copy is that database exhibit it has a dip in the middle so this time we just use the scripts only he'll make it slightly easy to run back through it so in this case that restoring to a point in time just before the differential was taken so she saw the fall back up the first transaction log the second transaction log back up and the third transaction log back up the next one is just after the DIF has been taken in this case we can see it's taken the first fall back up the differential back up and then the first log file off the differential backup so it knows it can skip the first transaction box and only needs the one after the differential I'm sorry to have done the yeah I know that family that goes with again it's within 30 seconds of restore time to the granularity for this database so there's quite a lot that goes under the hood of restore DVD a database it's a wrap around a lot of other functions going down that route that we've been pushed down the path of community of writing functions that do one job well and reuse them wherever you can so if you want it with the verbose you'll get a lot of information as it goes through you can see it starts from stored in a database go to the directory store font I've got the files it reads backup headers it filters them it restores them there are various tests and restoring database and filtered arrays there is here a flow chart of how it goes through all the different functionality and one benefit of moving to this architecture is that it's very quick to add new ways of looking at files or new rules so if something else wants to be checked we can very quickly add another function in here before we get to the bottom so one thing I was saying about isn't relying on the LS ends use the back in here in restore clean if we just set the my time on get missed all the time clean - now it's quite a way out from everyone else's but the benefit we have is because we're not like unlike some solutions we're not relying on the actual files timestamp we don't care we will just blast past that file snap I'm still restore it properly so if you've had a file recovered back from tape and they've not got the file creation dates right you don't have to worry this will still sort it out for you and again you can have problems if we have a look in here we have a single backup file you know it's not huge but it looks like it's a single backup if we actually run restore database against that it's past that or file I found out there's a full backup and a number a diff file and then a number of transactional files in there that need to be restored this is where I say you can get this problem with people back everything up into a single file they don't know about it but they still want to go to restore we can cope with that this is just picked up any facts this backup file has the same backup structure as the ones we've been doing before so it's got a full backup to transaction logs another full backup 3 transaction looks so we've been able to run through that find the latest backup file in there and then all the time that holds back up afterwards just say hell yeah that's super awesome and thank you that makes it so much more convenient that's all I've gone into places blind with them ask me to fix things and just found this terribly and a half backup file and the database is 2 Giga and it's like we need to recover it to yesterday and you need to pass it so yeah this is just making life easier for everyone huh thank you so much ok so you just take a sip water so the minute we've just been reading in RAW files or directories but we do support some other ways of getting the files in so the first and most common one is all Helen groans great maintenance solutions I expect many of you have seen these certainly I use them a lot and it's very reliable and very easy to so here we go classic I've got a full folder with some full backups in and the log folder weezing transaction logs backups in if you use the maintenance solution backups which we automatically know you're talking about an older Helen Grimm backup so we look under the path you provided and we look for the full the log and the differential backups and then we just process those so that will just go through one benefit of using the maintenance backup solution switch for all hangar and backups is we will short cut if we cannot find a file in the full directory we would just stop because we trust his scripts will always put them in the right location we can't always trust everything out there because we don't know how you've home-brewed your maintenance solutions or your own scripts but if you tell us it's a hangar in script we will check if the full backup is empty then we will just say no you've got a problem you need to fix it before we start trying to waste any more of your time the next common one people come across from is when you yourself do not have permission to see your backups but your sequel server instance can do I've seen this before you know the sequel server service account has permission to write to a UNC share but your normal day-to-day user account can't see it so what we've offered is assuming you have it enabled on your remote server is the XP dear true switch and this will go off and use XP dear tree to scan the files and this will also work with remote instances which I couldn't I'm sat at work in a small office at the moment and the remote is I've got to a would not like me having a demo on but if you specified you know my big server and want to read files off of its C Drive then you can use XP dear tree on there so it just means you don't have to physically be on a box so it saves a lot of time just a little bit of overhead all of us which ended up with sanity checking and we just look for back and CRM files we take pipeline input for files and here you can pass in anything I created a quick set of backup files which as you can see and not your standard backup file names but anything any of the other right indexes or anything but to be honest we don't again because we're reading the raw contents of the file you can name the whatever you want and we will still pick them up and process them so this is great if you've got massive files you don't what's going on with them again if you're striping in multiple locations we can take them in here just created some folders with some stripe backups in get child on the two and we'll just pull them from we'll pull everything in from both and we'll match them up just right backups don't need lots of typing in obviously when you know you're a strike back up you have to tell it all the disk files each time we'll just put that together for you if you want to cope with file creation so I've created a backup where fr was created and then deleted but across backups if we just run a point in time restore Terry's thoughts would be better you do it you see as we go through here every time it's just restored to files the MDF and the LDF if we go to a specific point in time here you can see we caught the fact that there was an end EF added that existed for ten minutes and this might be important it might be someone quickly added an extra file to cope with some work they were doing and that may be the bit of work that you need to go back and investigate so you need to get restore back to that point in time so again we scan all the headers we also get all the file information about so we can cope with things coming in and out and to stop you clobbering lots of things he says what conches the time I'm just gonna speed through some of these you can prefix where your store database files with a particular phrase so here we're telling to store all of them into a store time broke file folder but also prefix of all with restored so you could pick a unique value for that or create your own time stamps nap therefore you've got no chance of clubbin existing backup existing database you can also pass in destination log directory which will allow you to restore your log files into a different folder so any log file that you're restoring that will go in that for us if you've got separate drives for data and log you can split them to your heart's content if you're moving between database instances so you're doing an upgrade so you going from single 2012 to sequel 2016 you can tell it to use the destination default directories which will query the instance find out where the defaults are and push your files into those for you so if you want to give anything to see Program Files much more sequel server bla bla bla bla bla this will work it out for you so you don't have to also we're not bothered whether you pass in one database or many so here I'm going to pass in two sets of database backups once restored unclean one for to store timed if I can't give a database name now because it wouldn't make sense with two databases going in so we're just going to prefix and all restored again we've got two through restored they restored restore restore restore time diff and restored restore time clean and there's no limit the only thing we have at the moment is every database has have a unique name I do have a cunning plan to get around that blitz at the bottom a very long list so that's files but going back to ask passing functions across we can say n restores are so important that we will grant you additional time if you have it we do have another questions was that okay awesome we do have a number of questions and even if the person who has asked the question has to leave we will be as you know recording this session and putting it up on YouTube so feel free to take as much time as you like and we will get to the questions and we'll post everything up on YouTube oh that's brilliant thanks for that I know I finished for the day I'm just looking work has got better networking than my broadband at home awesome okay so yes so now we've gone through the files we've written this very much it ties with the rest of the tools so we show before get DBA back up history to return information about your back your backups so I have a second instance on my laptop secret Express 2012 instance and I get the DBA backup history the database restore time 2012 so here we have a number of full backups and time log backups and everything else so that has a full backup history so we can just pass that just straight into door to store it on to my local instance yeah so we've now restored from one twenty twelve instance straight into 2016 and yet we will still take we can still do points in time restores I just kind of when that database was created start real quick for a lot of people have to go the end one is asking for your email address so that they can contact you yep so just top if I just type it in here what people make notes everyone just grab that an in addition sort is also available on the sequel community slack in our channel so the channel name is DBA tools and the sequel community slack you can join it there's like two thousand other sequel server professionals that's at DBA tools io / slack and there's a separate channel for the parish o virtual group although it does still say vc where you can ask any questions and they'll be held for a little bit longer than the ones held in the gota meeting which will vanish straight away Stewart will I promise go and take a look in there and answer any questions you get over the next couple of days when you're using this and of course somebody said where do we get these tools DBA tools dot IO is the URL for that Chris you're going to see more questions there's a turn but I did want if Stuart are you I don't think that Stuart has completed his demo or did you know of a few more bits to go through but obviously people questions are about to head off I don't wondering a few now then carry on okay okay answer some questions so um we the first question we've got is answered can the command to be used if you use all the Holograms backup script that stores full and if log in different folders which you've shown is there a checksum issue got issue option there is yes yep checks on one of the backup issues important you have to then you know how to validate it so is there if you do those checks on validation will generate it but it's not really demo to show it in use of such any plans to support the parameter max transfer size we certainly can do yeah that's as you like that do list so if Lars if you or somebody could add it to the DBA tools and issues on github by if you go to DBA tools to i/o there's a link to the github repository there and if you open an issue you can at the linkers DBA tools got io / issues Thank You Chrissy just a note the difference in standard for connections for sequel server yet Peter those variants in naming of parameters through DB tools will be resolved in version 1 which the release date is Krissy at the moment we're hoping for June 1st and so far it's on target we'll know as we get closer yeah that is that is something that there's no standardization however I can say this that store its commands do support - sequel instance even though the examples use - sequel server so even though there isn't a very consistent feel across the parameter names and even the command names out we did put in a bunch of aliases so that it would still work i'm could you go through what the requirements are on the client to be able to run this you will need basically it deep if you download DBA tools module DBA tours comes with this mode doesn't it sorry DB elsewhere so the shell version 3 or above and you need to either have management studio or the SM Oh sickness cooled on the client machine and it'll connect to any version of sequel from 2000 up to V next on Windows and on Linux will be next and right version 3 yeah so starts commands are available within the DBA tools module and if you go to DBA tools io / install we there is an in-depth blog post about the minimum requirements and we we actually had a community member Sam just update the the web page today to list very straightforward the minimum requirements there that's at DBA tools that IO slash download and it is indeed Windows 7 with PowerShell 3 and then this is strictly on the client and then sequel server 2008 on the server the minimum requirement is no PowerShell and sequel server 2000 go on we believe we've tested all the sequel the backup and restore stuff has gone back to 2008 so that's something that we always we always try for so sorry apologies I've got a mute button on my headphones which if I lean forward meets me against my desk somebody asked so it's this DPL this dv8 tools have functionality to restore CDC enabled v's and piecemeal piece man not at the moment and CDC I'd have to have a play with I the problem with piecemeal is it's quite a complex operation so he's trying to muck you how we can handle that complexity I think we'd have to put some effort into that of not making it so complex you might as well just go into TC equal but coping with the fact that people do in piecemeal restores tend have very specific needs and yeah I'll have to look into CDC it should do because we just under the hood it's just running transact sequel restore statements awesome thank you so much okay I lost the rest of the questions on my screen and I'm trying to scroll up real quick Hey oh yeah this is a really good question there's actually two of them so if you strike backup files is there a recommendation for the number of files like one file per core potentially yes a lot slack open yes a lot would depend on your architecture your machines and you yeah if you've got enough to get a IO thread per core out to a different device then yes that will fly you may find you don't have that sort of bandwidth so there can be a little bit of playing with it to find out but remember the more stripes you have the more chance you've got of losing one of those stripes so if you've only got two stripes 50/50 they're both there if you if you've got 64 stripes going that's a lot more drives that couldn't fail or be having a bad day and again it depends if you as well the other benefit is I can't couldn't demo it because I'm really Express here but we support compress so for a lot of instances now compress is available since 2008 r2 in all editions that will also give you a huge benefit so you may find if you compress and just do two stripes you'll get the throughput you're looking for so it's not necessarily it's correlated to the cores but to the sand and network correct oh that's exactly yeah yeah the base of the you you're always gonna go to the speed of the slowest disk you're writing to cool so everything needs to be fast another really fun you can make it quicker you can make it the backups to a slow network share quicker by striping them but it's not going to get away from the fact that the network share is slow no no no no there was another question that I actually don't know the answer to I hadn't tested it only tested the backups the stripe backups is they restore from a striped backup set faster slower or the same speed as a restore from a non stripe backup faster good question it should be faster because again you're pulling it you normally i/o but but bound on a restore so if you can pull the i/o through faster you'll restore faster then but in my test and my testing has been faster yes then you're still gonna be limited to speed the disk you're writing - yes and I don't know if we had covered it but someone says what about doing a restore with checksum also to list any backups taken without checksum that comes from Patrick we could probably do that with to get a list of because without checksum and that's it that we're going to get DBA back up history yeah totally I would agree we pull that from the back set the backup set information yes we should be able to restore with check sum yep again it will just be one of the properties do you know a checksum is currently in the restore back yet the the get restore are sorry to get backup history command I don't know that's something that you guys can go to - DBA tools that io / issues that will redirect you to the github repository and then you can put in your request there and all of our requests are sent to Stuart thank God he takes care of them quite promptly he's amazing Stuart you might have as well um the if you if you take a backup from your production location and then you want to restore it to a new your development location but you copy the files first using copy item how do you make copy item how do you wait until the files are all there before you start your restore oh so you see right okay I probably just use if you throw out your scripts if you just have if you just did backup copy restore yeah then PowerShell will automatically do this command then do that command and it won't start restore until that one's finished if that makes sense all right yeah yes no you've got the question exactly right so Tim who has left efficiently there's copyright empath files destination local files with the recurse and says that partial and tries to run the command immediately but even if there's a false not there that hasn't been my my personal experience yeah I think that there might be something going on with with his system yeah so the if he's got something like you know if he's got a fancy foul fire on the end of it it may be telling me it's done blasted in cash yeah or maybe it's an it's being rehydrated if it's been D chipped or something strange like that yes yeah because if he might experience normally if you run commands one two three don't quibble with invoke parallel it's gonna go that order Claudio's mine southern cool claudio had made a comment and he says on mine too but i don't know if that's referring to that that claudio's experience is that copy item does wait until it's done or that he has issues with that as well so if you could hop in claudio and answer that there was another question by a guy named redwood that said testdb a backup now uses only full backups when when is dif going to be available so that was a command that i wrote back before stewart had joined the team and it's kind of it was kind of yeah i mean i only supported full at the time now that we have Stuart's commands that make it way easier for me I will write that rewrite it to make that to make a more robust command and we may rename it as well it may just be testdb a backup instead it says DBA last backup yeah I think there's a few backup oddities kicking around but it could just be tying it up so yeah yeah I believe at this time that is all the questions that we have you're welcome redwood and so if steward I would love to see the rest of your demo if you're still available to give it awesome excellent that's it and I'm fit and again when we do get DBA back up history you miss once you pass through one database again we're not bothered if you pass through multiple so we're going to grab three date my 2016 incidents personal injury restore again prefix and walrus with the restored name and prefix of the files we don't cover anything oh and some orange text that is always a bad sign something strange happened there it's interesting that should one of them failed I just take that out and read on the others mr.oat I'm clean they start while this is running just want to let you know you have a request to ensure that you show your slides at the end because people are clamoring for your contact info okay no problems yep I would certainly get that one back go that back right that's doing something funky with adding the log farm many many times so he just keeps their next bit which fool fixes as we said before with we rely on the other sent headers so this means we scan the head of every backup file we see so don't is some overhead in that especially for storing large amounts of files because each time missed again one with passing off to a sequel server to come back so we do offer one we taking input in from one of our own procedures we do offer the option of trust in the database backup history when that runs we don't scan the files and we do a minimal check for the LS coupon I mean sorry we check the file that she exists which the LS n chain is correct based on the history passed in and that you're not breaking any of the versioning rules about restoring back between different versions so it does restore disk all that so it doesn't mean that if your backup history is incorrect for some reason then you all have problems because we're trying to start the restore then throw out and that's worked he says try not sound so surprised I think the so that will make a bit of a difference he's not gonna be huge so here we're gonna run same query one We Trust DB a backup DB backup history on one without assuming this first one does an error it I don't know why I say it on that log file but the difference is it's that the seven seconds to five seconds so on this we're saving two seconds on the scanning files but these only have a couple of log files if you've been running ten minute log files 24 hours a day five days a week that was gonna mount up into quite a big saving you can also take the output from backup DBA database and push it across so perhaps you just want to duplicate a database a few devs they've asked for a copy so he's going to backup a database and push it through ok we still time clean restores restore time clone push the files in two locations here we go if we go into here we have a nice database already for the devs or if you want to practice a migration so again we pull a backup from sequel Express 2012 and we push it through to restore it back on the 2016 instance okay now these way you're pushing between different instances there is a reliance that you can see the same storage on both instances it's obviously this is all on my C Drive says not a problem but if you were restoring from server to server B you'd need to eventually you're backing up to some shared storage that both instances could see and the good thing is because we only copy only backups by default for the backup DBA database there's no impact in the original databases restore chain so you can just run this you impact because you're taking up i/o obviously but you've not going to break your restore chain you just clone it across quite quickly and of course this is DB a tool why we're going to stop with just one let's back up to databases and push them across with a new instance so here we go to databases very quickly pushed across into a new version a sequel server and ready for you to test wrap on so you know very very quick and simple if we just look there's the two down the bottom they're all ready to go and that is the end of the demos the slides yes the last one was just repeating the slack in the issues one I shall go back to my contact details there we go people to take those down I Christy was saying I'm quite often slack I've had a couple of days off just been writing this but yeah I'm really hanging around on there and get hub issues are very very useful obviously the nice thing about doing this or things you find out all the weird and wonderful things out there exists and you get to work with so that's excellent thank you it's really good have more questions or anything nothing there anything in this like Chrissy mine's crashed unfortunate I know my might my thing was taking up the entire screen and I couldn't see anything I'm like oh thank god okay I'm Bear let's see no it's just he's just you and me man we're we're in there talking excellent apologies hang on this the important play do clay do please ask your question here we go what about so it's a cliffhanger we don't know what about yet bit of a drumroll using seasonal sense captions Jarrett yep absolutely fine it supports the normal the usual deviate or sequel credential things yes you put grant your wines are connected with their I used to all with the windows or so so so what you saw in his demo was it's just using the windows authentication you can use the - sequel credential parameter to pass both alternative windows credentials and sequel credentials as well he said that esto Claudio is one of our developers and and he gets that question a lot so I just want to remind people that you can use both windows authentication and end sequel and in addition I think that it's important also whenever you're using storts commands that the it's important to note that the paths are relative to the sequel server itself just like when you use sequel server management studio and not your local machine so someone asked about managed service accounts can you talk more about that I'll be right back so flick as you mean at the active directory level or you expanded people this is this is managed service accounts if you can you see the questions pane Stewart's in your I'm just trying to expand it so my mind it's absolutely minimal okay he says managed service is a special ad account so I'm guessing yeah yeah cuz normally you haven't done you run your sequel service under managed service account and AD controls them having a randomized password that it resets for you yep believe yes yes correct I know those accounts you wouldn't really be using to connect to a database server to do this sort of work because one you shouldn't know the past but that's one of the points of them and two it's bad form because you don't know who's running the command it's like a lot of things if everyone logs in as administrator you don't know who dropped the database so people should have an account you can trace just you've got accountability yes I am cloudy says Peter says if you're doing automation you might want to use them I think you would use them for set it for running your sequel server and you would make sure the permissions were their fee to your backup yeah sure doing the backups yes I did want to use it for you backups you just get you just assigned that the permissions to run your job and then in that your Windows authentication would just take over so running shared to a task I don't know if you can assign the permissions to that user and then yeah that should just pass it through and Claire says how deep can you go in a folder level for the restore in the backup of commands or the restore commands and resume with our default file readers if you just give it a directory path it only scans that folder if you give it having Guren the maintenance solution backups all the scripts it will recurse only into the full the log and the diff folders xpd a tree currently recursos down the entire tree so everything under the folder and obviously because you can pile up in the output from GCI so i'll get child item you couldn't go as deep as you want there is nothing to stop you piping endings are C Drive of a machine if we sit there for a long time scanning all the files and kicking out the ones that aren't see called backups but you could do it i iived I did this equalised dogfooding this 22 migrations earlier this year and i recursed into 102 folders all containing hallander inside backups to migrate stuff across cluster instances and it didn't mind doing that it just takes some time to process the files yeah croaky is it possible to ship the ssi SDB in the backup restore function yes yeah you can do it but it will it totally boned a couple of my upgrades because it didn't set it didn't set a specific bit back you know that was like hey this is another system database and so my my SP update didn't recognize it it tried to update it and and it yeah it was it was tough yeah exactly my experience that it's a different beast it looks like a database but you need to have CLR enabled oh that's our business which did not area cross I just like to say it works with Linux as well I've just run a test and apart from my databases with the wrong sort of characters in them it has backed up my database of successful that's probably s mo playing up I'd thought Qaeda party why some I want society be Krissi's putting a request for an enhancement to do system databases Oh so that could probably be on the end of that one because once we've all read code with handling master we can crop requires restarts and trace Flags then we should hope to get to do the CLR enabling and do a society be excellent it's impossible to restore the sequel server manage backups MS as you're with parish shop don't know again that that's one things on my list of things to do is investigate that and how we can talk to your you know obviously if you've got your setup in a VM out there is no different as long as you can access to it but the actual secret of your stuff I've not looked into too deeply you know we do have a team member who is working on some of the is your stuff so we can pass that information on to him as well because I would be curious I know that I was really disappointed when just jumping into as you're in in general I thought that I could just backup and restore and then it wasn't that at all and so that would be really convenient for both Linux and as your if we could take care of that for people yeah that's definitely at least the backing up and restoring from Azure storage now what he doing but there's some stuff under the hood that needs doing so that's the server has to be configured to know the security keys and then it becomes easy but yeah a lot of that initial setup so I think that will be you know almost a separate piece of work because got the plug-and-play architecture now you should be able to bolt it through that's true thank you thank you so much sure the pendulums and thank you hour and a half of your time that's really we really appreciate it no that's absolutely fine now I couldn't talk about this for ages you know Brooke well what I think brought first at the very first version this session about eight years ago that was my that was the first session I ever presented away from my home user group and I was doing my session on Stewarts Mac which was still thank you very much again sir there's a selling point I'm about to sell that Mac has been friends for and much longer than I knew yeah yeah yeah well yeah I think the UK we've got a reasonably small secret user group community yeah we're actually reasonably close compared to the States or Europe so I will drive down to it's a recession and I don't rob was up last week for election in Nottingham I forgot about that fact less than 50 minutes from where I'm sat now right can somebody press stop recording and we will and fortunately this this week it's been a lot better as far as our video and audio quality has s con so I don't think that I'll need your session and store it but please do convert it and then I'll let you know if it didn't come through for me yeah I'm written is gone you know it'll pop up once the once the webinar has completed entirely it'll pop up and say do you want us to convert it now

Channel: PowerShell Virtual Group

Views: 4,088

Rating: 5 out of 5

Keywords: sqlserver, powershell, dbatools, backup, restore

Id: -q074XVYVPw

Length: 80min 7sec (4807 seconds)

Published: Tue Mar 21 2017