Scribd
Upload
7 views1 page

SQL Server Database Migration Steps

The document provides SQL commands to rename a database from Optics to Dr_Optics, detach and reattach the database files to a new location, copy data between the databases, and then detach and delete the Dr_Optics database.

Uploaded by

wolfegypt
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
7 views1 page

SQL Server Database Migration Steps

The document provides SQL commands to rename a database from Optics to Dr_Optics, detach and reattach the database files to a new location, copy data between the databases, and then detach and delete the Dr_Optics database.

Uploaded by

wolfegypt
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

sqlcmd -S".

\SQLExpress"
--------------------------------------------------USE master;
GO
EXEC sp_dboption Optics, 'Single User', True
GO
EXEC sp_renamedb 'Optics', 'Dr_Optics'
GO
EXEC sp_dboption Dr_Optics, 'Single User', False
GO
EXEC sp_detach_db @dbname = Dr_Optics
GO
--------------------------------------------------move mdf and ldf from instanace data folder to C:\Dr_Optics\*.mdf,ldf
--------------------------------------------------EXEC sp_attach_db @dbname = Dr_Optics
, @filename1 = C:\Dr_Optics\[Link]
, @filename2 = C:\Dr_Optics\[Link]
GO
--------------------------------------------------run opticube to create the new db
--------------------------------------------------DELETE FROM [Link] WHERE TreatmentID>1
SET IDENTITY_INSERT [Link] ON
INSERT [Link] SELECT * FROM Dr_Optics.[Link] WHERE Treatm
entID>1
SET IDENTITY_INSERT [Link] OFF
--------------------------------------------------DELETE FROM [Link] WHERE ItemID>1
INSERT [Link] SELECT * FROM Dr_Optics.[Link] WHERE ItemID>1
--------------------------------------------------SELECT * FROM [Link]
SELECT * FROM Dr_Optics.Misc
Select * FROM [Link]
Select * FROM Dr_Optics.Treatments
--------------------------------------------------EXEC sp_detach_db @dbname = Dr_Optics
GO
--------------------------------------------------DELETE FOLDER:\Dr_Optics

You might also like