SQLTeam.com | Weblogs | Forums

Creating SQL Database Backups (from dbo to archive)


#1

We are currently running SQL Server 12.

Our working database (not System Databases) is PrimaryErp and referenced in our projects using the familiar dbo syntax.

Currently, to do backups, we right-click the database from Microsoft SQL Server Management Studio (MSSMS), select Tasks > Back Up....

We have a similarly named database called PrimaryErp_Archive that these backups are restored to.

Management has learned that the database can be backed up differently. Both the Live and the Archive databases can have the same names. Live would still be referenced with the familiar dbo prefix; however, Archive could be prefixed with archive or backup.

Management simply told me this would be better, without explanations.

How is this better?

And, more importantly, how do I do it?

As a Bonus: Is there a way this database backup technique could reliably be done in in C# code, or would I need to always do this from within MSSMS? I know how to code in C# much better than I know my way around MSSMS.


#2

Is management getting database and table confused? The dbo I use is after the database name.


#3

Oh, You are correct dj. I hadn't thought about that.

I should get this clarified.

As FYI: Management calls their backup an archive, but nothing is ever really removed from the main database. It is just a backup of the data they call archive.

So, is your new F150 still that shiny?


#4

Terminology is an ongoing problem. :smile:

The picture of the 2009 F150 was taken at the dealership for their add. I bought it used a couple of years ago and would you believe it is still relatively shiny? Needed a picture for my avatar and that was the only one handy at work. :blush:


#5

OK, I got clarification.

I need the backups to be in the SAME DATABASE but with a new schema, "dbobackup".

Why would someone want this? Is it better?

How do I do this? How do I create a schema, and how do I backup from the default schema to another schema?

I'm guessing a script would work:

SELECT * INTO [dbobackup].Table1 FROM [dbo].Table1;

That just backs up one table, though. If I need to do this to 247 tables for this particular customer, I'd need a script.

We have several customers, too, and their databases do not have identical tables.

Could I do something along these lines?

public static void Backup(string sqlConnection)
{
    using (var conn = new SqlConnection(sqlConnection))
    {
        conn.Open();
        var tables = new List<String>();
        var sqlSelectTables = "SELECT TableName FROM [dbo];";
        using (var cmd = new SqlCommand(sqlSelectTables, conn))
        {
            using (var r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    var item = String.Format("{0}", r["TableName"]).Trim();
                    tables.Add(item);
                }
            }
        }
        var fmtSelectInto = "SELECT * INTO [dbobackup].{0} FROM [dbo].{0}; ";
        using (var cmd = new SqlCommand(null, conn))
        {
            foreach (var item in tables)
            {
                cmd.CommandText = String.Format(fmtSelectInto, item);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

#6

If the goal is to have access to the 'archive' data from within the 'live' database - I would recommend looking at synonyms.

CREATE SCHEMA dbobackup WITH AUTHORIZATION dbo;
GO

CREATE SYNONYM dbobackup.Table1 FOR PrimaryErp_Archive.dbo.Table1;
GO

Your backup/restore operation can be automated using SQL scripts - and you can easily create the synonyms using a script and scheduling that through a SQL agent job. Basic outline:

BACKUP DATABASE PrimaryErp TO DISK='{backup file location}' WITH INIT;
GO

RESTORE DATABASE PrimaryErp FROM DISK='{backup file}' WITH MOVE ..., REPLACE {other qualifiers as needed};
GO

For the synonyms - look at sys.tables and build the command - something like:

SELECT 'Create Synonym dbobackup.' + s.name + ' FOR PrimaryErp_Archive.dbo.' + s.name + ';' FROM sys.tables;


#7

Hey Jeff,

Is this not a common thing to do? Until your reply, I haven't found any information on it.

Am I calling it something wrong? Is there another term SQL folks use to refer to this process?

What are the advantages of it (as opposed to simply having a 2nd database that is comprised of the backup)?


#8

Thanks Jeff! That gives me a lot to do. :slight_smile: