Restore SQL Server database with TSQL, RESTORE WITH REPLACE

First connect with your sql server using sqlcmd then use restore command as below.

C:\back>SQLCMD -S .\SQLEXPRESS2008

1> GO

1> SELECT NAME FROM SYS.DATABASES

2> GO

NAME

--------------------------------------------------------------------------------

------------------------------------------------

master

tempdb

model

msdb

TrainingWeb

(5 rows affected)

1> RESTORE DATABASE TrainingWeb

2> FROM DISK = 'C:\Back\traing_db.bak'

3> WITH  REPLACE,

4> MOVE 'traing_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS

     2008\MSSQL\DATA\TrainingWeb.mdf',

5> move 'traing_db_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXP

     RESS2008\MSSQL\DATA\TrainingWeb_log.ldf'

6> go

Processed 240 pages for database 'TrainingWeb', file 'traing_db' on file 1.

Processed 1 pages for database 'TrainingWeb', file 'traing_db_log' on file 1.

RESTORE DATABASE successfully processed 241 pages in 0.222 seconds (8.481 MB/sec).

Note: the logical name e.g. traing_db must match with the name of database for which you have

Created backup. Backup file name may be different but you must specify the backup database name

Specify the yellow marked entries.

Copying a database using BACKUP and RESTORE

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.

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