Copying Production database(s) to Development server 2

[bburnell]

Hi all,

I have a database called "Membership" on our SQL Server. I want to import/export everything (tables, data, stored procedures, functions, etc.) from the Production server ("sqlserver") to the Development server ("sqlserver\dev"). The biggest problem I am having with DTS is that a lot of the tables have dependencies on other databases/tables ("Health", "FMS"), which are located on the same server(s). Any ideas? I'm going to SQL training next week since they want me to train to become the DBA, but I'd like to have a usuable development server without doing each table by hand manually and taking all of the dependencies off of them. We are using SQL Server 2005 on a Windows Server 2003 machine.

Thanks!
Brett

 

[gmmastros]

Wouldn't it be easier to backup the production database and restore it to the development server?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bburnell]

I've tried that to. I'll post the error that I get.
Ultimately, I would like it to be automated.

 

[bburnell]

The backup set holds a backup of a database other than the existing 'Membership' database. Right now, this production database is backed up every 30 minutes into a .trn file. I've also tried manually backing it up to a .bak file with the same results. Could it be the dependencies on the other databases?

Thanks!
~Brett

 

[bburnell]

Ok, when I manually backup the database to a separate .bak file, it works. When I try to restore it, I get an error: "The media set has 2 media families but only 1 are provided. All members must be provided."

~Brett

 

[bburnell]

Ok. I got the problem with the backup/restore fixed (thanks to this link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=259647&SiteID=1).

It's been restored successfully. How do I turn this into a job that runs by itself every Monday morning at 3:00 am?

Thanks for all the help!
Brett

 

[mrdenny]

You'll need to simply setup a JOB on each SQL instance. The job on the production instance will backup the database, and the job on the dev instance will restore the database. Make sure to leave enough time between the jobs starting that the backup job completes before the restore job starts.

If you want to you can have the backup job start the restore job when it's finished.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bburnell]

Ok. That's what I did. I created a job on Production that essentially shrinks the database and then backs it up to a file. This runs on the production server at 2:00 am. At 4:00 am, I run a job on the developer server that creates the database (if it doesn't exist) and then restores the file created on the Production server. That works fine as-is.

If you want to you can have the backup job start the restore job when it's finished.

How do I start a job on the Development server from the Production server?

-or-

Is there a way to tell that the job on the Production server is finished from the job on the Development server?

Thanks for the answer and help!
~Brett

 

[mrdenny]

You can do either one. I prefer to start the job on the dev server from the production server.

Add a second step to the backup job with the type of operating system command. For the command use sqlcmd to log into the development server and use the sp_Start_job procedure to start the job on the dev system.

You don't need to create the database if it doesn't exist. You can restore a database over nothing and it will restore just fine.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bburnell]

Thanks a lot for the help. I'll try that.
~Brett