Split backup into smaller files

Question

Split backup into smaller files

hvdtol-217342

SSC Enthusiast

Points: 189
More actions
April 15, 2006 at 8:37 am

#152911

In our organisation we manage databases with Sql Sever and Oracle.
In Oracle we use tablespaces, but not larger then 4 Gb. When a tablespace becomes full, we extended it with an extra datafile.
This way the backup files never become larger then 4Gb.
Why do we do this?
- we have had troubles in the past with very large datafiles; the backup program corrupted sometimes large datafile. Now it is our policy never to have datafiles grow over 4Gb.
- Also we think smaller datafiles are handeled better.
We want to adopt the same policy for Sql Server.
Two applications are the reason for this: MOM(Microsoft Operations Manager) and Sharepoint; both application can grow to very large databases, and very long backup times.
We want to do the following:
- Create datafiles for each database with a max of 4Gb per datafile
- Add more datafiles when needed to the same filegroup
- We will not define specific tables into a specific filegroup
- We will use one filegroup; perhaps two to sperate the system tables
- We want to make datafile backups and follow the same strategy as with Oracle: never make physical files grow over 4Gb.
Can anybody give me any advice.
Is this a workable situation or are will we face problems we might not want?

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

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

SQLSolutions360 Old Hand Points: 327 More actions · Answer 1

The Architecture for MS SQL and Oracle is completely different.

However, I have separated the database into multiple filegroups and multiple files ( more than 2 in any Filegroup ). But the reason for me to do this was crippled hardware in UAT. The maximum size of the Drive was 33GB, and the actual size of one of the .mdf file (data file) was 50GB. So to ease the REFRESH of a HARDWARE CRIPPLED Shitty UAT Box ( I later on kept 3 large tables on its own datafile on RAID 5 as these 3 tables became corrupt too often because of the heavy updates on a bit field ) I am on SP3a + Hotfix.

We want to do the following:

- Create datafiles for each database with a max of 4Gb per datafile This is pretty simple, all u will have to do is RESTRICT the size of the datafile in the DbOptions to 400,000 MB. So that when it becomes FULL, it will move onto another datafile.

- Add more datafiles when needed to the same filegroup Yes. It can be done at any time and does not require the database to be Offline etc during this operation.

- We will not define specific tables into a specific filegroup Yes. It can be done..... But please understand that we cannot backup individual data files.

- We will use one filegroup; perhaps two to sperate the system tables Now this is possible but i do not know if its a feasible solution. As it would require you to alter all the user tables and point them to the new file group that u intend to create. This would require an outage.....becoz MS SQL in the backgroud will have to copy the data inside this table to a temp table, then drop the table, create a new table on the filegroup you want and then load back the data............ As you know.... this would require an outage

- We want to make datafile backups and follow the same strategy as with Oracle: never make physical files grow over 4Gb.

This cannot be done ...... for sure...... The architecture is completely different.

Thanks

Kunal Gandre

Snr. SQL DBA

Kindest Regards,

Kunal Gandre
>
Snr. SQL DBA

hvdtol-217342 SSC Enthusiast Points: 189 More actions · Answer 2

hvdtol-217342

SSC Enthusiast

Points: 189

April 17, 2006 at 2:52 am

#632729

Thanks, I will think about it further

hvdtol

Jason Hall Hall of Fame Points: 3906 More actions · Answer 3

Also take a look at table partitioning in SQL Server 2005. In SQL 2005 you can partition a table into multiple filegroups giving you substantial performance and storage efficienty enhancements.

hvdtol-217342 SSC Enthusiast Points: 189 More actions · Answer 4

Jason,

This is a good idea when you can influence the application.

But we want to introduce datafiles, because of Sharepoint and MOM.

Both applications which can grow to a large db, and are 'standard' applications we do not want to break into.

For now i want to create datafiles in the primary filegroup. Maximize each datafile and use backup datafiles spread over several backup files.

Andrew Clarke-161005 Mr or Mrs. 500 Points: 587 More actions · Answer 5

I use Red Gate's SQL Server backup tool on a database of around 480 Gig. It allows you ro specify however many files you want to split the backup so it shouldn't be a problem.