如何自动刷新SQL Server数据库

最新推荐文章于 2024-05-20 06:02:10 发布

culuo4781

最新推荐文章于 2024-05-20 06:02:10 发布

阅读量3.9k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 java python 大数据 linux

原文链接：https://www.sqlshack.com/automatically-refresh-sql-server-database/

本文介绍了如何通过SQL脚本和SQL Server Agent作业自动每天晚上从生产数据库备份中还原测试/开发数据库，确保开发人员每天早晨都能使用新鲜数据。该方法适用于SQL Server Standard Edition及以上版本，需要DBMail配置和SQL Server代理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

As a DBA, I am often asked to automate tasks. In addition to this, I generally work with a lot of developers that need to test with data refreshing on a regular basis. A lot of the time, I am asked to either stand up a new test environment or overwrite/refresh an existing one. 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. 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. I cannot tell you how many times I say “I have a job for that” when a client asks me to automate a task.

作为一名DBA，经常有人要求我自动执行任务。除此之外，我通常与许多需要定期进行数据刷新测试的开发人员一起工作。很多时候，我要么被要求站立一个新的测试环境，要么覆盖/刷新一个现有的环境。现在，这些任务并不难完成，但为什么不将它们自动化成一个SQL Agent作业，并在需要时运行它，或者安排它并让该任务完成所有工作呢？如果您已经阅读了我以前的文章（是什么导致数据库运行缓慢或Simple SQL Server自动用户审核），您将知道我是自动化可重复任务的大力支持者。最好的部分是，如果您像我一样有多个客户端，则可以编写任务脚本，更改变量并一遍又一遍地使用它。当客户要求我自动执行任务时，我无法告诉您说多少次“我有工作要做”。

现实生活中的情况 (Real Life Situations)

Recently a new client asked me to do such a task. 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. They wanted to use the latest full back up which ran the night before at midnight. So, in the end, the data was really never older than a day. For this very simple task, I decided to compose a SQL Script and put it into a scheduled SQL Agent Job. For this to work, you will need at least SQL Server Standard Edition as the agent is not available in SQL Express Edition. I built this on SQL Server 2014 however it should run on versions beginning from 2008 – current.

最近，一个新客户要求我做这样的任务。我需要解决的问题是每天晚上从生产数据库的副本中还原测试/开发数据库，以便开发人员每天早晨都可以使用新数据。他们希望使用前一天晚上午夜运行的最新完整备份。因此，最终，数据确实从未超过一天。对于这个非常简单的任务，我决定编写一个SQL脚本并将其放入预定SQL代理作业中。为此，您至少需要SQL Server Standard Edition，因为该代理在SQL Express Edition中不可用。我是在SQL Server 2014上构建的，但是它应该在2008年至今的版本上运行。

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 have added <CHANGE HERE> to indicate where the variables are that need updating. Seriously, it cannot be any simpler than this!

下面SQL脚本将导航到文件系统上存储备份的源目录中，并将最新的完整备份还原到目标数据库中，该目标数据库是脚本中的变量。我添加了<CHANGE HERE>来指示变量需要更新的位置。严重的是，没有比这更简单的了！

您将需要什么 (What you will need)

SQL Server Standard Edition or higher
SQL Server标准版或更高版本
SQL Server Management Studio
SQL Server管理Studio
New Query Window
新查询窗口
The logical Name of the source database
源数据库的逻辑名称
The database file name and file locations of the source database files
数据库文件名和源数据库文件的文件位置
The source location of the backup file
备份文件的源位置
SQL Server Agent
SQL Server代理
DBMail enabled and configured
启用并配置了DBMail

剧本 (The Script)

The script is fairly simple. You will need to change the script below with your variables for the file location. First, it looks into the backup folder for the latest backup file. Then it creates a temporary table to house the file names. It will then select the latest backup file to use from the temporary table. 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. 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. I have also enabled notifications to email me if the job fails. Both of these last 2 items are just good practice and I usually do these on every SQL Agent Job I create.

该脚本非常简单。您将需要使用变量来更改以下脚本的文件位置。首先，它会在备份文件夹中查找最新的备份文件。然后，它创建一个临时表来容纳文件名。然后它将从临时表中选择要使用的最新备份文件。最后，它将使用临时表中列出的最新备份文件执行还原，并将其从备份文件系统文件夹中获取。除此之外，我还创建了一个审核日志以捕获每次作业运行时的作业活动，如果作业无法帮助解决问题，我通常使用该日志。如果作业失败，我还启用了通知以通过电子邮件发送给我。最后两个项目都是很好的实践，我通常在我创建的每个SQL Agent Job上都做这些。

Copy and paste this script into a new query window and change the variables marked /*<CHANGE HERE>*/ below.

将此脚本复制并粘贴到新的查询窗口中，然后更改下面标记为/ * <CHANGE HERE> * /的变量。

–get the last backup file name and path Drop table #BackUpFilesAvailable Declare @LatestBackupFile varChar(255) Declare @BackUpFolder varchar(255) set @LatestBackupFile = null set @BackUpFolder = ‘/*<CHANGE HERE>*/ C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\Backup\’ create table #BackUpFilesAvailable ( FileName varchar(255) , levels int , files int) –get all the files and folders in the backup folder and put them in temporary table insert into #BackUpFilesAvailable exec xp_dirtree @BackUpFolder ,0 ,1 –get the latest backup file name select top 1 @LatestBackupFile = @BackUpFolder + FileName from #BackUpFilesAvailable where Filename like ‘%.bak’ order by filename desc –execute the restore RESTORE DATABASE /*<CHANGE HERE>*/ [TestDevelopmentDB] FROM DISK = @LatestBackupFile WITH /*<CHANGE HERE>*/ 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

–获取最后一个备份文件的名称和路径删除表 #BackUpFilesAvailable 声明 @LatestBackupFile varChar （255）声明 @BackUpFolder varchar （255）设置 @LatestBackupFile = null 设置 @BackUpFolder = '/ * <在这里更改> * / C：\ Program Files \ Microsoft SQL Server的\ MSSQL12.SQLEXPRESS2014 \ MSSQL \备份\” 创建表 #BackUpFilesAvailable（文件名VARCHAR（255），级别INT，文件INT）-get所有文件和文件夹备份文件夹，并把他们在临时表插入到 #BackUpFilesAvailable EXEC xp_dirtree @BackUpFolder，0,1 -get最新备份文件名选择顶部 1 @LatestBackupFile = @BackUpFolder +文件名从 #BackUpFilesAvailable 其中文件名LIKE '％.BAK' 由 filename 递减顺序 -execute还原 RESTORE DATABASE / * <CHANGE HERE> * / [TestDevelopmentDB] FROM DISK = @LatestBackupFile WITH / * <CHANGE HERE> * / MOVE N'ProductionDB” TO N'C：\ Program Files文件\ Microsoft SQL Server的\ MSSQL12.SQLEXPRESS2014 \ MSSQL \ DATA \ TestDevelopmentDB .mdf' ，将 N'ProductionDB_log' 移动到 N'C：\ Prog ram文件\ Microsoft SQL Server \ MSSQL12.SQLEXPRESS2014 \ MSSQL \ DATA \ TestDevelopmentDB_log.ldf' ，替换

运行脚本 (Run the Script)

If all variables are changed correctly, you will see a confirmation in the messages window below the query window.

如果正确更改了所有变量，您将在查询窗口下方的消息窗口中看到确认信息。

代理工作 (The Agent Job)

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. 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. Note the SQL Agent must be running or this branch will be disabled.

现在，这是最好的部分！通过将作业放入SQL Server代理作业并安排其在特定时间运行来自动化该作业。要将其添加到代理作业中，请打开SQL Server Management Studio，然后将左分支向下扩展到SQL Server代理，然后将其展开。请注意，SQL Agent必须正在运行，否则此分支将被禁用。

Then right-click on Jobs and select New Job and give the job a name. Note that the owner column will default to you the logged in user. I usually change this to SA or a user with SA but that is up to you.

然后右键单击“作业”，然后选择“新作业”并给作业命名。请注意，所有者列将默认为您登录的用户。我通常将其更改为SA或具有SA的用户，但这取决于您。

Click on Steps in the left menu.

单击左侧菜单中的步骤。

Click on the New button in the lower portion of the screen. When the New screen opens, give the step a name in Step Name, paste the modified script from above then click on Parse to test the script. If no errors, then click Advanced in the left menu. Change On Success Action to Quit the job reporting success.

单击屏幕下部的“新建”按钮。当“新建”屏幕打开时，在“步骤名称”中为步骤命名，从上方粘贴修改后的脚本，然后单击“解析”以测试脚本。如果没有错误，请单击左侧菜单中的“高级”。更改成功操作以退出作业报告成功。

审核工作成功/失败 (Auditing Job Success / Failure)

One thing I like to do on all jobs I create is to create an audit log so if there are failures or issues they will be captured in a text file on the file system. So complete the Output file section as I have below.

我喜欢对我创建的所有作业执行的一件事是创建审核日志，以便在出现故障或问题时将其捕获到文件系统上的文本文件中。因此，如下所示，完成“输出文件”部分。

In addition to this audit history, I also like to add an email notification on job failure so that I am notified if such occurs. To do so, click OK on the screen above and then click on Notifications in the left menu.

除了此审核历史记录，我还希望添加有关作业失败的电子邮件通知，以便在发生此类情况时得到通知。为此，请在上方的屏幕上单击“确定”，然后在左侧菜单中单击“通知”。

Check e-mail and add the recipient. Please note that you must have dbMail enabled and configured in order for this to work as well as operators setup on your server. I’m not going to go through how to configure dbMail or adding operators as they are already readily available from other sources. Once notification is completed, click on Schedules in the left menu then click new.

检查电子邮件并添加收件人。请注意，您必须启用并配置dbMail才能使它运行以及在服务器上设置操作员。我将不介绍如何配置dbMail或添加运算符，因为它们已经可以从其他来源轻松获得。通知完成后，单击左侧菜单中的“时间表”，然后单击“新建”。

Add your run schedule for the restore job. For my example here, I have scheduled a daily job that runs at 4:30 AM with no end date.

添加还原作业的运行时间表。在这里的示例中，我安排了一项每日工作，该工作在凌晨4:30运行，没有结束日期。

结论 (Conclusion)

This is a quick and easy way to restore a database via a script that can be automated. Again, to automate this, I put a script into a SQL Agent Job and scheduled it to run on a daily basis at a specific time. I also created an audit log in the event of failures.

这是通过可以自动执行的脚本还原数据库的快速简便的方法。再次，为了实现此目的，我将脚本放入SQL Agent Job中，并计划将其每天在特定时间运行。如果发生故障，我还创建了审核日志。