Friday, September 2, 2011

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

1 comment :

  1. Good try in doing this post. But, it will be better if you spend a little bit more time to verify your syntax. Thanks.

    ReplyDelete