Restoring a SQL Server database directly from another database

Question

How to restore the original database without an appointment?

We can do this from SQL Server Management Studio, but I want to do this with a script that I can execute as a scheduled task.

Answer 1

Select a recovery source - another database. To create a SQL script - click

Answer 2

Note: using SQL Server Management Studio to restore a database with the From database option, however, restores the database from an existing backup.

You may need to copy the database. In this case, you should use a combination of backup and recovery, for example:

The following example uses both BACKUP and RESTORE to make a copy of the AdventureWorks2008R2 database. The MOVE statement calls the recovered data and the log file to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of files in the database restored. A new copy of the database is called TestDB.

Copy BACKUP DATABASE AdventureWorks2008R2 TO AdventureWorks2008R2Backups ; RESTORE FILELISTONLY FROM AdventureWorks2008R2Backups ; RESTORE DATABASE TestDB FROM AdventureWorks2008R2Backups WITH MOVE 'AdventureWorks2008R2_Data' TO 'C:\MySQLServer\testdb.mdf', MOVE 'AdventureWorks2008R2_Log' TO 'C:\MySQLServer\testdb.ldf'; GO 

Here's an example of how to automate the process of copying data - Create a copy of an existing SQL Server database