db restore using SQL

Another lesson learned:

RESTORE FILELISTONLY
FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db_200403280303.BAK'

Gives you something like:

LogicalName PhysicalName Type FileGroupName Size MaxSize
DB_Data d:\MSSQL\data\DB_Data.mdf D PRIMARY 51838976 35184372080640
DB_Data_Log d:\MSSQL\data\DB_Data_log.ldf L (NULL) 1310720 35184372080640

Next:

RESTORE DATABASE IDF_main
FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db_200403280303.BAK'
WITH recovery, MOVE DB_Data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_Data.mdf',
MOVE 'DB_Data_Log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_Data_Log.ldf'

Result: DB restored without SQL enterprise manager AND to another server.

Nice.

Comments

[Aris]

I would suggest for everyone that is using SQL Server to manage their backups thru backup devices, that means organize backup devices so they can access their backup devices thru logical names and not having to remember the physical names. In this way even if they need at a point to change drive letters or directories, the logical name is always the same (I see no reason in changing that) so the statement is always the same.

E.G.

backup database db1 to bck1 with format,init,stats=1

You can always manage your backup devices over the Management folder (Enterprise Manager) or even thru T-SQL statements.

Just a suggestion.