Difference between revisions of "Database backup and restore/ru"

Revision as of 10:03, 4 December 2011

Нижеследующие советы помогут вам сделать резервную копию базы данных тулсета и восстановить её. По умолчанию в статье используется дефолтное название базы данных, а именно "bw_dragonage_content".

Восстановление базы данных с помощью "MyRestore.bat"

Данный способ можно использовать в случае, если вы после установки тулсета получили ошибку "Unable to connect to Database".

Имейте в виду, что данный способ приведёт к перезаписи старой базы данных, если она существовала.

Итак, что надо сделать:

1. Создайте файл с именем "MyRestore.bat" где-нибудь в вашей системе и откройте его с помощью какого-нибудь редактора, например обычным Блокнотом.

2. Скопируйте и вставьте в новый файл следующие строки:

SET SOURCE=C:\Program Files\Dragon Age\tools\dbbak
SET TARGET=C:\Program Files\Dragon Age\tools\toolssql\Data
 
@Echo off
if not exist "%SOURCE%\bw_dragonage_content.bak" goto Sourcemiss
if not exist "%TARGET%" goto Targetmiss
:returnme
if not exist "%PROGRAMFILES(X86)%\Microsoft SQL Server\90\Tools\Binn\" goto SQLmiss64
SET varprogs=%PROGRAMFILES(X86)%
goto apply
 
:SQLmiss64
if not exist "%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\" goto SQLmiss32
SET varprogs=%ProgramFiles%
goto apply
 
:apply
Echo Running SQLcmd...
"%varprogs%\Microsoft SQL Server\90\Tools\Binn\"sqlcmd -S .\BWDATOOLSET -E -Q "RESTORE DATABASE bw_dragonage_content FROM DISK = '%SOURCE%\bw_dragonage_content.bak' WITH MOVE 'bw_dragonage_content' TO '%TARGET%\bw_dragonage_content.mdf', MOVE 'bw_dragonage_content_log' TO '%TARGET%\bw_dragonage_content.ldf'"
goto End
 
:Sourcemiss
Echo SOURCE not found. (bw_dragonage_content.bak) Please check.
Echo Edit this File with Notepad and reapply correct location.
goto End
 
:Targetmiss
Echo TARGET directory does not exist and will be created: %Target%
PAUSE
mkdir "%TARGET%"
if not exist "%TARGET%" goto Stillmiss
goto returnme
 
:Stillmiss
Echo TARGET Directory creation failed. Please check.
goto End
 
:SQLmiss32
Echo SQL is missing. Please check location (Windows 32bit): %PROGRAMFILES%\Microsoft SQL Server\90\Tools\Binn\
Echo SQL is missing. Please check location (Windows 64bit): %PROGRAMFILES(X86)%\Microsoft SQL Server\90\Tools\Binn\
Echo Edit this file with Notepad and apply correct location.
goto end
 
:End
PAUSE

3. В случае необходимости измените путь установки игры Dragon Age ("C:\Program Files\Dragon Age") в процедурах "SET SOURCE" и "SET TARGET". В тексте нашего скрипта приведён дефолтный путь установки для английской Windows XP 32bit.

4. Сохраните и запустите ваш новый "MyRestore.bat". Проследите, что вы вошли в систему с правами администратора, также, если вы работаете в Windows 7, полагаю будет нелишним запустить файл от имени администратора.

5. После окончания работы bat-ника, о чём вас известит сообщение, вы можете запускать тулсет.

6. В сулчае, если произойдут какие-либо ошибки, то смотрите приведённый ниже список возможных ошибок (ошибки могут быть скрытыми, но в первую очередь просмотрите список).

Возможные ошибки и их обработка

В случае, если во время работы bat-ника произшла ошибка, то ищите первую произошедшую при работе ошибку в нижеприведённом списке:

1. Among other messages, you get: "MSG 5133, Level 16, State1, Server ****\BWDATOOLSET, Line1" (Error, system cant find that file...)

1.1 The path to TARGET was incorrect and cant bee found. The SQL doesnt create paths on its own. Although the BAT file above will do and tell you anyway. Just in case.

2. Among other messages, you get: "Msg 3634, Level 16, State 1..." "The operating system returned the error (Access denied) while attempting..":

2.1. SQL being picky about write access here. Workaround: Change the second line into SET TARGET=C:\DB , and rerun it. You may experiment with this.

3. Among other messages, you get: "Msg 262, Level 14, State 1, Server ****\BWDATOOLSET, Line 1"

3.1 Vista Problem. (Might apply to Windows 7 too, but it was not released in 2005) Please confirm.

3.2 Log in to Windows Vista using your administrative account

3.3 Open SQL Server Surface Area Configuration Application ( Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools -->SQL Server Surface Area Configuration)

3.4 Click on Add New Administrator

3.5 Select the Member of BWDATOOLSET, and click on the Add button to move it to the right. Then click OK, and run the bat file above again.

3.6 Source Link: http://blog.techdreams.org/2007/11/create-database-permission-denied-sql.html

4. Amonst other messages, you get: "HResult 0x2, Level16, State 1" Named Pipes Provider: Could not open a connection to SQL Server [2].

4.1 Link: http://www.cryer.co.uk/brian/sqlserver/hresult_0x2_npp_error_establishing_connection.htm

Search: Put the name of the error number into a searchengine. Errors seems to be very common here, and likely can be found.

Reinstall: If you customized the installation, you may want to reinstall the Toolset and try again. There can be errors if entering something out of the norm.

Using the Backup and Restore batch files

Note: You may only use the following ,if you have no database problem. This section is meant to do backups from a working system!

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\.

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'

Troubles with sqlcmd

You may experience a strange occurrence where sqlcmd is not recognized as a dos command. You can try to download Microsoft SQL Server 2005 Command Line Query Utility at

http://www.microsoft.com/downloads/details.aspx?FamilyID=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en (scroll down a bit)

Reboot your computer and try it again, it should work.

Quick Backup & Restore

Before doing this tutorial I am going to assume you installed the toolset and sql server at the default settings.

Tutorial

1. create a folder called "Backups" in your C Drive.

2. navigate to the folder and make note of the files there.

C:\Program Files (x86)\Dragon Age\tools\DatabaseUtilities\Backup_Restore

3. go to start --> programs --> accessories --> and "right click" on "command prompt" then choose "Run as administrator"

4. A new window will come up and you'll notice in the top left it will say administrator and you should be at the windows\system32 directory

5. change directory to the backup_restore folder above.

6. run the backup.bat file and then type Backup to confirm .... This will backup your entire DA database to the backups folder. The file was around 783mb for me.