Sql-server – How to clone a database in Microsoft SQL server using PHP

PHPsql server

I am creating a portal where user can create the b2c or b2b store itself, so I need to clone the sample database to perform the task. It's not looking simple to clone the database in MSSQL. Please help if someone knows the solution.

$sql = "RESTORE DATABASE test2 FROM DISK=N'/var/pathto/TEST.bak'
        WITH REPLACE, RECOVERY
        , MOVE 'TEST' TO '/var/pathto/TEST.mdf'
        , MOVE 'TEST_log' TO '/var/pathto/TEST_log.ldf'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false) 
{
    echo '<pre>'; print_r(sqlsrv_errors()); echo '</pre>'; 
    die(); 
}else
{
    exit('executedd.');
}

What's wrong with my script?

/**************************************/

After hours of research below script is working but still showing restoring in SQL Server Management studio, check the attached screenshot.

$sql = "CREATE DATABASE test9";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false) 
{
    echo '<pre>'; print_r(sqlsrv_errors()); echo '</pre>'; 
    die(); 
}else
{
    //echo ('executedd.');
}

$sql = "USE test9";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false) 
{
    echo '<pre>'; print_r(sqlsrv_errors()); echo '</pre>'; 
    die(); 
}else
{
    //echo ('executedd.');
}

$sql = "BACKUP DATABASE test9 TO DISK = '/var/opt/mssql/data/test9.bak'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false) 
{
    echo '<pre>'; print_r(sqlsrv_errors()); echo '</pre>'; 
    die(); 
}else
{
    //echo ('executedd.');
}

$sql = "RESTORE DATABASE test9 FROM DISK='/var/opt/mssql/data/test4.bak'
        WITH REPLACE, RECOVERY
        , MOVE 'test4' TO '/var/opt/mssql/data/test9.mdf'
        , MOVE 'test4_log' TO '/var/opt/mssql/data/test9_log.ldf'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false) 
{
    echo '<pre>'; print_r(sqlsrv_errors()); echo '</pre>'; 
    die(); 
}else
{
    //echo ('executedd.');
}
exit;

enter image description here

Best Answer

Your best option is to take and then restore a backup of the database. If your sample database is fairly static then I suggest making a gold standard backup and using that. If it's under constant modification then taking a backup and then restoring it on the spot should be sufficient. 

BACKUP DATABASE [Sample] TO DISK=N'<path to file>'

RESTORE DATABASE [TargetName] FROM DISK=N'<path to file>'
WITH REPLACE, RECOVERY
, MOVE 'file1' TO '<new path for data file>'
, MOVE 'log1' TO '<new path for log file>'

Just make those commands dynamic and grant whatever user you are using for this db_creator and db_backupoperator

However, this command assumes that [TargetName] doesn't already exist. You can check for the existence of a database by querying sys.databases. If you need to override an existing database, then you need to kick everyone out before starting the restore.

Both of the commands above should be run from [master] to avoid conflicts.

This command will set a database to single_user mode, which will kick everyone out. However, the first connection to it will take the only allowed connection, so you should run this and then immediately run the restore command, preferably in the same transaction. 

ALTER DATABASE [TargetName]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
Related Question