SQL database backups - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

SQL database backups

Author

Topic

johns
Starting Member

24 Posts

Posted - 2006-10-02 : 07:05:05

Is there a good, reliable tool for weekly backing up complete databases from a production SQL Server to a local developer SQL Server?

Thanks,

John

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 09:42:08

Read about Backup and Restore in sql server help file. You can schedule it as Job to take backup every week

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 10:06:19

This has come up quite a few times here - I don't have links to hand, but a Google search should help

You could perhaps try Googling for something like:

site:sqlteam.com backup production restore development

For help on the basic syntax see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Backup,Restore

Kristen

johns
Starting Member

24 Posts

Posted - 2006-10-02 : 10:36:49

Hi Kristen,

I have been reading that section on SQLTeam looking for my solution. I am also looking through the book Professional SQL Server 2000 Programming. I am not afraid to read, study and learn.

I am probably using the wrong terminology to describe what I want to achieve. I never write new code procedures and test with production data. I want to be able to keep local developer databases in sync with the productiion SQL Server DBs. All the DBs have a nightly backups in place using a server backup and the DBs are set to 'simple'. Everytime I work on a site I now have delete my local data tables and then export the production database data tables to my local dev. machine. I would like to find a better way to automatically keep the 2 in sync.

If you can point me where to read, I would appreciate it.

Thanks,

John

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 12:17:13

Ah, OK, I think I see the "gap" in your knowledge now!

Basically you need to arrange a scheduled task to copy a FULL backup from the production machine to the Dev machine. You are already making the Full backup, so the Copy task just needs to be sure to happen after the backup task has finished (it is possible to make it a step in the backup process, so it would then always happen immediately after, and never half-way-though!)

Then you need a step on the Dev machine which restores the backup file into an appropriate database on the Dev server. This also needs a little bit of synchronisation - it can't start until the backup file has arrived, and it most definitely should not start until the backup file has finished copying!

I know my FAQ link didn't answer this question, but what I was wanting to alert you to is this same question "How do I copy my Production database to my Dev server on a regular schedule" has come up on SQL Team before, and if you can find it in Google you are likely to have some threads where the ins-and-outs have been discussed in more details - and the pitfalls solved more readily than my rusty old brain can now remember them!

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-02 : 12:49:19

nigel has some code that basically does this

have a look here http://www.nigelrivett.net/

specifically http://www.nigelrivett.net/SQLAdmin/s_TestRestore.html

-ec

vaddi
Posting Yak Master

145 Posts

Posted - 2006-10-02 : 16:30:18

Hello

When I am executing the code that is given in the above link, I am getting the error :

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 's_TestRestore'. The stored procedure will still be created.

What can I do for this.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-02 : 16:33:56

vaddi, did you create the s_TestRestore? If so, then you can ignore this warning (it's not an error).

Tara Kizer

johns
Starting Member

24 Posts

Posted - 2006-10-02 : 20:00:54

This thread [url]http://www.sqlteam.com/Forums/post.asp?method=ReplyQuote&REPLY_ID=116296&TOPIC_ID=38003&FORUM_ID=6[/url] gives me a good solution and works great. I use SFTP with WS_FTP Pro which allows me to auto download folders and files and specify how many times a week.

Now all I need is to automate the SQL backup process..

BACKUP DATABASE dbname
TO DISK = 'c:\download\dbname.bak'

Thanks,

John

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 01:58:30

I hate it that Snitz allows REPLY pages to be spidered ... here's a clean link to that thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38003

"Now all I need is to automate the SQL backup process"

The SQL Scheduler can do that for you. Not that you need to add

BACKUP DATABASE dbname
TO DISK = 'c:\download\dbname.bak'
WITH INIT

if you are going to reuse the same file to prevent backups being appended

Kristen

johns
Starting Member

24 Posts

Posted - 2006-10-03 : 06:04:38

quote:
I hate it that Snitz allows REPLY pages to be spidered ... here's a clean link to that thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38003 height="1" noshade id="quote">

I thought that was really strange.

Use 'WITH INIT' to prevent appending to the existing file. Got it!

Thank you!

John

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-10-03 : 09:29:24

I may be missing something major here but this could be done (and automated) with DTS as that can wait for one event to complete succesfully before starting another. Maybe not the best solution but certainly a quick one to setup

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources