Difference between revisions of "Database backup and restore"

Revision as of 21:22, 2 December 2009

The following command-line commands can be used to backup and restore your database, assuming you're using the default database name (bw_dragonage_content):

Backup

sqlcmd -S .\BWDATOOLSET -E -Q "BACKUP DATABASE <Database Name> TO DISK = '<Backup Directory><Backup Filename>'"

Where:

<Database Name> is the name of your database, typically bw_dragonage_content

<Backup Directory> is the name of the directory where you want to save the backup file (directory must exist, this process will not create it)

<Backup Filename> is what you are naming the backup file

an example:

sqlcmd -S .\BWDATOOLSET -E -Q "BACKUP DATABASE bw_dragonage_content TO DISK = 'C:\Backup\bw_dragonage_content.bak'"

Restore

sqlcmd -S .\BWDATOOLSET -E -Q "RESTORE DATABASE <Database Name> FROM DISK = '<Backup Directory><Backup Filename>' WITH REPLACE"

where:

<Database Name> is the name of your database, typically bw_dragonage_content

<Backup Directory> is the name of the directory where the backup file resides

<Backup Filename> is the name of backup file you wish to restore

an example:

sqlcmd -S .\BWDATOOLSET -E -Q "RESTORE DATABASE bw_dragonage_content FROM DISK = 'C:\Backup\bw_dragonage_content.bak' WITH REPLACE"

If you're restoring to a data store that's in a location other than the store you made the backup from (for example if you're moving your data from one computer to another and the toolset is installed in a different location on the destination machine) you can add the following to the restore command to restore to the new location. If you're restoring to the same place you made the backup from you can omit this.

WITH MOVE 'bw_da_content_euts_Data' TO 'C:\Program Files\Dragon Age\toolssql\Data\bw_dragonage_content.mdf', MOVE 'bw_da_content_euts_Log' TO 'C:\Program Files\Dragon Age\toolssql\Data\bw_dragonage_content.ldf'

Using the Backup and Restore batch files

A pair of batch files have been included with the toolset install to ease the process of making backups. They can be found at:

C:\Program Files\Dragon Age\tools\DatabaseUtilities\Backup_Restore

Both the "Backup" and "Restore" scripts are controlled by settings stored in the config.ini file. If you're using the default database location the only settings you'll need to change are the filenames that backups will be stored to or read from.

Use caution when restoring your database, as this will overwrite everything in your existing database.

Also note that SQL Server is very picky about directory access permissions, and may not be able to write to the directory of your choosing. Check to make sure that your backup file was actually created after doing a backup. A common way of ensuring that SQL Server will be able to write to the backup directory is to create the backup directory directly off of the root of your drive, such as C:\Backups\.