back up .mdf and .ldf files?

[basti42]

hello!
how to back up .mdf and .ldf files?

i used File.Copy but always show the error msg, "the file is used by another process"

tnx much

[Paul M]

That means the file you are trying to copy is in use. So basically until you end the process you can not involve it in any other operations.

[jmcilhinney]

If you want to backup an SQL Server database you either do it using Management Studio or else execute a T-SQL BACKUP DATABASE statement.

http://search.msdn.microsoft.com/sea...=00&lang=en-us

[Paul M]

Jmc, I am not much of a database programmer, but doesn't the process need to be closed?? Sorry i am just a little confused and didn't interpret the question properly -.-

[szlamany]

MS SQL server allows hot backups while the database is in use - through the T-SQL BACKUP command or through doing a BACKUP with the Mgt Studio GUI.

Actually this is the preferred method of doing a SQL backup - as it respects transactions that are in process and a restore from a backup done this way will properly retain the data that was committed as of the moment of backup.

[basti42]

SqlConnection conn = new SqlConnection(cVar.conString);
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "backup database DBName to disk = '" + Application.StartupPath + @"\Backup\DBname.MDF" + "'";
comm.ExecuteNonQuery();


this is working but .ldf does not included, i have to backup both
.MDF and .LDF right?

[szlamany]

That doese backup the MDF and LDF all into one single .BAK file.

You are naming the file poorly - do not use this:

Backup\DBname.MDF" + "'";

instead use this...

Backup\DBname.Bak" + "'";

[szlamany]

Also - you should timestamp those filenames - as doing a backup into an existing .BAK file will keep growing that file - adding new "backup sets" to the .BAK file. That only confuses you later if you need to restore.

btw - if a query window you can execute RESTORE FILELISTONLY on a .BAK file and see what's in it. The .MDF and .LDF parts of the .BAK and how many backup sets are in it.

[basti42]

sorry its my first time to back up in MS SQL 2005
if "backup database" is for backup
what is the command if i want to restore the .BAK

do i need to reattached myDatabase?
or extract the .BAK and then overwrite the old one?

ynx for your help

[basti42]

sorry for my question...
i tried to open MS SQL Server Management Studio and
tried the Restore Database and it works fine.

i think i don't need to use the RESTORE DATABASE command

thank you and God Bless.