February 9, 2016 at 11:20 am
I need to Copy/refresh the "data only" to Test from prod without making changes to users/permissions. Please advice on the best approach to follow through with carrying out this ask
February 9, 2016 at 1:54 pm
If the amount of data is not overly big, I would recommend looking into RedGate's SQL Data Compare utility.
February 9, 2016 at 2:24 pm
Thank you Hugo. The thing is I won't be allowed to download and use unlicensed software and we don't have a copy of that app. The database is approximately 4gb... I had just created a "transfer sql server objects task" package using ssis, but I discovered there isn't any Integration services installed on the prod side.....and on the test side where the data is supposed to go to, we have integration services installed. I imported the ssis package into msdb(cos i couldn't find file system) and created a job to run it from there and it failed "access denied".
I tried to right-click on the package itself and run it from msdb and it failed "nothing to copy"... I setup the connections properly in visual studio while creating the ssis package so im really confused now.
are there any sequence of steps that i could follow, i mean rather than using any third party tools?
Export wizard? what do you think?
February 9, 2016 at 2:33 pm
How many actual users exist, and how are the permissions configured?
It seems as if generating a script to remove or disable users that exist in production, and the same for the users an permissions in dev, would be far easier than going through all of the work required to use the import wizard, an ssis package, or a third party tool.
Once those scripts are created, you restore a backup of production to development and run the scripts.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 9, 2016 at 3:20 pm
Michael L John (2/9/2016)
How many actual users exist, and how are the permissions configured?It seems as if generating a script to remove or disable users that exist in production, and the same for the users an permissions in dev, would be far easier than going through all of the work required to use the import wizard, an ssis package, or a third party tool.
Once those scripts are created, you restore a backup of production to development and run the scripts.
I'm with Michael on this one. Another approach to the permissions issue is to write a script for all the user permissions in dev and save it. Restore a backup from production into dev. Then drop all the database users from dev (inherited from the production restore) and run your script to create users and permissions like they were before.
One caveat: Unless you have sysadmin privs, don't forget to leave your database user intact so you can run your script to apply the permissions. If you have sysadmin privs, you don't need to worry about it.
February 10, 2016 at 5:27 am
sounds awesome Mike and Ed. I'll try that today... let you guys know how that went. Have a great day at work today
February 10, 2016 at 5:32 am
michael_okonji (2/10/2016)
sounds awesome Mike and Ed. I'll try that today... let you guys know how that went. Have a great day at work today
You too. Glad to help.
Don't forget to write the CREATE USER statements along with the permissions.
February 10, 2016 at 10:34 am
Ok guys. So.. here is what has happened so far... I decided to try something.. so at test(destination) I used the "generate script wizard"... generated a "drop and create" statement so I could drop all constraints.. and then I could import from prod using the "impoprt/export wizard" and after that created the constraints and that has worked so far for 3DBs
but for one particular db, and its the last, I kept getting errors, and so I did the import in batches to reduce the workload, all succeded but this one table.. and when I try to import just that one table I get
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - attachments_blob returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Memory allocation failure".
(SQL Server Import and Export Wizard)
maybe my google-fu is weak, but I haven't been able to narrow down an answer for this one.
February 10, 2016 at 10:40 am
It looks like there wasn't enough memory to move the data. What kind of columns are in the table? Are there blobs or other large objects?
-SQLBill
February 10, 2016 at 10:55 am
Hey SQLBill I think you're so right because...I've actually been trying to do this using ssms on my local machine to remotely get the job done... your comment suggested to me that I might wanna log on to the server itself which I did and it completed successfully. Phew! Thanks guys the job is done.
February 10, 2016 at 2:16 pm
Instead of SSIS, google this:
sp_help_revlogin
The output from that will produce output to create the users.
This code will generate code to create the users and add them to the proper server roles:
SELECT 'EXEC sp_addsrvrolemember @rolename = ' + QUOTENAME(usr1.name, '') + ', @loginame = ' + QUOTENAME(usr2.name, '')
FROM sys.server_principals usr1
INNER JOIN sys.server_role_members rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals usr2 ON rm.member_principal_id = usr2.principal_id
As for the permissions inside each database, go to Books Online and look up sp_helprolemember. That can help you create a set of scripts to get you started.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply