Bdtjtk

I have a SQL Server 2008 database (A) that I want to copy to a new server to use as a copy of previous state (A_BKP) .

I have understood from https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-to-a-new-location-sql-server?view=sql-server-2017

That this is possible in later versions but I also want to move the target files from the C: drive to the D:

Is this possible with SQL Server 2008, and what steps would I need to prep the database backup file to import but to the D: drive.?

If you want to "move" your DB to another server first you need to DETACH your DB.
Instruction is :

• Open SSMS


• Choose your db which you want to move.


• Right Click --> Tasks --> Detach


• Detach Database pop-up will be shown.


• Before you detach your database you need to sure no one connected to database. Check the "Message" column. It will show you if there are connections. If there are connections but you still want to Detach your database you can click "Drop Connections" check box.


• Press Ok

Now you can move your database files (MDF,LDF,FDF) to another location.

To ATTACH your database to new server :

• Connect to new server using SSMS


• Right Click on Databases and Click Attach


• Press Add button in middle right.


• Choose the MDF (Main DB File) file which you moved and press ok.


• Press ok.

If you want to restore database backup to new server as NEW database :

• Connect to new server using SSMS


• Right Click on Databases and Click Restore Database


• Click Device on top right under the Source and add your backup file


• If you want to change your db name you can update it on Destination --> Database section.


• Go to Files tab on top left


• You can see file locations there. if you want to change the location simply you can update it on "Restore As" column.


• Press Ok.

or use following script :

USE [master]

RESTORE DATABASE [A]

FROM  DISK = N'<yourbackuplocationA_BKP.bak>' 

WITH  FILE = 1,  

MOVE N'A_Main' TO N'<new location for MAIN database file<file name>.mdf>',  

MOVE N'A_Log' TO N'<new location for LOG database file<file name>.ldf>',  

NOUNLOAD,  

STATS = 5

GO

If you want to restore database backup to new server to EXISTING database :

ALTER DATABASE [A] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [A] 

    FROM  DISK = '<yourbackuplocationA_BKP.bak>' WITH  FILE = 1,  

    MOVE N'A_Main' TO N'<new location for MAIN database file<file name>.mdf>',  

    MOVE N'A_Log' TO N'<new location for LOG database file<file name>.ldf>',  

    NOUNLOAD,  

    STATS = 5

ALTER DATABASE [A] SET MULTI_USER