To backup all databases use a database maintenance plan to backup them up to the local server then have it run a script on completion to copy the files to the other server then when that job is finished have it run the restore of the databases.
Joshua Tipton -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 15, 2002 7:01 PM To: CF-Talk Subject: RE: OT SQL SERVER Backup > Is there an easy way to back up all the SQL server DB's and > restore them on a separate Machine? > > ... > > I have SQL server 2K > > and thiry + databases ... > > I know how to backup each one, but was wondering if there is > a bulk backup of the entire server. There are a couple of options that might work for you. If this is a one-time thing, and you can stop the source database server, you can simply copy the database and log files to the new server, and use sp_attach_db to attach each to the new server. Alternatively, you can use T-SQL to automate the backup and restore process; on the source database server, run the BACKUP DATABASE command through any database. OSQL is a handy command-line client for doing this sort of stuff. Since you want to backup the entire server, you'll probably want to write a T-SQL batch to query the master database to find out about each database, then loop over that information. I'll bet you can find a useful script for that here: http://www.swynk.com/sqlscripts/backuprest7.asp Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
