Back up a database nightly

Question: Is it possible to automate the process of coping a full database every night into the development database on the same server?

Software: Windows Server, SQL, SQL Server Management Studio, ColdFusion

Background: 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

Is there some sort of a scheduled task or stored processed that can handle this sort of request?

Solution 1:

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

Below are the outlined steps that should work just fine, you'd obviously need to plug in the variables for you environment though. All this can also be done with SQLCMD if needed but assuming you have SQL Server and SSMS, I'm going to assume you also have SQL Agent jobs on this instance, and that you're somewhat familiar with it.

Create a Backup Device in SQL Server via SSMS:

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

Create a SQL Agent Job via SSMS and schedule that to run the backup at the designated time:

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

Create a SQL Agent Job via SSMS and schedule that to restore the full backed up DB designated time:

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'

You could do it all as one SQL Agent job with the below logic as well:

Backup database DBName to DBName with description = 'DBName Backup', skip, 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'LogicalLogFileName'  TO N'L:\Path\Log\PhysicalLogFileName.ldf',
MOVE N'LogicalDataFileName' TO N'X:\Path\Data\PhysicalDataFileName.mdf'

One SQL Agent Job With No Backup Device Needed:

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'LogicalLogFileName'  TO N'L:\Path\Log\PhysicalLogFileName.ldf',
MOVE N'LogicalDataFileName' TO N'X:\Path\Data\PhysicalDataFileName.mdf'

NOTES

1. This assumes you have permissions to complete a database backup onm the SQL Server instance (get with your DBA otherwise).
2. This assumes you are creating a new full backup file but if you can use an existing one, then I'd suggesting using the full DB production backup file to complete the DB refresh to dev and just time it out to ensure no overlap, etc. (get with DBA if needed)
3. I'd suggest getting with your database administrator regardless to talk about this if you've not already or if you're not also the DBA. The DBA can grant you access to create SQL Agent jobs that you own by granting you access to the msdb SQL Agent roles accordingly.