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!!
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!!
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you restoring on the same server or another one?
ASKER
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.
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.
Thats great.
ASKER
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).
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).
Where you need only specific tables use the SSIS Import Export wizard.