Following script can be used to detach or attach the database. If the database is to be from one database to another database following script can be used to detach from old server and attach to a new server. Let us learn about how to Attach and Detach Database.
Process to move the database:
-- Step 1 : Detach Database using following script USE [master] GO EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks', @keepfulltextindexfile = N'true' GO -- Step 2 : Move Data files and Log files to new location -- Do this step and move to Step 3 -- Step 3 : Attach Database using following script USE [master] GO CREATE DATABASE [AdventureWorks] ON ( FILENAME = N'C:\Data\AdventureWorks_Data.mdf' ), ( FILENAME = N'C:\Data\AdventureWorks_Log.ldf' ) FOR ATTACH GO IF EXISTS ( SELECT name FROM MASTER.sys.databases sd WHERE name = N'AdventureWorks' AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [AdventureWorks].dbo.sp_changedbowner @loginame=N'sa', @map=false GO
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. It is recommend that you do not attach or restore databases from unknown or untrusted sources. When a read-only database is detached and then reattached, the backup information about the current differential base is lost. When you attach a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata in the database, such as logins and jobs, on the other server instance.
Reference: Pinal Dave (https://blog.sqlauthority.com),