Backup from "dbo" to "backup"

Category: sql server multiservermgmt

Question

jp2code on Fri, 04 Dec 2015 15:34:25


I have been assigned the task of backing up our database in a new way.

Currently, our database "Erp" will be backed up to another database called "Erp_Backup".

My task, specifically, is to:

Revert back to the just one primary connection, but to fixup the table name to start with dbobackup. instead of dbo.  when it's time for saving the archive data.

Does anyone know what this means?

The simplest way I have seen of doing a backup is with the script:

> SELECT * INTO Erp_Backup.Table1 FROM Erp.Table1;

That will only handle a single table, though.


~Joe


Avoid Sears Home Improvement

Replies

scott_morris-ga on Fri, 04 Dec 2015 16:36:17


Does anyone know what this means?

This sounds like a completely short-sighted approach to creating a backup database.  What will it do?  It will more than DOUBLE the size of your existing (production?)database and cause a jump in the size of the log file.  Do you have more than one file group?  If so, is the goal to also create these backup tables using the same file arrangement?  Will this be done repeatedly?  On some regular basis? 

I don't think anyone considered security and the permissions that go along with these new tables.  What about referential integrity?  And who or what application will access these tables.  Generally speaking, applications are not designed to simply "switch" schemas when accessing a database.

The simplest way I have seen of doing a backup is with the script:

> SELECT * INTO Erp_Backup.Table1 FROM Erp.Table1;

That will only handle a single table, though.

Correct, but you are not helping your cause by using confusing schema names with database names. Before you concern yourself with the implementation details, I suggest you discuss the goal(s) with the requestor.  This approach seems doomed from the start. And given the terminology in use here, I suggest you and your organization discuss your real disaster recovery requirements.  I realize that the term "backup database" can mean different things, but having your backup database reside within the same server instance is problematic - they will compete for resources.  Having them in the same database (as proposed) means you lose both if the database or server instance goes down.  Having them on the same disk means you lose everything if the instance or disk goes down.  If you have no actual backups that are stored on some other media, then you risk losing everything. 

Charlie Liao on Tue, 12 Jan 2016 05:10:18


Hi Joe,

Have you try the suggestions? If the issue persists, please provide us more information about it, so that we can  make further analysis.

Regards,