Create database and move mdf files to SQL default location

Question

I want to create a database in SQL from .mdf file present in a temp location say C:\Temp

I am using the following query

USE [master]
GO
CREATE DATABASE [database_name] ON 
( FILENAME = N'C:\temp\temp.mdf' ),
( FILENAME = N'C:\temp\temp.ldf' )
FOR ATTACH ;
GO

But I want the mdf and ldf files to be copied to the SQL default path(C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\) on attaching, as the temp location may get deleted or changed in the future.

How do I do this?

CanMan · Accepted Answer · 2014-03-06 06:15:01Z

1

You'll need to copy/move the files into the desired directory before attaching. If you want it to be done completely from within SQL you will need to:

Enable xp_cmdshell

Execute a script like below:

USE [master]
GO
DECLARE @result int
EXEC @result = xp_cmdshell 'copy c:\temp\temp.mdf
    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.mdf';
IF (@result = 0)
    EXEC @result = xp_cmdshell 'copy c:\temp\temp.ldf
        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.ldf';
    IF (@result = 0)
        CREATE DATABASE [database_name] ON 
        (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.mdf'),
        (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.ldf')
        FOR ATTACH ;
        GO
    ELSE
        PRINT 'Failure'
ELSE
    PRINT 'Failure'

edited Mar 6, 2014 at 6:15

answered Mar 6, 2014 at 6:01

CanMan

267 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suraj Rao · Accepted Answer · 2019-04-02 12:21:15Z

0

To avoid error because of space in the directory path, use double quote.

USE [master]
GO
DECLARE @result int
EXEC @result = xp_cmdshell 'copy "c:\temp\temp.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.mdf"';

IF (@result = 0)
Begin
    EXEC @result = xp_cmdshell 'copy "c:\temp\temp.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.ldf"';
    IF (@result = 0)
    Begin
        CREATE DATABASE [Your_Database_Name] ON 
        (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.mdf'),
        (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\temp.ldf')
        FOR ATTACH ;
    End
    ELSE
        PRINT 'Failure'
End
ELSE
    PRINT 'Failure'

edited Apr 2, 2019 at 12:21

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Apr 2, 2019 at 12:13

Abdul Karim Siddiqui

111 bronze badge

Collectives™ on Stack Overflow

Create database and move mdf files to SQL default location

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related