[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

How to duplicate tables and/or db's, including all permissions, users, etc.?

Hi Friends :-)

I'm a Flash/Flex developer, crossing over into .NET web apps.
I can't stop the SQL server, and prefer not to take anything off-line.
I have admin on IIS, and sa on SQL.
There are about 50 dbs on this server, so I want to minimize impact.
I just want tables and sometimes entire db's copied with new names, but with all the same data and permissions, etc., so that I can do testing with web code, and not worry about trashing or changing anything critical.

Any solid references with lots of detail are especially appreciated.  :-)

thanks!!
0
ksuchy
Asked:
ksuchy
  • 4
  • 3
1 Solution
 
sbagireddiCommented:
Where you need entire databases the fastest way is to backup/restore.
Where you need only specific tables use the SSIS Import Export wizard.
0
 
ksuchyAuthor Commented:
Hi sbagireddi,

Thank you for this info.

However, isn't it a potential problem that the backup/restored db will have the same name when restored, right?  
Again, I'm doing this all live, and so I don't want the WORKING copies of tables and db's to be taken offline or disabled at any point in time, if possible.
I want my TESTING/DEVELOPMENT copies of tables and db's to have different names, than the ones currently used.

Kindest Regards,
K
0
 
sbagireddiCommented:
Since this is a production server and considering the enormity of the task, I would suggest you do it during  off-peak
hours.
Also database can be restored with a new name using the syntax below:

http://msdn.microsoft.com/en-us/library/aa238405(SQL.80).aspx

Look for "E. Make a copy of a database using BACKUP and RESTORE  ".

For tables you can change the name once the database has been restored using this syntax:
http://doc.ddart.net/mssql/sql70/sp_ra-rz_11.htm

But again please be forewarned that this is a prod env. I would do all this in a test environment and only if everything goes OK, do it in prod.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sbagireddiCommented:
Are you restoring on the same server or another one?
0
 
ksuchyAuthor Commented:
Same server.
I do have a test environ, with a very close replica machine.
I'll try it there first, then let you know how it goes.
0
 
sbagireddiCommented:
Thats great.
0
 
ksuchyAuthor Commented:
http://www.databasejournal.com/features/mssql/article.php/3594201

First I ran a backup of the live operational db
Then I created a blank new db (with the suffice DEV on the name of the db)
When I restored, I used my new name, and checked the box in the second tab to force overwrite all stuff
I renamed tables I wanted to keep handy.
then I ran DTSWizard.exe to copy individual tables over from the live db to the backup copy.

took a few mistakes to realize that you have to close/open, or otherwise refresh Enterprise Manager, or the new tables don't show up.  Attempting to create a blank table from within the same DEV db, and then copy over data from good table (renamed) to the new blank table wouldn't work.  Also, you can't duplicate a table with source/destination both in the same db, because you can't get at the SQL or get the table copy to have a new name (it will just append or rewrite the same data within the same table).
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now