Restore SQL Server DB direct from another DB

Question

How can I restore a live database without taking its backup first.

We can do this from SQL Server Management Studio, but I want to do this with script, which i can run as a scheduled job.

Please help.

Answer 1

Choose restore source - another DB. To generate a SQL script - press the button

Answer 2

Please Note: Using of SQL Server Management Studio for restoring database with "From database" option, however, restores database from existed backup.

Maybe you need to copy database. In this case you should to use backup/restore combination like:

The following example uses both the BACKUP and RESTORE statements to make a copy of the AdventureWorks2008R2 database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named 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 the example how to automate the process of databse copying - Create a copy of an existing SQL Server database