Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    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:

    Code:
    -- =============================================
    -- 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:

    Code:
    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:

    Code:
    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:

    Code:
    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,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You may need something like
    Code:
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •