Database Refresh Automation in MS SQL SERVER

and meanwhile I will share my screen can anyone confirm that uh are you able to see my screen yes oh yes yes okay so I will start this so guys uh this session is all about like Auto refresh so sometimes like uh I am we are dealing 40 clients so every client have some boundary certain requirements so some customers will ask uh will ask you to do this activity like every Saturday or Sunday or evening some customers will ask for like an times like it means they will ask some of the I'm interested like at the big days as well there is no particular requirement that they want to do like on this day I'm there so some clients will be like that so first of all this act you know that simple Auto re-brush is simple like back taking and backup uh of the copy only of the product server and really restoring that the only problem is uh permissions and so what are the permissions we are having in the test server so that might be different in uh products or in the broad database so so deep brush means just like uh replacing the diamond replacing the updated data with with the source permissions sorry with the destination permission that is called exactly what the database refreshes so here I have a written some of the action plan so if it is requiring uh it is meeting your client requirement you can follow the steps or you can add your own steps or you can delete that unnecessary steps what you you might or not require okay so just wait here I will accept once again yeah so here so first of all I am taking the database of the source server and it means a broad server database into the local path if the local if you sometimes if your network is passed I I mean you can directly take that backup file to the uh Network to the network path only so if sometimes okay so I mean to say like if the network is not first you can take that uh to the local path only here I have taken to the local path only so once I have done that once you take this database right now I am doing the demo on adventure Works 2019 database this is consider this is a Nike my production database see this is the issue every time I need admittal okay so I have I'm going to take the backup of this database to the local path along with the compression okay now what I am doing here is I am using Windows command okay so this is the local path okay this is the local path for this okay but I I cannot access I mean uh I the for the security I mean uh I I have kept this as a one source server to destination right uh so that's the reason I have changed this Azure to the network path so I have um you I'm using Windows command Windows copy command from this folder I am setting to this to the uh and you just copy this BAC file to this destination server for this reason I am using the all the network path only why because I don't want to put the button on the network through SQL Server that's the reason I have divided this step okay uh through I mean I have separated these two steps okay and for example if you are having uh if network is passed you can directly take that and you can avoid this step okay the Second Step second is um the part is I am taking the backup of the data uh Source the destination permissions into the temporary table that table uh actually I took this step permission backup to the global variable after the successful of execution of the global variable the connection is getting disconnected to this reason I mean uh the permissions are all are all getting deleted so that's the reason what I have done is instead of putting the global variable and keeping the session active what I have done I have created a table in the temp DB and I am putting all this information into the temporary table and now what I am doing here uh backup with the local folder uh so for example let's assume that up to third step I have completed so in between of that after completing a third step So currently instance got restarted our systems are done whatever happened it happened okay so if you know that whenever system got rebooted our instance got rebooted all the data which are storing in the tempdb will get deleted right so at that time okay so even though if you uh I mean so there will be chances of losing the permission backup due to this reason what I have done I have added additional step okay so I am taking the database backup permissions as well to the local folder okay I will show you that one so in the log file I am going to add this step so that it will add the all the this permissions into the local drive with along with the date so that it will be useful for us what are the permissions that are there from very long time and coming to fifth step I am taking the adventure box database of in the destination server okay why because some clients are very sensitive and like very irritating so I am having 40 clients in that two our clients are like that so if they don't like that what they have a new data okay so sometimes application will make uh crash to do this all new data or new schema changes so sometimes what happened okay uh so my application application they don't know that either we are having the destination server backup offer this simple ask that okay please restart the database we don't have we don't require as of now the database refresh please keep it as it is as it has in the previous so they will ask this kind of questions so if they ask like that we will be in trouble right why because we don't have the destination backup so if your clients are having like that to be unsubset why because as a DBA we want to be protect our database at the same time we want to protect ourselves as well okay so you can say that this is our action plan this is for optional for the kinds of who are very sensitive for the data Okay the third step what I am doing here is we have so many read connections are there it will not allow to go into single user mode or uh we did not allow to the restore the database and everything that's the reason I have um I have added this command so so that if what are the connections it will be there active connections it will get terminated what is the first thing after that it is going to take the single uh user mode immediately okay and after that we are going to restore the database this is all one step only okay from six okay all these these are all like sub points okay so once we have restored the database okay and what we are doing we are going restoring the permissions okay which is used from step three okay step four is optional why because it is we have kept in the local folder if we get any error from step three we can use we can use step four and from step four we can do that okay and what are that um we once we restore the permissions if there are any orphan users are there okay we need to fix it if the windows orphans is not able to fix that for example uh our phone users are exist and SQL Server is also got exist so if we uh if we fix the orphan user script okay it will fix automatically if they they are just the two sides will be there okay uh if we use our phone users script it will get linked okay so sometimes when uh the users the S scale user is there but login is not there so at that time so it will generate uh like I don't want to any orphan users so if you have that Commander if that kind of requirement as well using this comma you using this command you can delete the orphan users which are not required for your environment this is all about uh DB refresh so what year what how exact here is I have done automatic known you know did do anything okay so if you have anything customer schedule the particular requirement is there so you can you have to schedule accordingly for example you have a requirement like uh uh you have to restore at like 10 30. okay so you have to come to one conclusion why because these two the about two points or for you have to do in the Source uh Source server so this backup will take for a customer asked you to restore the database at 10 30 or 10 Saturday evening 10 30. pm okay so if you ask like that customer will ask that you have to analyze this okay for how much time it will take the word backup like half an hour 30 minutes so how much it will take that backup file from this source to destination like 15 minutes means 45 I mean it means you have to configure at like uh 10 30 I mean uh like 10 9 45 you have to schedule this as a 9 45 so that automatically that uh it will get restored at 10 30. so you have to analyze that kind of schedules and everything you have to uh schedule okay so now I will show you that one thing so now I am here is my one drive is there so here my source database I am going to delete destination I am going to delete backup I am going to delete so first if I execute this command first it will get created in this backup file after that it is going to get uh in the destination server it will get copied okay so first I am connected to the source server which is SQL 2019 named instance so now okay Adventure works and security SQL Server agent so DB where is DB refresh yeah here so here DB flash of source day server here if you see here I am taking the backup of database with copy only if you see my command see it is a backup only okay the second command is is moving from source to destination so this is the source back and destination okay so why I have given because here you can give the physical path but in the like if you're in an environment it might be network path mostly right so that's the reason I have selected this as a network path only okay so I have just you copied Windows user command Windows user command so that uh it will uh here you need to select operating system command okay so this is done now what I am doing whenever if customer has requirement okay that's fine if customer is not having reference just I need if you if you ticket got from the respective client only who have authority okay so that you just whenever you take it easy you just assign the ticket on your name and you simply execute this job so right as of now see in the dish it got competitive source is also completed and destination is just just copied that in in your in the real time destination database is in your like stationary server I mean in the opposite okay Target server right now both missions are in same my mission only that's the reason you are seeing like that okay to simulate the real time I have just uh acted like I mean I just created the folder like this okay so it was done okay now coming back to this I am connected to the destination server so here I am having Auto defresh activity you just uh click Start so start I got this error no problem so let's see that what could be the error or anything so I got an error at fifth step okay what is that the file add-in device.bak is correctly formatted cannot be read or cannot be restored terminated okay so I have some issue with this okay and let's see how it goes okay simply what I will do here is come on I will run from this oh I will tell you what exact reason why because if the compression is enabled not enable and I will fix this issue okay no problem so what means but if I'm correctly formatted okay I will run from fifth step onwards okay hmm here are sure is it sorry to interact is it the permission issue no it's a take a single user mode right so what it uh where it is failing here is fifth step okay so what is the fifth step here so if you see here it is taking the single user mode and restoring that [Music] might work so why it is working not fine you have mentioned that uh otherwise I think said the single user mode right you have kept it uh twice so can you just uh okay yeah good catch you will see so I will delete this once seal same issue I don't know why it is failing here do this ah I got this take restoring is getting failed [Music] it is so you have deleted backup file I think uh in few minutes back can you just check that backup file is there so it is taking the destination so with BAC file learn non-upload okay so no problem we'll see what I will do here is uh just wait this is reverse man uh Charles you can add you can do this one out file you can add out file and run the job you may get exact error we'll do that as well so I think restore I am going to put this one I suspect right you have mentioned uh the file ID equal to five something right so file it is file equal to five right that means like if you take the backup into the same backup device with the same name right so uh inside the file it will give you the number right yeah but since we have only one backup file it is looking for the backup file which is marked as file since yeah we don't have anything like it might get failed so I think we can remove that yeah okay now set it done so I will start from PI let's see so you will see he will die here so here we will see uh 2019 cannot be restored with move command did I select replace overwrite I have selected right already so no problem we'll do one thing okay device sorry guys Safari killing your time sometimes uh it will get irritated so I think it's an a permission issue Pak oh God okay now I'm going to start here simply show see the same step I got succeeded uh from here Okay so as of now what I will do I will check that option and I will give the whole script for you so right now I am going to uh start with the step six is that fine for you guys yeah at least for me it is actually yeah so now what I'm going to do here is I am going to restore the database permissions so that permissions it will be just uh it is taking from the uh temporary folder which is created in the tempdb once it is done so it is done okay so let's see that all the steps got completed or not six seven go eight completed okay so now I'm going to uh do you have any what I am doing here is so I will tell you here I am coming to tempdb okay so where is my temp DB temporary table so these many temper tables it got created so now I will tell you one so that got already it's got deleted so now now DB refresh so I'm I will open one each and every command and I will show you the syntax as well so you know that how to take the backup to the dish uh to the uh permissions backup see this is the command so what it will doing it is going to create the temp DB one table in the temp DB okay so after that it is go that I am creating a Crusher so the cruiser will go to the adventure Works database and it's going to fetch all this information okay user Grant and everything okay and it is going to set it is going to upload in the tempdb dot output file okay so right now I have deleted I have just uh come uncommented that uh two commands Okay so why because we need the data right at the same time we don't require it so what I have done uh I have uh reached once it is done here I have removed this here why because if we keep on putting the data the data uh I mean data will get increased and at the same time uh in the real time we cannot I mean use tempdb for every purpose items once it is done we have to vacate it so that's the reason I have removed that I have dropped uh that 10 pdb table okay so in the sixth command now I will show you the second command so if you see here so I am taking the same backup permissions for safety purpose so I am redirecting to the output to the local folder see in this into this destination Forex is for example I am going to do destination server see the permission backup is there by any chance if I uh the tempo TV table got deleted a drawable or restart whatever is it okay I am safe side why because the backup copy of permission uh source of destination permission backup is there okay so if you see an open here okay now if I you if you see here I got this permissions okay as of now there are there nothing is that that's the reason it got uh I mean there is a uh no objects levels are there okay so it is got directed this is their job name okay and uh this is the time okay so at the same I have backup user now here if you come here so it is uh taking the destination database backup for safety purpose if you don't if the customer is not sensitive so you can remove this step and this is the we are all the killing active transactions and here we are taking the database into single user mode after that we are resouring the database and I will uh in the I will before going to upload this video in the YouTube I will check this command and I will uh or I mean I will provide the updated code okay so and after that we are going to say you know that how to I will show you this command as well so this is the command okay so coming now here uh if you see here click on edit you know this right uh we are taking the single user mode and we are restoring it so uh by using query window it got success but uh here I don't know why it is prompting for different error I will look into that so now we are restoring the permissions now I will show you one quite interesting thing here I am I'm using the cruiser see if you see here select statement from tempdb table dot ID so what is the output I am getting uh from the temporary output table that I am going to execute in the database of Adventure works okay so you will get to know okay either yeah this is the command uh for just restoring the database permissions this is the main thing here so your backup and restore everybody can do this is the only thing which is highlight for this session now fix our phone users so this is the command which is going to fix the orphan users and this command uh will tell you okay how many users got it fixed and how many will be there still exist if you see go on here I am just see so they are totally zero users as of now that's the reason this is total fixed zero user fix is also zero okay now so if you have any orphan user it is going to delete that so now I am going to kind of execute this command so it is not it now so what I will do here I will simulate one situation here okay so now Adventure works so I am going to create a login with arsha123 and I am going to drop the login so if I use this command it is going to delete the arsha123 user why because it is like orphan user and we are using this syntax which we need to drop that orphan users which are not fixed by that okay so if I refresh here go to database security users see there is no user call like asha123 provide which is called as a previous uh orphan user okay so this is all about uh like Auto debris the main thing here is uh like taking the backups and putting into the temporary table of tempting to Temp DB and fetching that user and executing an adventure Works database using a cursor okay so what I will do here I will send this document and as well as the scripts in the Google Drive and I will put into that with the latest update description everything so that you can go through that and you can execute these commands and you just replace your database names okay and also what are the steps which are not required for your environment so that's All About Auto DB DB refresh if any users are I mean if any doubts are there let me know hello foreign guys it's time for questions yeah thank you for sharing this but I have a question um in my environment I have a large database like 150 GB database is there okay like while I am copying uh copying from one server to other server it is taking like uh almost one day it was taking for copy okay is there any uh Powershell script or something like that we can copy fast because uh my environment they are telling it yeah they are telling that daily they have to take the backup and restore it okay so one thing here if the backup is happening through Network so you need to con you need to update the drivers of the your network adapter you need to increase the speed of that okay so if you I it didn't mean that if you go through Powershell it will get fast so first of all you need to contact your network team so you need to collaborate with that at that and you need to go through third listing so first we need to put the 150 GB and you need to ask that how much it is going to what is the transfer rate for that so you need to contact networking team only for that so using Powershell or thesis call script whatever it it doesn't mean that so it doesn't provide that much of freed okay so it all depends upon the drivers network drivers only yep yep apart from this any doubts guys please hello yep yes can you please describe on what is the network could drive or restoring the databases yeah network adapters I will show you so if any speed is there I will let you know here so currently I am using this wi-fi Wi-Fi adapter if you let me know this adapter is like just assume that so uh like my adapter is having the speed of 10 GB okay my internet is having like 100 GB so if I if I say to somebody else so my network is very fast so my network is very slow it is that mean any sense like I am having fast like uh I'm a habit like a a very very huge Broadband network speed but my network adapter is not able to cast that much of speed okay so I mean network adapter means this is this adapter so what you are having in your server so your server uh network drive have should that much of capacity okay okay thank you so in the uh apart from these two guys anything else or you want to add something for conducting the decision if possible can you conduct them or it will be useful for all sure sure no problem from Mass evening onwards I am searching I mean I am preparing that uh all these steps okay okay so whenever the time will there I will randomly share my uh like meeting links so if you go through that it will be useful is it possible to show Powershell next time if it if it's possible using yeah correct I will try why because I need to go through testing as well why because see even though after complete if you see here uh like I mean go to refresh so before doing anything so I will test on first myself okay once it is doing well then I I only come to the okay the this is working fine let's show to our people so if you see here you study she previously it got success right see one two three four five everything got successful so after that suddenly it got an error yeah yeah that's common we need yeah we can troubleshoot it that's that's not an issue but you are doing good yeah yeah if any errors we will look into it all all we can troubleshoot together yeah yeah thank you no brothers Commando I I experienced that error but uh we in the meeting we are so many members are there right so the reason so I need some peaceful time so that I can work on that I will work on the Terrace yep can you share the script whichever you are using yeah sure I'll see everything uh whatever I have done in the YouTube videos or whatever it is I are the Google drive itself I've already uploaded and everything for this Auto script in that cool drive only I am going to create auto DB refresh folder I am going to correct all this okay yep yeah anything else hello yeah so if no questions so we can wind up this session thank you guys thank you all for the attending the session yes anything else yeah no no bye bye thank you thank you [Music] hello team uh I have just fixed that issue so I have just changed the backup command and everything okay so like what are the key SQL command that I used to restore the same SQL command I have I mean I have replaced with that in the fifth step so now coming to the source server like um I am going to Source over like here so selecting our DB here is my DBA refresh so I am clicking click Start having two jobs I it got successfully executed yeah it is done now I'm going to the destination server so here now click Start so it got successful now if you see history all jobs got completed successfully if you see here previous like in the fifth step I think some having uh that I am having some issues between the folder as well okay so that's after changing the folders it got successfully uh restored so yeah so I'm going to give the two scripts okay I right click and then everything so like that I am going to give the two scripts and you just replace that and uh with your replace with original database values and go through your requirement and you can add additional and you can remove that uh steps that are not required I hope you may like this video thank you

Channel: MS SQL DBA Tech Support

Views: 3,998

Keywords: microsoft sql server, database refresh, database refresh activity, db refresh in sql server, db refresh automation through sql server agent, Automation, MS SQL Server, Database, Refresh, SQL, Efficiency, Workflow, SQL Server Maintenance, Scheduled Tasks, Database Administration, Streamlining, Productivity, SQL Server Automation, Database Maintenance, Data Refresh Strategy, Database Automation Tools, SQL Server Automation Script, Data Refresh Automation Process, sql server

Id: Bi6GUHo_PFo

Length: 38min 27sec (2307 seconds)

Published: Mon Jul 17 2023