creating a nightly replicate of a DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

creating a nightly replicate of a DB

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-09-03 : 18:52:12

I have a SQL 2008r2 server with two tables ProdTable and DevTable, how can I do a nightly copy of all objects and data from ProdTable to DevTable (over writing all exsiting data in DevTable).

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 19:25:44

do you mean database rather than table? otherwise i didnt understand what i meant by

nightly copy of all objects and data from ProdTable to DevTable

if its dbs then best thing is to backup proddb and restore it as devdb each day.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-09-03 : 19:39:18

It is a full DB, ProdDB and DevDB. on the same server. I know I could do a DB backup and restor but I want it to happen automaticly each night and I need to make sure it is an exact duplicate, tables procs views keys constraints and data.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 19:47:55

quote:
Originally posted by Eagle_f90

It is a full DB, ProdDB and DevDB. on the same server. I know I could do a DB backup and restor but I want it to happen automaticly each night and I need to make sure it is an exact duplicate, tables procs views keys constraints and data.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

add backup restore as a sql server agent job in the server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-09-03 : 19:56:04

i need more details then that, the agent requires SQL scripts to be ran it will not let me just say "backup ProdDB, restore DevDB"

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 20:54:08

quote:
Originally posted by Eagle_f90

i need more details then that, the agent requires SQL scripts to be ran it will not let me just say "backup ProdDB, restore DevDB"

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

yep
you need to use backup restore t-sql scripts

http://msdn.microsoft.com/en-us/library/ms186865(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms186858(v=sql.100).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-09-03 : 21:26:47

I am trying to make this work but I can not get it to. I have a job with two steps one to back and one to restore but all it tells me is "The job failed" (I have it loging fails to the event log and that is all it says no error number or anything.

BACKUP DATABASE [FFInfo] TO  DISK = N'D:\FFInfo-Full-Back' WITH NOFORMAT, NOINIT,  NAME = N'FFInfo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [FFInfoDev] FROM  DISK = N'D:\FFInfo-Full-Back' WITH  FILE = 3,  MOVE N'FFInfoDev' TO N'D:\Data\FFInfoDev.mdf',  MOVE N'FFInfoDev_log' TO N'D:\Logs\FFInfoDev.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 21:34:59

does the paths exist

first try running the statements in ssms query windows and see if it works. then put them in job steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-09-03 : 21:36:22

The paths are vaild. I had SSMS generate them from the back/restore option and then tested them in a query window and it worked for the first time but as soon as I added them to the agent it failed.

Edit:
Just tried them again from SSMS and they run fine, it is just in SQL Agent they are failing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 23:30:58

hmm..then it might be something to do with permissions of account used for running the job. Does it have access to the paths?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-04 : 01:32:41

Also, use the WITH COPY option on the BACKUP - to avoid breaking the log chain

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-09-04 : 22:28:30

I do not see where the permissions problem is at but I have no idea what I am looking at here. The account (we will call it Admin) I use to do everything in and has admin rights is set as the Owner, and the step that runs it is set to Run as User Admin (under Advance). The only real thing that looks wrong is for the Step that does the back on the general open the run as drop down is blank not sure if there needs to be something there. Any chance you can give me a few screens of what the setup should look like?

I also was able to dig this error up from the SQL log

quote:
Date 9/4/2012 10:40:18 PM
Log SQL Server (Current - 9/4/2012 10:40:00 PM)

Source Backup

Message
Error: 3041, Severity: 16, State: 1.

I have no idea where to get more details of the error though. MS says to look at the backup log but I can't figure out where that is at. Please help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources