NOTE:
Most/many of these procedures are still relevant,
but this page was created in the SQL Server 2000 era!
Relevant facts and restrictions:
No two databases with the same name can exist within the same instance of SQL Server.
No two databases can share database any of their data (.mdf/.ndf) or log (.ldf) files.
ALTER DATABASE...
can rename databases,
can rename logical filenames,
can only alter physical filenames on the TempDB. Interesting restriction!
can't rename a DB through Enterprise Manager.
I can attach a backed up database to another machine.
I can copy a database (using the DTS Export Wizard) to copy a database, constraints, objects and data to the same SQL Server.
To move a Database File (other than for 'Master' and 'TempDB'...
First, do sp_helpfile to make sure you have the exact name of every file in the DB.
Second, detach the DB with sp_detach_db, which you can't do while the DB is in use.
Third, move files to their new location and/or rename them.
Finally, execute sp_attach_db pointing to the DB name and file locations.
Q224071
How to move SQL Server databases to a new
location by using Detach and Attach functions in SQL Server.
Relevant BOL Topics:
"Copying Databases" and "Rename a Database"
Relevant Tools and Procedure(s)...
|
Task: |
Method: |
Limitation(s): |
|
Duplicate a database with: Copy Database Wizard |
Start by clicking on the Wizards toolbar button, then
Management, then Copy Database Wizard, |
Source & Destination Server cannot be the same server. Destination server cannot have a DB with the same name as the source. [Double check] Does not create constraints at the destination |
|
Copy Databases and Database Objects: DTS Transfer Databases Task |
Copies or Moves one or more selected databases to a destination. |
From BOL: "The Transfer Database Objects tasks are custom tasks used by the Copy Database Wizard..." |
|
Copy Tables & Views: DTS Import/Export Wizard |
Use a Query to Specify the Data to Transfer Copy Objects & Data between SQL Server Databases (same instance) |
Copy Tables & Views... Use a Query to Specify... Copy Objects & Data...will permit copying to the same or different SQL instance or SQL Server but you must create a new DB on the fly; this also recreates all constraints!
|
|
Move and/or Rename Database files to the same or different server or instance: Detach/Re-attach DB |
Use Enterprise Manager, or, System-Stored Procedure |
Enterprise Manager...this retains constraints, database users, and a database diagram System-Stored Procedure...sp_detach_db and sp_attach_db After the database is detached, you can attach the DB to a new or same destination. |
|
Relocate a Database: Backup/Restore |
After a database is backed up to a file or device, you can attach the DB to a new destination. |
|
|
Rename a Database: sp_renameDB 'oldname', 'newname' |