?
Solved

NEWBIE: How to (fully) copy a database with TSQL?

Posted on 2006-04-05
8
Medium Priority
?
845 Views
Last Modified: 2008-02-01
Dear Experts,

I would like to write a TSQL script that would:

1. Copy an entire database as NewDB, retaining all data, keys and indexes.
2. Assign ownership of NewDB to an existing user (Not the user I'm signed on as).
3. As a bonus, copy it from one machine to another.

Any ideas?

Thanks,
BrianMc1958
0
Comment
Question by:BrianMc1958
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16380914
BrianMc1958,
> 1. Copy an entire database as NewDB, retaining all data, keys and indexes.

 you need to generate the script manually thru EM
BrianMc1958,
> 2. Assign ownership of NewDB to an existing user (Not the user I'm signed on as).

sp_changedbowner [ @loginame = ] 'login'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16380923
BrianMc1958,
> . As a bonus, copy it from one machine to another.
it's better to take the backup from the first machine and restore it on the other
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16380941
for what purpose ?
and how frequently would you be doing it.

have you considered snapshot replication?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:BrianMc1958
ID: 16381621
I'm doing it because as my company's development proceeds, we typically "give" our current database to testers, and then we copy the database to do our new development on.  We do that about once every two weeks.

I will also need to have a customer do it at their site soon, and I would much prefer to just hand them a script to do this, rather than walk them through it.

We keep getting errors in the copy.  MS SQL will sometimes fail to copy the keys and indexes for some tables, or just fail with mysterious messages.  It's costing us enough time that it seems worthwhile to look for a more bulletproof method.

I should one box is MS SQL Server 2000, and the other is 2003.  We sometimes copy within one box, and sometimes between.

I'm a newbie... What's snapshot replication?

Thanks again,
BrianMc1958
0
 

Author Comment

by:BrianMc1958
ID: 16381837
Typo:

I should SAY one box is MS SQL Server 2000, and the other is 2003.  We sometimes copy within one box, and sometimes between.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 16382658
1.  Create a full backup of the original db; this will contain everything in the original db.  Restore it as "NewDB".  

SQL code:

BACKUP DATABASE originalDb
TO DISK = 'd:\full\path\to\backup\originalDb.BAK'

RESTORE DATABASE NewDB
FROM DISK = 'd:\full\path\to\backup\originalDb.BAK'
WITH MOVE 'logical_data_file_name' TO 'd:\full\path\to\new\db\file\NewDB.MDF',
    MOVE 'logical_log_file_name' TO 'd:\full\path\to\new\log\file\NewDB.LDF'
    --,REPLACE  --if NewDB already exists

2.  EXEC NewDB.dbo.sp_ChangeDbOwner 'newOwnerName'
--note: you will need 'sa' authority to run this cmd

3.  Copies the backup to the other machine and do a restore there.  The user doing the copy must have proper authority on *both* machines, so must be a domain user, not a local SQL account.
Also, you will need to "re-sync" logins and userids for that db.  More info on that later if you need it.


Personally I suggest creating a job(s) to do this, then just running the job(s) whenever you need to.
0
 

Author Comment

by:BrianMc1958
ID: 16384034
Thanks!  I'll try it...
--BrianMc1958
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16384758
If you restore to a different machine, you will have to re-sync all native SQL userids with the corresponding native SQL login.

This will generate the commands that need run:

SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + su.name + ''', ''' + su.name + ''''
FROM sysusers su
LEFT OUTER JOIN master..syslogins sl ON su.sid = sl.sid
WHERE su.issqluser = 1 AND su.name NOT IN ('guest', 'INFORMATION_SCHEMA')
--AND sl.sid IS NULL
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question