We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

BrianMc1958
BrianMc1958 asked
on
Medium Priority
859 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
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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'
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
CERTIFIED EXPERT
Top Expert 2011

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

have you considered snapshot replication?

Author

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

Author

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.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks!  I'll try it...
--BrianMc1958
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.