We help IT Professionals succeed at work.

Making a test copy of a database on the same SQL server

17 Views
Last Modified: 2020-05-04
I need to make a copy of a SQL Database to test with, on the same SQL Server.  I tried using the copy function but it is failing with The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure.  Can you either help me fix this error or tell me another way to clone the data to a different database name?
Comment
Watch Question

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Easy way to do this is...

1) Dump the entire database.

2) Create a new/empty "test" database.

3) Load the dump from #1 into the empty database in #2.

Normally the dump/load process will work better for this than other approaches.

Where "better" means many timeout conditions can be avoided.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I created a full backup with the copy only option.  I then created a blank test database and I chose to restore.  I pointed to the full backup and pointed to my blank database.  On the files tab of the restore I verified that Restore As had the names of the new test database but when I try to restore I get the following error
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

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