May 13, 2011 at 8:41 am
Is there a way to migrate just the raw data to another database? I dont know the correct terms.
I accidently removed some public permissions on the Database A and is building another one SQL server that i want to migrate the data , Database B.
is there an easy way to do this? or is this even possible? I would restore from backup but the delete occured before my oldest backup.
so basically get my database back (permission wise) and just dump the data back to Database B.
thanks!
May 13, 2011 at 8:54 am
I'm a little confused. If you know what permissions are needed, can you not just add them to the existing database? You'll need to add them to the new database anyway.
In terms of moving data, you can use bcp to suck data out and push it back in, table by table. SSIS can be used with the import/export wizard as well, and might be easier, but it will still be table by table.
The copy database wizard might work, but I think that might move permissions.
Perhaps if you explain what happened, we can help you fix permissions instead of hassling with moving the data.
Note that you shouldn't be assigning permissions to the public role anyway. That is a practice frowned upon.
May 13, 2011 at 9:00 am
someone ran a script to "removed" all public permissions and they were trying to piece it back, its like almost an impossible mission. the script was something like delete * from sys. somthing like that...sorry i dont have it because i deleted it...worst script ever.
that is the issue, we do not know the original permissions. Just trying to get back to the default model. just want a raw data dump to the new database.
(sorry if english not good)
May 13, 2011 at 9:28 am
In that case, if someone mucked with system tables, you probably have the right approach. I might suggest the bcp method, which has worked well for me.
I would script out all objects, sans permissions, and recreate them in the new db. SQL Compare from Red Gate makes this year (other third party compares as well), or use "Generate Script in SSMS.
Then, here is bcp_out and bcp_in which will move all data. I'd put them in a loop to run through all tables
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/30028/
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/30027/
May 13, 2011 at 9:52 am
Thanks Steve, i will try that out.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply