Thread: Backup Database using Files
08-10-04, 16:32 #1Registered User
- Join Date
- Dec 2002
Unanswered: Backup Database using Files
I am but a lowly DBA unworthy of this task...
I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.
My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.
While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:
-- ============================================= -- Create database on mulitple file groups -- ============================================= IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'MultiFile') DROP DATABASE MultiFile GO CREATE DATABASE MultiFile ON PRIMARY ( NAME = MultiFile, FILENAME = N'e:\MSSQL\Data\MultiFile.mdf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%), ( NAME = MultiFile2, FILENAME = N'e:\MSSQL\Data\MultiFile2.ndf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%), ( NAME = MultiFile3, FILENAME = N'e:\MSSQL\Data\MultiFile3.ndf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%), ( NAME = MultiFile4, FILENAME = N'e:\MSSQL\Data\MultiFile4.ndf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%), ( NAME = MultiFile5, FILENAME = N'e:\MSSQL\Data\MultiFile5.ndf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON ( NAME = MultiFile_Log, FILENAME = N'e:\MSSQL\Data\MultiFile_Log.ldf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%) GO
BACKUP DATABASE MultiFile FILE = 'MultiFile', FILE = 'MultiFile2' TO Backup01 WITH INIT BACKUP DATABASE MultiFile FILE = 'MultiFile3', FILE = 'MultiFile4' TO Backup02 WITH INIT BACKUP DATABASE MultiFile FILE = 'MultiFile5' TO Backup03 WITH INIT
RESTORE DATABASE MultiFile2 FILE = 'MultiFile', FILE = 'MultiFile2', FILE = 'MultiFile3', FILE = 'MultiFile4', FILE = 'MultiFile5' FROM Backup01, Backup02, Backup03 WITH MOVE 'MultiFile' TO 'E:\MSSQL\Data\aMultfile.mdf', MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile2.mdf', MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile3.mdf', MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile4.mdf', MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile5.mdf', MOVE 'MultiFile_log' TO 'E:\MSSQL\aMultFile_Log.ldf'
Server: Msg 3259, Level 16, State 1, Line 1 The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I am trying this because my objectives are to:
1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window.
2. The database must be up and operational 7x24x365 (except for one 4 hour window each month)
3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.
I appreciate any thoughts and or guidance you can provide.
hmscottHave you hugged your backup today?
08-10-04, 17:14 #2Registered User
Provided Answers: 13
- Join Date
- Jan 2003
You may need something like
RESTORE DATABASE MultiFile2 'MultiFile', 'MultiFile2', 'MultiFile3', 'MultiFile4', 'MultiFile5' FROM Backup01, Backup02, Backup03 WITH MOVE 'MultiFile' TO 'E:\MSSQL\Data\aMultfile.mdf', MOVE 'MultiFile2' ....
08-10-04, 17:15 #3Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
You might consider a backup directly to tape... You can do a backup while the server is running with negligable impact, and it would allow you to relatively quickly and easily get a backup (or two) made and SENT OFF SITE before you have a cornary! You'll eat a couple of tapes, but that doesn't even rank as a HK at this point in time!
I don't know of any good way to backup part of a filegroup. It just isn't a good plan in my experience.
You could also BCP the tables out to flat files, and back those up. The down side to this approach is that there isn't any synchronization, so you'll never get a full backup made that you can really truly trust.