December 29, 2005 at 1:48 pm
we are planning to install SP 4 for SQL Server 2000 and want to back up all the databases and all the DTS and Jobs. What is the easiest way to do all these? do we have to backup the databases one by one?
also, what system database file holds all the DTS/Jobs? in another words, to back up DTS and JOBS, what should we do???
Thank you very much for your help.
Disclaimer: We no longer have a DBA so here come all these questions.
December 29, 2005 at 1:57 pm
The msdb database holds the DTS packages and the information on the SQL Server agent jobs.
K. Brian Kelley
@kbriankelley
December 29, 2005 at 2:04 pm
You can script out the backups, but yes each database is backed up separately. You may not need to run a full backup if you have a recent full backup and all the transaction log backups since, but if you are unsure of yourself, it's best to be safe and make the full backups (assuming the databases aren't so large that you don't have time...)
Jobs and normally DTS packages are in the msdb database. I qualify DTS packages because they can be saved as files and stored somewhere in the file system, but most of the time they tend to be in msdb. So make sure you have a good backup of master (logins, databases, and linked servers) and msdb (jobs, schedules, DTS packages, and history).
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply