The most important part of a SQL Server maintenance plan is backing up your databases regularly. To backup a database, you cannot simply copy the respective MDF and LDF files of the database as SQL Server has a lock on these. Instead, you need to create a true backup file through SQL Server. While this can be done by developing a Maintenance Plan inside of SQL Management Studio, the free Express editions of SQL Server do not offer this interface. To work around this, you can easily backup your databases by running the command below while logged in as a Windows Administrator:
SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO
DISK=’X:PathofBackupLocation[Name_of_Database].bak’”
Example:
Default SQL Server instance:
SqlCmd -E -S MSSQLServer –Q “BACKUP DATABASE [Bookdb] TO DISK=’E:\Bookdb.bak’”
Named SQL Server instance:
SqlCmd -E -S MyOwnInstance –Q “BACKUP DATABASE [Bookdb] TO DISK=’E:\Bookdb.bak’”
The above create a fully restorable backup copy of “Bookdb” to the file “E:\Bookdb.bak” which can be used for disaster recovery. Of course, you can change the backup location and file to whatever you need, but make sure you specify a folder location which exists on the local machine. This backup file can then be copied to a tape drive or another external backup location.
A common question is “Can a backup file be created to a mapped drive or UNC location?” and the quick answer is no. The reason is because the SQL Server Windows Service runs as a user account which only has access to the local machine. You could change the account the service runs as, but this is highly discouraged for security reasons.
Restoring a Database Backup from the Command Line
To restore a database from a backup file, simply use the command:
SqlCmd -E -S Server_Name –Q “RESTORE DATABASE [Name_of_Database] FROM
DISK=’X:PathofBackupFile[File_Name].bak’”
Example:
SqlCmd -E -S MyServer –Q “RESTORE DATABASE [Bookdb] FROM DISK=’E:\Bookdb.bak’”
The above command will restore a backup of “Bookdb” from the data stored in the backup file
“E:\Bookdb.bak”. Any changes made to Bookdb since the backup file was created will be lost.
An important thing to remember when using the above command is that it is intended to be used on the same SQL Server that the respective backup file was created on. SQL backup files store ‘behind the scenes’ information that control where and how the data files in the backup file are copied. If you are restoring a backup from a different SQL Server, the path locations in the backup file may not match the server you are restoring to and an error will result. While this can be worked around, it is much easier to restore backups created on another SQL Server using the SQL Management Studio tool.
Note: the commands above will work on SQL 2005 and higher (any edition). For SQL 2000 and earlier, replace ‘SqlCmd’ with ‘oSql’.
Comments