CODEWITHSUNDEEP
sql-serversql-server-2008backupdatabase-backupssql-server-agent
Brandon
Brandon

Reputation: 10953

Best Way to Create Nightly Replication of Sql Server Database

I want to create a job that runs every night. I have a database (MyDatabase) that I want to copy/replace my staging database with (MyDatabase_Stage).

I presume the easiest way is to do something related to SQL Server Agent, but I have never done anything like this before. What is the best practice and easiest route to go to get this setup and tested?

I do not care if the data is 24 hours old and the most important criteria is that is does a full copy every night at the same time.

Upvotes: 1

Views: 1806

Answers (2)

Jimbo
Jimbo

Reputation: 2537

copy the .bak file to your staging server and restore from there using a script. Run the script on a schedule in an agent job.

The benefit of a script is that you can add functionality later - for instance you might not require audit tables and these can be truncated.

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32727

Check out snapshot replication. As part of the setup, it'll create a SQL Agent job to do the copy of the data and whatnot. You can then schedule that job at whatever time and frequency you like.

Upvotes: 1

Related Questions