Backup databse files and filegroups

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Backup databse files and filegroups

Backup databse files and filegroups

Rate Topic

Display Mode

Topic Options

Author

Message

MTY-1082557

Posted Thursday, November 05, 2009 3:13 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:34 AM
Points: 160, Visits: 341

what is the best practice for backup/restore database files and filegroup files ?

Regards,

Post #814596

MTY-1082557

Posted Monday, November 09, 2009 11:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:34 AM
Points: 160, Visits: 341

any advice ?

i have a database in a Simple mode, full backup every sunday and differencial every night. I added 12 filegroups/files ..

What is the best way to backup/restore this case?

Regards,

Post #815982

Bru Medishetty

Posted Monday, November 09, 2009 11:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, March 18, 2010 9:03 AM
Points: 1,427, Visits: 1,506

I hope you know that you cant recover the Database to a Point In Time with Simple Recovery and if that's what you are intending then the Backup Schedule is ok. But a point to mention is what is the size of the Database, without knowing which it is not appropriate to say if it is a good schedule you have in place, example if you have a DB which is 20 Gigs then the above Backup schedule will be point less (unless you have no space for storing 7 Full Backups).

And finally what is the purpose that you have added 12 File /Filegroups?
can you explain?

Bru Medishetty

To get quick answers read the article
How to post data/code on a forum to get the best help

My Blog - learnsqlwithbru.com

Post #815997

MTY-1082557

Posted Monday, November 09, 2009 11:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:34 AM
Points: 160, Visits: 341

My DB is 330GB. We don’t need a full model in this case. We just insert data and then we create reports. The reason of the 12 filegroups is because I have a table with more than 200million records and the partitions are for month (each month each filegroup/file).

I’m wondering what is the best practice of backup for this scenario.

Thank you in advance

Post #816006

Bru Medishetty

Posted Monday, November 09, 2009 11:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, March 18, 2010 9:03 AM
Points: 1,427, Visits: 1,506

Since your DB is 330 Gb and I get the reason for the additional Filegroups, the backup plan you have is good to work with.

Just for more details, what do you mean you insert data and run reports, do you import data using SSIS or BCP or Stored Procs and the reporting application used the Data. Is that right?

Bru Medishetty

To get quick answers read the article
How to post data/code on a forum to get the best help

My Blog - learnsqlwithbru.com

Post #816010

MTY-1082557

Posted Monday, November 09, 2009 11:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:34 AM
Points: 160, Visits: 341

right now I used bcp.. The next step is to use SSIS instead bcp for the insertion of data.

conclusion: If i restore my full backup, the filesgroups will restored with as well right?

Post #816013

Bru Medishetty

Posted Monday, November 09, 2009 12:47 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, March 18, 2010 9:03 AM
Points: 1,427, Visits: 1,506

Yeah, Filegroups will all be back when you restore from a full backup.

Bru Medishetty

To get quick answers read the article
How to post data/code on a forum to get the best help

My Blog - learnsqlwithbru.com

Post #816068

« Prev Topic | Next Topic »

Permissions