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

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
BrianMc1958Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LowfatspreadCommented:
for what purpose ?
and how frequently would you be doing it.

have you considered snapshot replication?
0
 
BrianMc1958Author Commented:
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
 
BrianMc1958Author Commented:
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
 
BrianMc1958Author Commented:
Thanks!  I'll try it...
--BrianMc1958
0
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.