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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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'
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
LowfatspreadCommented:
for what purpose ?
and how frequently would you be doing it.

have you considered snapshot replication?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianMc1958Author Commented:
Thanks!  I'll try it...
--BrianMc1958
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.