May 27, 2008 at 1:11 pm
I have an active database that has a large number of permission groups. I want to create the database on a different computer without the permission groups. Is there a way of doing this when backing up the schema? If not, how do I backup the permission groups so that I don't have to recreate them.
May 27, 2008 at 2:33 pm
So, you want to create a copy of the database without the users? I can think of two ways to do it.
1. Backup the database and restore it in the destination instance. Then delete the users.
2. Use the Transfer SQL Server Objects task in SSIS to copy the objects but not the users.
Greg
May 27, 2008 at 2:51 pm
Thank you Greg. I don't want to back up the data. will this just allow me to create a copy of the database without any data but all the tables etc?
May 27, 2008 at 3:00 pm
you can script out the objects (tables, views, etc.) and then run this on the new server. I think permissions is an option when scripting.
May 28, 2008 at 12:23 pm
Steve's got your third option. If you use either SSIS or scripting, you'll have to create the database first on the destination computer.
Greg
May 28, 2008 at 4:04 pm
And now a plug from our sponsors (I am not affiliated - I just use their products). 😀
1) Create a new blank database
2) Using SQL Compare by Redgate, perform a comparison between your existing database and the new blank database
3) Uncheck the objects that you don't want to synchronize
4) Run the synchronization wizard
This will create a new database with all objects (tables, views, schemas, stored procedures, functions, etc...) that you selected, without any data.
Now, if you want to include data for selected tables - run the SQL Data Compare tool and select only those tables. Run the synchronization wizard and you now have all of the data selected that you want in this new copy of your database.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply