Copy entire database

Question

4.00/5 (2 votes)

See more:

Hello guys! I am currently developing an inventory system. My system don't support any hard deleting. I wanted to include the feature of backing up the entire DB then clear the database for new records. But I just don't know how to code the copying DB. Any idea on how to do this? Thanks guys!

Posted 2-Dec-12 1:20am

gaga blues

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-12-02T01:59:00

Solution 1

Have a look at this: Backing up an SQL Database in C#[^] - it's in C#, but the code is pretty obvious and simple to translate.

Posted 2-Dec-12 1:59am

OriginalGriff

Comments

gaga blues 2-Dec-12 9:50am

I am currently trying the solution link that you gave me. I encountered an error like Backup failed for Server '<servername>'. I'm downloading the Hotfix for this now. Hope it will solve the problem. Thanks for the link!

OriginalGriff 2-Dec-12 10:04am

I saw your post at the Tip itself and thought it was probably you! :laugh:

It may be that you have miss-translated from C# to VB - but you do need to know which line it is excepting on.

gaga blues 2-Dec-12 10:16am

Yes it's me. I'm desperate :D
Here's the converted version of your code
Dim con As New ServerConnection("user\sqlexpress")
Dim server As New Server(con)
Dim source As New Backup()
source.Action = BackupActionType.Database
source.Database = "myDB"
Dim destination As New BackupDeviceItem(backUpFile, DeviceType.File)
source.Devices.Add(destination)
source.SqlBackup(server) //The exception is triggered here :(
con.Disconnect()
Anything wrong in this translated version that you see? :(

OriginalGriff 2-Dec-12 10:21am

Server name.
Double check that it is USER\SQLEXPRESS - use SSMS to make certain.

gaga blues 2-Dec-12 10:25am

I used the server explorer to come up with the user\sqlexpress. Here's the exact name in the server explorer. user\sqlexpress.myDB.dbo

OriginalGriff 2-Dec-12 10:34am

Looks ok, as does the VB code.
Have you had a look at the exception detail? And what is the path of the destination file?

gaga blues 2-Dec-12 10:41am

is the "backUpFile" and the directory to where the DB will go the same? I put a value "c:\user\my documents\db goes here" to the backUpFile.

OriginalGriff 2-Dec-12 10:51am

It shouldn't be a directory - it should be a file name:
public static void BackupDatabase()
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Sql Backup files|*.SqlBak|All Files|*.*";
sfd.DefaultExt = "*.SqlBak";
sfd.CheckFileExists = false;
sfd.CheckPathExists = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
DBAccess.BackupDatabase(sfd.FileName);
}
catch (Exception ex)
{
StringBuilder sb = new StringBuilder();
sb.Append("Unable to save the backup.\nThe stystem reported the error as:\n");
Exception e = ex;
while (e != null)
{
sb.AppendFormat(" {0}\n", e.Message);
e = e.InnerException;
}
MessageBox.Show(sb.ToString(), "Backup Database");
}
}
}
And did you check the access permissions on the container folder? (In your case c:\user\my documents\ needs to be handled with the "So it'll work now?" bit of the tip.)

(SMO errors are never very helpful - a simple "IO Authorization failure" message would have been so easy for MS to add)

gaga blues 2-Dec-12 11:02am

I was able to change the permission for the folder. I am currently having an Error in this word, DBAccess. The error states that it is not accessible. Must i declare it(and what is the datatype)? I'm sorry for being such a total noob :(

OriginalGriff 2-Dec-12 11:16am

Sorry, no - my fault! It was a copy of the method I use in one of my apps to do the backup - the two methods in the Tip are part of the DBAccess class (so it knows what the SQL server instance is - it extracts it from the connection string but I don't show that part so as not to confuse people in the Tip). It was intended to show you the code I use to save the DB copy, not something for you to paste verbatim. :laugh:
So just remove the "DBAccess." bit!

gaga blues 2-Dec-12 11:22am

WOW! Thanks! It's working now! :D Thanks for all the help! I'd treat you a cup of coffee if I can :P But anyways, thanks for the time and efforts in helping me out! :D

OriginalGriff 2-Dec-12 11:38am

You're welcome - just email the caffeine and I'll rehydrate it at this end. :laugh: