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.
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.
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.
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();
}
}
}
}
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;
