SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Refreshing Test Data With Prod Overnightly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kowani1
Starting Member

42 Posts

Posted - 12/03/2006 :  20:57:45  Show Profile  Visit kowani1's Homepage  Click to see kowani1's MSN Messenger address  Reply with Quote
Hi All,

I need to refresh a copy of my test database with production data every night via a scheduled job, and need to know what the most efficient way to do it is - without impacting sql logins etc.

I did create a DTS package to copy the data and objects, however that returned an error and I seemed to also have lost all my views.

Please help!!

Jungle DBA

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/04/2006 :  00:09:41  Show Profile  Send jen a Yahoo! Message  Reply with Quote
if it's only data, just dts the data then

but why do you want to do this? is the data not confidential?

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/04/2006 :  07:34:56  Show Profile  Reply with Quote
I think the easiest way of doing this is BACKUP PRODUCTION and RESTORE to TEST

You will have to reinstate the logins, but that only requires a small script. (Do you actually need all the production Logins available on Test? I find it more common that the Test population is either a subset of production, or completely different!)

Kristen
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 12/05/2006 :  11:01:13  Show Profile  Reply with Quote
I have a different set of users on the test database , so when I refresh the test database from the production how can I restore the test users. Is there any script .

Thanks
Go to Top of Page

monty
Posting Yak Master

130 Posts

Posted - 12/05/2006 :  22:29:09  Show Profile  Reply with Quote
you dont need a script, as there wont be any orphaned users becoz test db has a diffrent set of users so master db is in sync as it was earlier so why need a script?:-)

its me monty
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 12/06/2006 :  01:11:14  Show Profile  Reply with Quote
But the user logins are becoming invalid. When I try to test the user login throught ODBC , it says the user doesnot exist. So I have to go to the sysuser , delete the user and add him again. The NT user is also dropped when I refresh the Db and have to add him again.

Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/06/2006 :  01:16:04  Show Profile  Reply with Quote
The users in the database will be lost if you restore a different database.

If you set up the users just how you want them you can script them with Enterprise Manager, and then re-run that script after you restore the database. (You only need the Database User stuff, not the Server Logins)

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000