windows – 每晚备份数据库

前端之家收集整理的这篇文章主要介绍了windows – 每晚备份数据库前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
问题:是否可以将每晚处理完整数据库的过程自动化到同一服务器上的开发数据库中?

软件:Windows Server,sql,sql Server Management Studio,ColdFusion

背景:我有一个开发数据库,​​我想用生产数据库中的数据刷新.如果可能的话,我希望每天晚上都能这样做.这样,开发站点只会落后一天,对数据所做的任何更改都将在每晚被覆盖

是否存在某种可以处理此类请求的计划任务或存储处理?

Back up a database nightly

I have a development database that I would like to refresh with the
data from the Production database. I would like for this to happen
every evening if possible. This way the development site will only be
one day behind and any changes made to the data will be overwritten
every evening

下面是应该正常工作的概述步骤,你显然需要为你的环境插入变量.如果需要,所有这些也可以使用sqlCMD完成,但假设您有sql Server和SSMS,我将假设您在此实例上也有sql Agent作业,并且您对它有点熟悉.

通过SSMS在sql Server中创建备份设备:

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk',@logicalname = N'DBName',@physicalname = N'S:\MSsql.1\MSsql\Backup\DBName.bak'

通过SSMS创建sql代理作业并安排在指定时间运行备份:

Backup database DBName to DBName with description = 'DBName Backup',skip,init

通过SSMS创建sql代理作业并安排恢复完整备份的DB指定时间:

ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DB_Name SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [DB_Name]
FROM DISK =N'S:\MSsql.1\MSsql\Backup\DBName.bak' WITH REPLACE,MOVE N'LogicalLogFileName'  TO N'L:\Path\Log\PhysicalLogFileName.ldf',MOVE N'LogicalDataFileName' TO N'X:\Path\Data\PhysicalDataFileName.mdf'

您可以使用以下逻辑将所有操作作为一个sql代理作业完成:

Backup database DBName to DBName with description = 'DBName Backup',init

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [DB_Name]
FROM DISK =N'S:\MSsql.1\MSsql\Backup\DBName.bak' WITH REPLACE,MOVE N'LogicalDataFileName' TO N'X:\Path\Data\PhysicalDataFileName.mdf'

一个没有备份设备的sql代理作业:

DECLARE @BackupFileName varchar(256)
SET @BackupFileName = 'S:\MSsql.1\MSsql\Backup\DBName.bak'
BACKUP DATABASE [DBName] TO DISK=@BackupFileName WITH skip,init
GO

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [DB_Name]
FROM DISK =N'S:\MSsql.1\MSsql\Backup\DBName.bak' WITH REPLACE,MOVE N'LogicalDataFileName' TO N'X:\Path\Data\PhysicalDataFileName.mdf'

笔记

>这假设您有权在sql Server实例上完成数据库备份(否则请使用DBA).>这假设您正在创建一个新的完整备份文件但是如果您可以使用现有的备份文件,那么我建议使用完整的DB生成备份文件来完成数据库刷新到开发,并将其计时以确保不重叠等.(如果需要,请与DBA联系)>我建议您与数据库管理员联系,如果您还没有谈论这个,或者您不是DBA.通过授予您相应的访问权限,DBA可以授予您访问权限以创建您拥有的sql代理作业的权限.

原文链接:https://www.f2er.com/windows/369126.html

猜你在找的Windows相关文章