Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
derwolf
Starting Member
3 Posts |
 Posted - 2011-07-11 : 03:53:19
|
| Hello Everyone!This is my issue:I need to build a script using bcp to backup & restore a whole database into single, table-named files.(This is necessary due to the MS SQL-Server does not automatically grant Admin-rights after restoring a Database - and with my limited rights on the DB server i'd lock me out of my own db after a restoring procedure)The first attempt of the backup code works so far:bcp "[DB_21014a].[db_owner].[t_BlockingStatusHistory]" out "C:\t_BlockingStatusHistory.dat" -S "localhost" -n -U "user" -P"pw"Now i'm looking for an "update" procedure to restore the downloaded table into the database using bcp again by first disable the triggers and constraints, trunkate the old table data, restore the backed up data from the local file and enabling the triggers and constraints again... - and have no idea so far...Can someone assist me? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-11 : 04:38:22
|
| Errr let's rather focus on your comment that SQL does not grant admin rights...If the login is on the server with the same SID as the user in the database, the user has the same privileges after the restore that they had on the DB when it was backed up--Gail ShawSQL Server MVP |
 |
|
|
derwolf
Starting Member
3 Posts |
Posted - 2011-07-11 : 04:43:59
|
| Actually i have got db admin rights, but no server admin rights (cause the server also hosts other db's).This issue seems only to occur using ms sql server (2005/2008) - had very long discussions with the db-admin team about that.The result was, that there are only 2 ways to restore a db: using bcp or using the inter-company ticket system to make the db-server admins restore it - which would take way too long... So now i check the bcp-way :P |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-11 : 05:30:14
|
| So what's the detail of the problem with the restore?bcp is not a backup/restore. It's tables only, you're not getting any of the structures, any of the procs, functions, etc, you're not getting permissions, etc, etc.--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-07-11 : 06:21:36
|
As far as your other question is concerned you can use something like this to disable propertiesTo Disable constraints for a particular table EXEC sp_msforeachtable @command1="ALTER TABLE YourTable NOCHECK CONSTRAINT ALL"; To Disable triggersEXEC sp_msforeachtable @command1 = "ALTER TABLE YourTable DISABLE trigger ALL"; To enable it replace NOCHECK with CHECK and DISABLE with ENABLE .PBUH |
|
|
|
derwolf
Starting Member
3 Posts |
Posted - 2011-07-11 : 08:13:27
|
| Thanks... testing... |
|
|
|
|
|
|
|
|
Backup/ Restore procedure using BCP