Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Faster way to move all the data from one database to another ( without backups)? Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 139, Visits: 542
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
Post #1539272
Posted Friday, February 7, 2014 10:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1539298
Posted Friday, February 7, 2014 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 139, Visits: 542
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
Post #1539305
Posted Friday, February 7, 2014 11:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1539311
Posted Friday, February 7, 2014 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 139, Visits: 542
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.
Post #1539321
Posted Friday, February 7, 2014 11:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
And if you try to drop the filegroup? An error that it's not empty?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1539334
Posted Friday, February 7, 2014 11:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 139, Visits: 542
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/

Post #1539337
Posted Friday, February 7, 2014 12:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
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 2008, MVP
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

Post #1539339
Posted Friday, February 7, 2014 12:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:39 PM
Points: 139, Visits: 542
the state is 7: Defunct
Post #1539340
Posted Friday, February 7, 2014 1:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
ALTER DATABASE <database name>
MODIFY FILE (Name = <file name>, OFFLINE);




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1539362
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse