windows – 每晚备份数据库

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.1MSSQLBackupDBName.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.1MSSQLBackupDBName.bak' WITH REPLACE,MOVE N'LogicalLogFileName'  TO N'L:PathLogPhysicalLogFileName.ldf',MOVE N'LogicalDataFileName' TO N'X:PathDataPhysicalDataFileName.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.1MSSQLBackupDBName.bak' WITH REPLACE,MOVE N'LogicalDataFileName' TO N'X:PathDataPhysicalDataFileName.mdf'

一个没有备份设备的SQL代理作业：

DECLARE @BackupFileName varchar(256)
SET @BackupFileName = 'S:MSSQL.1MSSQLBackupDBName.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.1MSSQLBackupDBName.bak' WITH REPLACE,MOVE N'LogicalDataFileName' TO N'X:PathDataPhysicalDataFileName.mdf'

笔记

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