File Backup

Question

Post reply

File Backup

matt101

Default port

Points: 1456
More actions
August 7, 2003 at 9:06 am

#106432

When you do a file backup, does it backup any stored procedures and/or defaults you have set? I tried looking through the doucmenation on it and didn't see where it said it did or did not.
Matt

Viewing 8 posts - 1 through 7 (of 7 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

File backup just backs up the physical database files (MDF).

matt101 Default port Points: 1456 More actions · Answer 2

Hmm I see, so I will need to backup stored procedures as text files to back them up along with the data.

Matt

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

I don't think so. Stored procs are stored on the physical mdf files of the database. If you are doing file backups, you are capturing the database schema also because that is stored in the primary file group.

Thanks Tom

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Full backup will backup everything for you. It is good practice to script all SPs too.

matt101 Default port Points: 1456 More actions · Answer 5

By if i do a full backup to Disk would that get everything? I am assuming it will since when I backup to tape it does since I had to restore a database a couple times for testing purposes.

I am just trying to streamline my backup since SQL Server and the IIS and my aspx/html/asp pages all reside on the same server.

Matt

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 6

Does that mean it is good practice to script all views, udf's,diagrams,table_schema.

The whole point of database backup's is recovery of the database. That doesn't mean, the database less the stored procs, because you should script those out seperately.

I would not throw an extra step to script out the stored procs. However, if I was doing file backups I would put only system data in the primary filegroup and make sure that that filegroup was backed up on a nightly basis, and create a user_data and user_indexes filegroup to distinguish thoses from primary.

Just my opinion.

Thanks Tom

growl Hall of Fame Points: 3068 More actions · Answer 7

Howdy Matt. If you are wanting to achieve smaller backup files of your databases then I would suggest litespeed. It's an excellent solution to reducing file sizes.