A good data-protection plan involves two core activities: backup and restore. In "Backing Up Your Database" (in the archive at http://www.sqlmag.com/Article/ArticleID/93004/sql_server_93004.html )I explain how to use T-SQL commands to back up your SQL Server 2005 Express databases. Although you can use SQL Server Management Studio Express (SSMSE) to back up your databases, T-SQL also lets you automate and schedule backups.
In this article, I cover basic full-backup restore. Before jumping into the T-SQL RESTORE commands, I should point out that similar to SQL Server 2005, SQL Server Express supports multiple types of backup and restore options including full, differential, and log backups.
As with the SQL Server Express database backup options, you can restore a database by using either SSMSE or T-SQL commands from SQLCMD or Query Editor. In "Backing Up Your Database," I used T-SQL commands to show you how to back up a database, so I'll show you the T-SQL RESTORE commands in this article. The following code restores the MediaCollection database from a previous full-database-backup disk file named MediaBackup.bak:
RESTORE DATABASE MediaCollection
FROM DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\ MediaBackup.bak'
As you can see, the RESTORE command for a full backup is straightforward. You provide the name of the database you want to restore after the RESTORE DATABASE command, the name of the disk backup file, followed by the FROM DISK = clause. It's that simple.