Note that the owner column will default to you the logged in user. Then right-click on Jobs and select New Job and give the job a name. Note the SQL Agent must be running or this branch will be disabled. To add it to an agent job, open SQL Server Management Studio and expand the left branch down to SQL Server Agent and expand it. Now here’s the best part! Automate the job by putting it into a SQL Server Agent job and schedule it to run at a specific time. If all variables are changed correctly, you will see a confirmation in the messages window below the query window. RESTORE DATABASE /* */ FROM DISK = WITH /* */ MOVE N’ProductionDB’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\TestDevelopmentDB.mdf’, MOVE N’ProductionDB_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\TestDevelopmentDB_log.ldf’, REPLACE Run the Script Select top 1 = + FileName from #BackUpFilesAvailable where Filename like ‘%.bak’ order by filename desc –execute the restore Insert into #BackUpFilesAvailable exec xp_dirtree ,0 ,1 –get all the files and folders in the backup folder and put them in temporary table Set = null set = ‘/* */ C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\Backup\’ create table #BackUpFilesAvailable ( Both of these last 2 items are just good practice and I usually do these on every SQL Agent Job I create.Ĭopy and paste this script into a new query window and change the variables marked /* */ below. I have also enabled notifications to email me if the job fails. In addition to this, I have created an audit log to capture job activity each time the job runs which I generally use if the job fails to help troubleshoot. Finally, it will execute the restore using the latest backup file listed in the temporary table and take it from the backup file system folder. It will then select the latest backup file to use from the temporary table. Then it creates a temporary table to house the file names. First, it looks into the backup folder for the latest backup file. You will need to change the script below with your variables for the file location. The database file name and file locations of the source database files Seriously, it cannot be any simpler than this! I have added to indicate where the variables are that need updating. The SQL Script below will navigate into a source directory on the file system where the backups are stored and restores the latest full back up to a destination database that is a variable within the script. I built this on SQL Server 2014 however it should run on versions beginning from 2008 – current. For this to work, you will need at least SQL Server Standard Edition as the agent is not available in SQL Express Edition. For this very simple task, I decided to compose a SQL Script and put it into a scheduled SQL Agent Job. So, in the end, the data was really never older than a day. They wanted to use the latest full back up which ran the night before at midnight. The problem that I needed to solve was to restore the Test/Development Database from a copy of the Production Database every evening so that the developers would have fresh data to work with each morning. Recently a new client asked me to do such a task. I cannot tell you how many times I say “I have a job for that” when a client asks me to automate a task. The best part of this is if you have multiple clients like I do is that you can script out the job, change the variables and use it over and over again. Now, these tasks are not difficult to complete, but why not just automate them into a SQL Agent job and just run it whenever you need it or schedule it and let the job do all the work? If you have read some of my previous articles ( What is causing database slowdowns or Simple SQL Server automated user auditing), you will know I am a big proponent of automating repeatable tasks. A lot of the time, I am asked to either stand up a new test environment or overwrite/refresh an existing one. In addition to this, I generally work with a lot of developers that need to test with data refreshing on a regular basis.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |