Note: SQL Server experts might want to skip today’s lesson, as it’s aimed at beginners.

The following article explains how to backup your MS SQL Server 2008 database using SQL Management Studio. The backup can then be used at a later date to restore the database if information is lost. The backup file needs to have a .bak file extension.
Note: If your database is stored on dwdata MS SQL Server server, the backup file will be created on that Microsoft SQL Server and not on your local machine, if you are connecting remotely. If you need a copy of the backup file, please connect to the file system in the server and they can have a copy of the backup file.

To backup your MS SQL Server 2005 database, please follow these steps:

• Open SQL Server Management Studio.
• Connect to your MS SQL Server database.
• Right-click your database, expand All Tasks and select Back Up.
• Under Backup type, select Full.

• Under Destination, click Add

• Enter the path and file name for the backup file and click OK.

• Now, under the option section I like the option Overwrite and Verify backup.

• Review your backup settings and then click OK. This will back the database to the path that you have entered.

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:PathToBackupLocation[Name_of_Database].bak’”

The examples below will help.

Default SQL Server instance:

SqlCmd -E -S MyServer –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak’”


Named SQL Server instance:

SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak’”


The above create a fully restorable backup copy of “MyDB” to the file “D:BackupsMyDB.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:PathToBackupFile[File_Name].bak’”


For example:


SqlCmd -E -S MyServer –Q “RESTORE DATABASE [MyDB] FROM DISK=’D:BackupsMyDB.bak’”


The above command will restore a backup of “MyDB” from the data stored in the backup file “D:BackupsMyDB.bak”. Any changes made to MyDB 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’.

Note: SQL Server experts might want to skip today’s lesson, as it’s aimed at beginners.

Before starting, you will need to copy the SQL backup file (typically has a .BAK extension) to a local hard drive on the destination SQL Server machine.

Open SQL Server Management Studio and login to the SQL Server you want to restore the database to. It is best to either login as a Windows Administrator or as the SQL ‘sa’ user.

Once logged in, right click on the Databases folder and select ‘Restore Database’.

Click the ellipses button next to ‘From device’ under the ‘Source for restore’ section.

Set ‘File’ as the backup media and then click ‘Add’.

Browse to the SQL backup (BAK) file you want to restore.

In the Restore Database dialog, type or select the name of the database you want this backup restored to.

• If you select an existing database, it will be replaced with the data from the backup.
• If you type a database name which does not currently exist in your SQL Server installation, it will be created.

Next, select the restore point you want to use. Since a SQL backup file can hold multiple backups you may see more than one restore point listed.

At this point, enough information has been entered for the database to be restored. However, SQL backup files store information about where data files are copied so if there are any file system problems such as a the destination directory not existing or conflicting data file names an error will occur. These problems are common when restoring a backup created on a different SQL Server installation.

To review and change the file system settings, click the Options page on the left in the Restore Database dialog.

On the options page, you will want to make sure the ‘Restore As’ column points to valid folder locations (you can change them as needed). The the files do not have to exist, however the folder path must exist. If the respective files do exist, SQL Server follows a simple set of rules:

• If the ‘To database’ (from the General page) matches the restore database backup (i.e. restoring to matching databases), the respective files will be overwritten as part of the restore.
• If the ‘To database’ does not match the restore database backup (i.e. restoring to a different database), the ‘Overwrite the existing database’ will need to be checked for the restore process to complete. Use this function with caution as you can potentially restore database backup information on top of data files from a completely different database.

Generally, you can tell the databases differ based on the ‘Original File Name’ which is the internal name SQL Server uses to reference the respective files.

Once your restore options are set, click Ok.

Summary

SQL Server Management Studio makes the restore process simple and is ideal if you seldom perform database restores. This process works in every version of SQL Server from Express to Enterprise. If you are using the Express version, you can download SQL Server Management Studio Express to get access to this interface.

Links

Download SQL Server Management Studio Express from Microsoft