[Guide] Auto DB backup + copy over network [BG/EN]

[AnHiMiLaToR]

Наскоро си го направих за моя сървър от подписа. Реших да го споделя с Вас, защото е по-добре да достигне до повече хора и да е от помощ отколкото да си го крия и ползвам само за мен.

Какво представлява обясненото по-долу:
Автоматично създаване на бакъп всеки ден в определено време + копиране на бакъпа по локалната мрежа на друг компютър, за да имаме копия на две места. По този начин гарантираме наличност на бакъп каквото и да става.

Тествано и работещо при мен със следния софтуер:
- Windows 7 64-bit;
- Microsoft SQL Server 2008 R2.

Направено е за сървър с една база данни, но по аналогичен начин с малко мисъл може да си го направите за повече бази данни.

ДА ЗАПОЧВАМЕ!

Прочетох обяснение как става това от официалния сайт на Microsoft, от тази статия. Кодовете по-долу са от там с леки корекции за да стават за MuOnline.

СТЪПКА 1
Отворете SQL Server Management Studio (Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio или просто си го потърсете с търсачката на start менюто).
Трябва да се логнете в него, щом сте си направили сървър значи вероятно знаете как става (Windows authentication ползам аз).

СТЪПКА 2
Отворете си ново query (бутон New query) и поставете в него следния код:

--Copyright © Microsoft Corporation.  All Rights Reserved.

--This code released under the terms of the

--Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

 

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

 

      WHILE @Loop IS NOT NULL

      BEGIN

 

-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

 

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

 

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

 

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

 

-- Generate the dynamic SQL command to be executed

 

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

 

-- Execute the generated SQL command

       EXEC(@sqlCommand)

 

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

 

END

За изпълнение на този код може да натиснете Execute (или клавиша F5).

СТЪПКА 3
Направете си нов празен .bat файл и го отворете с текстов редактор (аз ползвам Notepad++, няма значение по принцип).
Вътре трябва да поставите следния код:

sqlcmd -S [COLOR="Red"]<PCNAME>[/COLOR] -E -Q "EXEC sp_BackupLocation='[COLOR="Red"]<BACKUPLOCATION>[/COLOR]', @databaseName='MuOnline', @backupType='F'"

На мястото на червените:
<PCNAME> - тук поставете PC Name на компютъра със сървъра, може да се види от Properties н My Computer (This PC).
<BACKUPLOCATION> - желаното място, където да записва бакъпите.

Забележка:
В урока от Microsoft не е написано с PCNAME, а с ".\SQLEXPRESS" което би трябвало да взима default инстанцията на SQL сървъра от тази машина, но при мен даваше грешка и тръгна с PCNAME.

Примерни стойности:

sqlcmd -S [COLOR="Red"]ANHIMILATOR-PC[/COLOR] -E -Q "EXEC sp_BackupLocation='[COLOR="Red"]D:\MU_DB_BACKUPS\[/COLOR]', @databaseName='MuOnline', @backupType='F'"

(не забравяйте да си създадете папката за бакъпите)

По-любопитните може да отворят Microsoft сайта, който съм дал по-горе и да разгледат различните параметри какво правят - например -S -E -Q, backuptype="F" и т.н.

Сега си запишете файла, за целта на урока ще използвам име MuAutoBackup.bat, няма значение какво ще пишете, важното е в следващите стъпки да го използвате същото.

СТЪПКА 4
(копиране на бакъпите по мрежата на друго място)
(ако Ви се развали хард диска на сървъра ще имате бакъп на базата благодарение на това)

По същия начин както в стъпка 3 - направете си нов празен .bat файл и напишете вътре следното:

echo n | copy /-y [COLOR="Red"]<BACKUPLOCATION>[/COLOR]*.* \\[COLOR="Red"]<NETWORK_PC>[/COLOR]\[COLOR="Blue"]<BACKUPS_FOLDER>[/COLOR] /Z /Y

<BACKUPLOCATION> - същата папка, която сте ползвали в стъпка 3
<NETWORK_PC> - достъпна, работеща и с позволен sharing машина в локалната Ви мрежа. Записва се computer name.
<BACKUPS_FOLDER> - папката на отдалечената машина. Трябва да е с достъп Everyone на sharing-а (при Windows), може и с пароли, но Ви трябват по-напреднали техники, за които не ми е цел да обяснявам в текущия урок.

Примерна ситуация:

echo n | copy /-y [COLOR="Red"]D:\MUSQLBackups\[/COLOR]*.* \\[COLOR="Red"]backup-server[/COLOR]\[COLOR="Blue"]MU_LOCAL_BACKUPS[/COLOR] /Z /Y

Какво прави този код? Опитва се да копира всичко от папката с бакъпите, но ако файла съществува автоматично му дава Skip, т.е. винаги копира само файлове, които ги няма там.

Запишете си файла и го оставете настрани, по същия начин както с първия. Име на файл, да речем COPY_BACKUPS.bat.

СТЪПКА 5
Отворете си Task Scheduler (Start меню и търсене).
В тази програма, при мен са в дясно, има Раздел Actions. Аз съм на Windows 7, на по-нови или по-стари може да е по-различно, но би трябвало да е аналогично и с малко мислене да се справите.

Там има изброени Create Basic Task, Create Task и т.н. още 5-6 надолу.
Избираме Create Basic Task.

Name и Description - няма значение какво ще се напише. Нещо, което да Ви е полезно на Вас за различаване. Например: MU AUTO BACKUP.

На следващия екран имаме Trigger - тук може да изберете кога да се извършва бакъпа. Аз съм си избрал Dailiy - ежедневно.

На следващия екран при избрано Daily имаме възможност да настроим дата на първото тръгване и час за всички изпълнения в бъдеще, включително и първото.

Датата трябва да е днешна, аз съм си избрал часът да е към 4-5 сутринта, за да има най-малко играещи по това време и за да сме получили най-големия възможен обем данни през изминалия ден.

Следващ екран: Action - избира се "Start a program".

Program/script: тук натиснете Browse и отидете до първия .bat файл, а именно: MuAutoBackup.bat

На следващия екран имаме просто Finish.

Сега новата задача би трябвало да се е появила в списъка - намерете я и натиснете дясно копче на мишката -> Properties.

От там навигирайте до Actions, изберете New и посочете втория .bat файл, който създадохме преди малко (COPY_BACKUPS.bat).

След това, просто натиснете "OK" и сме готови.

Ако желаете, може да си тествате създаването на бакъп и/или копирането по мрежа на нещата. Просто стартирайте .bat файловете по отделно или изберете дясно копче на мишката -> Run от новата scheduled task, която създадохме в стъпка 5. При мен след изпълнение на всички тези стъпки бакъпите работят отлично и си създава всеки ден нов.

Какво може да се обърка:
- може да не са правилно настроени правата на share-натата папка на другия компютър;
- името на инстанцията на MSSQL сървъра;
- друга версия на MS SQL сървъра и различен интерфейс;
- друга версия на Windows и различен интерфейс на Task Scheduler;

Но при всички тези проблеми с малко мисъл и използване на мозък ще се справите.

Смятам, че съм обяснил лесно, конкретно, ясно и просто всички стъпки. Ако откриете грешка или неточност в моите обяснения, ще съм благодарен да я посочите и да я коригираме.

Дано да съм бил полезен и не забравяйте, че ако Ви писне да се занимавате с правене на сървър, винаги може да влезете в моя от подписа :

ENGLISH VERSION
I just made this auto backup for my server, from the signature. I decided to share the method with you because it's better to share. It's better to help. Why not?!

Allright, what's this guide about:
Auto creation of backup every day at a certain time + copy the backups to a computer on the local network. We will have copies on two different machines and this will guarantee the backup availability no matter what happens to the server.

Tested and working, my software versions are:
- Windows 7 64-bit;
- Microsoft SQL Server 2008 R2.

It is done with a MU server with just 1 database, but it should work with more databases.

LET'S GO!

I read an explanation/tutorial on this topic on the official Microsoft site, from this article. Codes below are from the very same article with little correction to work seamlessly with MuOnline.

STEP 1
Open your SQL Server Management Studio (Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio or just search for it from Start menu).
You must login to the management studio but if you already have a server that means you know how to log in there (I personally use Windows authentication).

STEP 2
Open a new query (New query button) and paste the following code:

--Copyright © Microsoft Corporation.  All Rights Reserved.

--This code released under the terms of the

--Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

 

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

 

      WHILE @Loop IS NOT NULL

      BEGIN

 

-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

 

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

 

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

 

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

 

-- Generate the dynamic SQL command to be executed

 

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

 

-- Execute the generated SQL command

       EXEC(@sqlCommand)

 

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

 

END

To execute this code, you can press the Execute button (F5).

STEP 3
Create a new empty .bat file and open it with text editor (I use Notepad++, practically it doesn't matter).
Insert the following code:

sqlcmd -S [COLOR="Red"]<PCNAME>[/COLOR] -E -Q "EXEC sp_BackupLocation='[COLOR="Red"]<BACKUPLOCATION>[/COLOR]', @databaseName='MuOnline', @backupType='F'"

Explanation:
<PCNAME> - here put your PC Name of the MU server computer, it can be seen from My Computer -> Properties.
<BACKUPLOCATION> - where to create the backups.

Note:
In the Microsoft guide it isn't written PCNAME. It is written ".\SQLEXPRESS" which should login with default SQL instance but I received some errors and it worked with PCNAME for me.

Example values:

sqlcmd -S [COLOR="Red"]ANHIMILATOR-PC[/COLOR] -E -Q "EXEC sp_BackupLocation='[COLOR="Red"]D:\MU_DB_BACKUPS\[/COLOR]', @databaseName='MuOnline', @backupType='F'"

(don't forget to create that folder)

The more advanced and curious users can open the Microsoft article and check the different parameters -S -E -Q, backuptype="F" and so on.

Now save the file, for this guide I will use MuAutoBackup.bat, it's not important but remember - you will need the name in the next steps.

STEP 4
(copying the backups to a network folder)
(if your hard disk or ssd or whatever drive you use for data broke, you will have DB backup on another place thanks to this step)

In the same way as in step 3 - create a new .bat file and paste the following:

echo n | copy /-y [COLOR="Red"]<BACKUPLOCATION>[/COLOR]*.* \\[COLOR="Red"]<NETWORK_PC>[/COLOR]\[COLOR="Blue"]<BACKUPS_FOLDER>[/COLOR] /Z /Y

<BACKUPLOCATION> - same folder as used in step 3
<NETWORK_PC> - available, working and sharing-enabled machine in the local name. Its COMPUTER NAME (My Computer -> Properties) is used.
<BACKUPS_FOLDER> - the network machine folder. It should have Everyone access in the Sharing dialog (for Windows). It can be done with password authentication of course, but you need more advanced techniques which I wont explain in this guide.

Example:

echo n | copy /-y [COLOR="Red"]D:\MUSQLBackups\[/COLOR]*.* \\[COLOR="Red"]backup-server[/COLOR]\[COLOR="Blue"]MU_LOCAL_BACKUPS[/COLOR] /Z /Y

What does this code do? It tries to copy everything from the backup folder to the network machine but if the file exists, the code "selects" Skip automatically. So we have copied only files that don't exist on the network folder.

Save that file and leave it for a little bit. File name, let's say COPY_BACKUPS.bat.

STEP 5
Open Task Scheduler (Start menu and search).
In this program, I have them in right, there is a region named Actions. I'm on Windows 7, on newer or older versions it can be different but with a little bit of thinking you should be able to do it.

There is a list with available commands: Create Basic Task, Create Task and so on - 5-6 more
Select Create Basic Task.

Name and Description - doesn't matter. Something that you will remember. For example: MU AUTO BACKUP.

On the next screen, we have Trigger - here you can choose when will we execute the backup commands. I selected Daily.

On the next screen (when Daily is selected) we have settings for the initial date and time of the first run. The hour settings is for first run and other runs in the future as well.

Date should be today's date, I chose the hour to be around 4-5 AM because we have least players playing and we have greater amount data collected for the day.

Next screen: Action - select "Start a program".

Program/script: click Browse and go to the first .bat файл, MuAutoBackup.bat

On the next screen we have just Finish.

Now, the new scheduled task should have appeared in the list. Find it and do a right click -> Properties.

Go to Actions, click New and select the second .bat file which we created earlier (COPY_BACKUPS.bat).

Now just click "OK" and we're ready.

You can test the backup creation and/or copying the stuff over network. Just start the .bat files one at a time or right click -> run on the new scheduled task. Everything works perfectly on my MU server machine.

What can go wrong:
- wrong sharing settings of the network PC;
- MSSQL server instance;
- different MS SQL server version (and a different interface);
- different Windows version and different interface of the Task Scheduler;

But all of these problems should be easy to solve if you use your brain a little bit.

I think I have explained easily, concrete, clear and simple all the steps. If you find some error in my explanations, please let me know.

I hope I helped. And don't forget - if you ever feel annoyed while administering a server, you can always come play in mine - from the signature :

 

[mistar_ti]

Много добре си го написал има само 1 лееекичък проблем от Windows 7 и Server 2008 има проблем с изпълняването на бат файлове от шедулера, но ти предполагам го знаеш, той не се появява винаги, но го има. Проблема се решава с конвертирането на бат файла в екзе например с това след това учудващо, но Майкрософт имат много добра програма и забележи безплатна за синхронизация и бекъп на файлове и това е тя. Предполагам само, че не очакваш много хора да пускат машините локално при себе си и на хостовете този шеринг е неосъществим за това принципно се използва програма която да прави архивите на фтп и ето ти една такава която работи сравнително добре. Надявам се не съм те засегнал нещо аз уважавам труда на другите, но няма да преведа какво съм написал на Английски 1-во не го знам и 2-ро се намираме в България.

 

[AnHiMiLaToR]

Проблемът с изпълнението на bat не съм го срещал, хубаво си направил, че си дал софтуер за конверт.

По другите проблеми - едно: аз правя бакъп на базата - то си го има вградено, а с този софтуер може ли да ти бакъпва базата?
Две: на всеки му е различна конфигурацията, аз си имам офис и в него няколко сървъра - MU-то е един от тях и ме устройва напълно network копирането

За всякакви други конфигурации на сървъри сисадмините вече трябва да си преценят.

Относно езика - избрах да преведа на английски, за да има по-голяма аудитория и това да може да помогне и на форума да се развива. Английски знаят страшно много хора по света, докато български - вероятно под 10 милиона.

Поздрави

 

[dota-sdso]

Браво, много добре написано, отделил си доста време да го оформиш. Хубаво е да има повече такива постове, защото повечето или не правят бакъпи или го правят с отделни софтуеъри.

Аз лично бях от вторите и навремето използвах програма, която има доста по-добри възможности от стандартните на SQL. Няма да влизам в подробности за да не изглежда, като реклама, целта ми е да покажа и друг вариант.