Post reply

How do DTS's, Views, Stored Procedures get backed up?

  • November 20, 2007 at 5:07 pm

    #184002

    I've been reading a little about backing up a SQL server 2000 database using Enterprise Manager by right-clicking on a database and then using those dialog boxes to effect a backup of the selected database.

    But I'm wondering how the other database items such as Views, Stored Procedures, and DTS's can be backed up.

  • November 20, 2007 at 5:53 pm

    #752919

    Hi Meridth,

    Views and stored procedures exist in the database. So if you have a database called northwind with 35 tables 6 views and 10 stoted procs when you envoke a 'native' backup of northwind (full database backup)through entrprise manager all these objects are backed up and stored in the backup file.

    DTS packages can exist in a couple of locations - either a storage file or the MSDB database if your DTS packages are stored in the MSDB database you will need to backup the MSDB database to backup the packages.

    Hope this helps and I hope it makes sense.

    Gethyn Elliswww.gethynellis.com

  • November 20, 2007 at 6:10 pm

    #752924

    Thanks Ellis,

    I realized after posting that the Views and Stored Procedures reside in each database.

    When I view the graphical view of SQL Server in Enterprise manager, all of the databases appear under the "Databases" folder, then there's another folder called "Data Transformation Services," and our DTS's are found under a subfolder there called "local packages."

    Do you think a full backup of the msdb database would save these DTS's?

    The way they appear it is not obvious they would get saved with the msbd database backup.

  • November 21, 2007 at 1:58 am

    #752978

    Hi Meridith,

    The DTS packages in the 'Local Package' folder will be stored in the MSDB database. Several tables in the MSDB database hold information relating to the DTS package but if you query the the sysdtspackages table in the MSDB database

    SELECT * FROM msdb.dbo.sysdtspackages

    this will return an enrty for every DTS package and version on that server . If you find your DTS packages in this table a full backup of the MSDB database will backup your DTS packages.

    Hope this Helps?

    Gethyn Elliswww.gethynellis.com

  • November 21, 2007 at 9:18 am

    #753154

    Yes, that helps!

    Many thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply