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

I have tried the following backup script:

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

And here is the restore script:

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'

However, running the Restore script generates the following error:

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'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?

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.

Regards,

hmscottYou 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' ...

Some cautions, though. You will need to keep all of your transaction logs, if you back up the files on separate nights. I have not tried to do a file by file restore, so I am not sure how easy it would be.|||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.

-PatP