Creating a Test Server From a Live Server - Auto Refresh -- sql server new users

MBSNewbie
5/3/2006 11:16:32 AM

Hi All,
I'm hoping someone may be able to help me out.
We are running multiple databases on our SQL Server. I would like to create
a test environment based on the existing databases. (Because I'm new, I
don't want to mess up what is there) I have been trying for a week to copy,
backup & restore, even replicate, but I cannot get an exact match to the
existing server. Again, I'm mostly doing this to try and learn a bit more
about SQL. I have taken a class, but you know, you don't really learn til
you get your hands dirty.
I am having trouble figuring out how to keep the permissions and stored
procedures....

I'd love any help as my company has limited funds and I can't afford any
more formal training.
Thanks!

Keith Kratochvil
5/3/2006 3:52:19 PM

step #1 move the users

HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133

step #2
Move the databases

BACKUP and RESTORE is probably your best bet

It will look something like this
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT

The restore (on the other box) will look something like this:
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
WITH MOVE 'foo_dat' TO 'x:\foo.mdf',
MOVE 'foo_log' TO 'x:\foo.ldf'

--
Keith Kratochvil

[quoted text, click to view]

sql server new users : Creating a Test Server From a Live Server - Auto Refresh