Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ricardo_chicas
ricardo_chicas
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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


ricardo_chicas
ricardo_chicas
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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


ricardo_chicas
ricardo_chicas
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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


ricardo_chicas
ricardo_chicas
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
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/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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


ricardo_chicas
ricardo_chicas
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 676
the state is 7: Defunct
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search