November 9, 2010 at 1:55 pm
Hi All
I am running sql server 2005 and need to create a copy of our production database for a test environment on the same server
I am looking to find the best way to do this. I have read that some suggest backing up production and restoring (copy only) to an empty test db. I have also read that scripting out production and running on test.
Please include as many details as possible. I am kinda nervous because I do not want to mess up the server.
Thanks so much for your help
Craig
November 9, 2010 at 2:23 pm
Define "copy":
if that is the same objects without content, just script out your full db ( rightclick in your object browser hovering over the dbname, choose "all tasks" select generate sqlscript and off you go
Run that script in your dev db.
If you also need the data, restoring your prod db to a new name for the dev db is the best way.
Keep in mind with this setup, your test db may push out your prod db !!!
Take very good care of security !!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 9, 2010 at 3:27 pm
The only issue(s) I can come up with on the backup/restore method is
1. You will need to modify the file name on the new database otherwise SQL will tell you the database already exists.
2. Make sure you give limited access to only those users that must have access. It can be very easy for an every day user to select the wrong database, make massive entries or changes and then realize they were in the wrong db.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 17, 2010 at 11:43 am
I am having a problem when I run the script on the new database. I get several errors that I do not have permissions to create several tables. When I script the table out by itself, everything works fine.
November 18, 2010 at 12:02 am
- Are there any database references in your script ?
If so, did you modify those to point to your intended database ?
- what kind of script objects does the script have ?
- which authority set do you think you have when you're runing the script
If you want you can post the script (attachement please) and we'll have a look.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 1, 2010 at 7:11 am
I think this is being made to look more complicated than it is...
Create a blank test database - a suggestion would be to not call it production_nameTest as they will be easily mistakable.
Backup your production database,
Restore the TEST database with replace & Move.
If its the same server then Fix up the users
-- fix Users
set nocount on
declare @v_dbuser varchar(255)
declare @sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
open c1
fetch c1 into @v_dbuser
while (@@FETCH_STATUS <> -1)
BEGIN
print 'Fixing User ' + @v_dbuser
set @sql = 'sp_change_users_login ''auto_fix'',' + @v_dbuser
exec sp_executesql @sql
fetch c1 into @v_dbuser
END
CLOSE C1
DEALLOCATE C1
If you need help with code then post up your DB names and locations of where you want files to go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply