How to do a full copy of a database onto the same server with a different name
-
I'm already working on this project, and I have a way to do it. That being said there are many on here that always seem to have a better and faster way to do these kind of things.
My task was simple: take database ABC on server 123, and make a copy to server 123 with the new name DEF.
The way I did it was:
Right-Click > New Database > Name = 'DEF'
Right-Click > Tasks > Import Data > I selected all tables in the original database, and did a straight copy.
Expand Views and Stored Procedures > Right-Click each object > Script As > Create To > New Query Window
On each of those windows I changed the Using line from ABC to DEF, and executed.
So that is done...how can this be completed more efficiently?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein"DOH!"
- Homer Simpson[/font] -
back and restore is the absolute fasted.
lets assume that a FULL backup exists for a given database on a certain server...if it doesn't exist, create one with copy only so you don't interfere with any existing backup strategy.
Then whether it's the same server or a different one, you restore the database as a new name.
type in a new name, select either an existing backup or click the "from device" and browse to a backup file.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Backup the original database and restore it to a new name using the WITH MOVE syntax to change the data and log file names.
-
Backup/Restore is best.
You can also do a copy by: right-click your db >> Tasks >> Copy Database then go through the wizard.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Glad I posted this, because my brilliant idea caused the system to crash.
I tried the Tasks > Copy Database option. I made it most of the way through, but that ended with an error saying the database name already exists on the server. This was after I typed the new name, so I'm not sure how it died.
Lowell, I'm attempting your method now, but I cannot seem to find the Restore Sandbox interface you have a screen shot of. Can you offer a touch more direction?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein"DOH!"
- Homer Simpson[/font] -
jarid.lawson (11/26/2012)
Glad I posted this, because my brilliant idea caused the system to crash.I tried the Tasks > Copy Database option. I made it most of the way through, but that ended with an error saying the database name already exists on the server. This was after I typed the new name, so I'm not sure how it died.
Lowell, I'm attempting your method now, but I cannot seem to find the Restore Sandbox interface you have a screen shot of. Can you offer a touch more direction?
Right-Click DB > Tasks > Restore > Database
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
ok sorry...
you can restore a database that doesn't exist yet.
in the object explorer in SSMS, you should be able to expand the server and see the Folder for databases...it may be expanded already,and you see the list of databases...
if you right click on the Database Folder, the third selection is "Restore Database"
the dialog i posted shows up there...that's where you begin, by putting in the new database name.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That got it. Thank you for all the help.
If someone comes across this post later, and wants to see a walk through, I'll upload the screen capture of what I did to YouTube and will post the link to that as soon as I'm out from behind my company's fire wall.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein"DOH!"
- Homer Simpson[/font] -
You want to use a T-SQL script to do the backup and restore, not go thru the GUI. That will speed up and simplify future repeats of the same task.
I wrote T-SQL that generates the backup/restore T-SQL.
But you could just create "canned" code and save it, leaving it (relatively) hard-coded.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply