Faster way to move all the data from one database to another ( without backups)?

  • Hello All

    I have what I suppose is not a very common situation

    A large database near 1TB, 6 filegroups, one defunct/offline filegroup and corruption in the main file,

    so far everything is working "fine" but i know that I have a potential disaster is I do not fix that database, since a check of the database integrity shows several errors, I want to recover that database and at the same time be able to remove the offline filegroup, those who have seen it will know that MS was kind enough as to leave you without any option when a FG is like that, it will remain forever wasting space

    So I am trying to move all the data from that database into a new clean one, I am thinking about using replication or an SSIS component to move the data, anyone have better ideas?

    the machine I have for that is medium size 16 cores, 96gb, MS Sql server 2008 r2

    Thanks in advance

  • Can you not just drop the filegroup? It should work, though may need a couple other steps first.

    What's the corruption-related errors? Might be an option that doesn't involve recreating the entire DB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I would love to drop that filegroup but on the current state everything I have tried returns an errors saying that it is not possible because the filegroup is offline, if you know a way to deal with that I will really appreciate it

    for the corruption I have another couple options, but since I also want to partition that database and fix a few other things it is a nice option to have a "clean" to create everything in there

  • ricardo_chicas (2/7/2014)


    I would love to drop that filegroup but on the current state everything I have tried returns an errors saying that it is not possible because the filegroup is offline, if you know a way to deal with that I will really appreciate it

    I might, with more information including the exact status of the filegroup and files and the exact error messages

    for the corruption I have another couple options, but since I also want to partition that database and fix a few other things it is a nice option to have a "clean" to create everything in there

    Personally I wouldn't enjoy spending a weekend recreating a TB DB unless absolutely necessary, but if that's the chosen path... I'd probably use BCP. BCP out the tables to disk, drop the DB, recreate the structures and BCP in. Makes it nice and modular so if one thing fails that one thing can be rerun

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This one I try to use it as something to learn how to move large amounts of data, so I do not mind spending some time doing it, will try the bcp path, thanks

    for the filegroup it had a separate file, was set offline, it is possible to remove the filegroup but not the physical file, when I try to do that I get this:

    Msg 5056, Level 16, State 2, Line 1

    Cannot add, remove, or modify a file in filegroup 'FG_lost' because the filegroup is not online.

  • And if you try to drop the filegroup? An error that it's not empty?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • exactly, that is the error

    I am reading about a way to do it changing the system tables ( something that I will try in a safe test environment )

    http://www.sqlnotes.info/2013/05/09/bring-offline-file-online-modify-sql-server-metadata/

  • No, no, no, no, don't do that.

    What's the exact state_desc of the file? (sys.database_files)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • the state is 7: Defunct

  • ALTER DATABASE <database name>

    MODIFY FILE (Name = <file name>, OFFLINE);

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • that got me this message:

    Msg 5057, Level 16, State 3, Line 1

    Cannot add, remove, or modify file 'offline_data' because it is offline.

  • Hmmmm....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 12 posts - 1 through 11 (of 11 total)

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