SQL Script to Backup your Database

[Drewww]

Over the past week+ I have been learning a little more about SQL. Finally got a working script you can use to keep a backup of your databases.

This will create a folder C:\ROMServerBackups, in it will be a folder index by Year,Month,Day,Hour,Minute,Second
In that folder will be the five databases.

DECLARE    @Newfolder varchar(200)SELECT    @Newfolder='C:\ROMServerBackups\' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-')
EXEC master.dbo.xp_create_subdir @Newfolder


DECLARE    @DB1 varchar(200)
DECLARE    @DB2 varchar(200)
DECLARE    @DB3 varchar(200)
DECLARE    @DB4 varchar(200)
DECLARE    @DB5 varchar(200)


SELECT    @DB1 @Newfolder + N'\ROM_Account.bak'
SELECT    @DB2 @Newfolder + N'\ROM_GlobalDB.bak'
SELECT    @DB3 @Newfolder + N'\ROM_ImportDB.bak'
SELECT    @DB4 @Newfolder + N'\ROM_World.bak'
SELECT    @DB5 @Newfolder + N'\ROM_WorldLog.bak'


BACKUP DATABASE ROM_Account        TO DISK = @DB1 WITH INIT, NAME = N'ROM_Account',    SKIP, STATS = 10
BACKUP DATABASE ROM_GlobalDB    TO DISK = @DB2 WITH INIT, NAME = N'ROM_GlobalDB',    SKIP, STATS = 10
BACKUP DATABASE ROM_ImportDB    TO DISK = @DB3 WITH INIT, NAME = N'ROM_ImportDB',    SKIP, STATS = 10
BACKUP DATABASE ROM_World        TO DISK = @DB4 WITH INIT, NAME = N'ROM_World',        SKIP, STATS = 10
BACKUP DATABASE ROM_WorldLog    TO DISK = @DB5 WITH INIT, NAME = N'ROM_WorldLog',    SKIP, STATS = 10

I have tested the backup in a virtual machine and it does appear to work just fine for me. And I see this forum isn't friendly to my TABs, I tried to keep it aligned and tidy so it makes sense and people can learn from my work.

Don't forget to click like, lets me know ppl find this useful.

[MentaL]

[Turmalin]

Nice thing!
I use almost the same code, exclude simple automatization.


Code on C#:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace DBBackup
{
    public partial class Form1 : Form
    {
        private SqlConnection Sql;
        string connectionString;
        private bool start = true;
        public Form1()
        {
            InitializeComponent();
            _initialize();
            textBox1.Text = "60";
        }


        private void button1_Click(object sender, EventArgs e)
        {
            Thread th = new Thread(() =>
            {
                try
                {
                    _request();
                }
                catch (Exception ex)
                {
                    _print(ex.Message);
                }
            });
            th.Start();
        }


        private void _initialize()
        {
            connectionString =
                @"Persist Security Info=False;Integrated Security=SSPI;server=YOUR_SERVER_NAME;Connect Timeout=30";
            Sql = new SqlConnection(connectionString);
        }


        private void _request()
        {
            while(start)
            {
                _print("Start check at " + DateTime.Now.ToString("dd.MM.y H.mm.ss"));
                try
                {
                    _query("select 1");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                _print("Test query executed");
                var f = File.ReadAllText("request.sql");
                f = f.Replace("KEY_DATA", DateTime.Now.ToString("dd.MM.y"));
                f = f.Replace("KEY_TIME", DateTime.Now.ToString("H.mm.ss"));
                _query(f);
                DateTime dt = DateTime.Now;
                double hours;
                int minutes;
                int.TryParse(textBox1.Text, out minutes);
                hours = minutes/60f;
                _print("Next check at " + dt.AddHours(hours).ToString("dd.MM.y H.mm.ss"));
                Thread.Sleep(1000 * 60 * minutes);
            }
        }


        private List<Dictionary<string, object>> _query(string comm)
        {
            List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
            Sql.Open();
            try
            {
                using (SqlCommand command = new SqlCommand(comm, Sql))
                {
                    command.CommandTimeout = 999;
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Add(Enumerable.Range(0, reader.FieldCount)
                                .ToDictionary(
                                    i => reader.GetName(i),
                                    i => reader.GetValue(i)));
                        }
                    }
                }
                _print("Ready at " + DateTime.Now.ToString("dd.MM.y H.mm.ss"));
            }
            catch (Exception ex)
            {
                _print(ex.Message + ", " + DateTime.Now.ToString("dd.MM.y H.mm.ss"));
            }
            
            Sql.Close();
            return result;
        }


        void _print(object text)
        {
            printer.AppendText(text + "\n");
        }


        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            Process.GetCurrentProcess().Kill();
        }
    }
}

Declare @account varchar(500);
Declare @global varchar(500);
Declare @Import varchar(500);
Declare @world varchar(500);
Declare @w_log varchar(500);
Declare @folder_date varchar(100);
Declare @folder_time varchar(100);


SET @folder_date = 'MD C:\backup_auto\KEY_DATA';
SET @folder_time = 'MD C:\backup_auto\KEY_DATA\KEY_TIME';


exec sp_configure 'xp_cmdshell', 1;
EXEC xp_cmdshell @folder_date;
EXEC xp_cmdshell @folder_time;
--convert(varchar, getdate(), 104)


SET @account = ('C:\backup_auto\KEY_DATA\KEY_TIME\ROM_Account.bak');-- CURRENT_TIMESTAMP;
SET @global = ('C:\backup_auto\KEY_DATA\KEY_TIME\ROM_GlobalDB.bak');-- CURRENT_TIMESTAMP;
SET @Import = ('C:\backup_auto\KEY_DATA\KEY_TIME\ROM_ImportDB.bak');-- CURRENT_TIMESTAMP;
SET @world = ('C:\backup_auto\KEY_DATA\KEY_TIME\ROM_World.bak');-- CURRENT_TIMESTAMP;
SET @w_log = ('C:\backup_auto\KEY_DATA\KEY_TIME\ROM_WorldLog.bak');-- CURRENT_TIMESTAMP;




BACKUP DATABASE [ROM_Account] TO  DISK = @account WITH NOFORMAT, INIT,  NAME = N'ROM_Account.bak', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE [ROM_GlobalDB] TO  DISK = @global WITH NOFORMAT, INIT,  NAME = N'ROM_GlobalDB.bak', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE [ROM_ImportDB] TO  DISK = @Import WITH NOFORMAT, INIT,  NAME = N'ROM_ImportDB.bak', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE [ROM_World] TO  DISK = @world WITH NOFORMAT, INIT,  NAME = N'ROM_World.bak', SKIP, REWIND, NOUNLOAD,  STATS = 10
BACKUP DATABASE [ROM_WorldLog] TO  DISK = @w_log WITH NOFORMAT, INIT,  NAME = N'ROM_WorldLog.bak', SKIP, REWIND, NOUNLOAD,  STATS = 10

Need to replace YOUR_SERVER_NAME string.
This would make backup each N secound to C:\backup_auto folder.

[Drewww]

Auto Backup, very nice.

[Turmalin]

Usable thing, when some shit happens and you forgot to make backup manually)