CODEWITHSUNDEEP
c#sql-server
ghanshyam.mirani
ghanshyam.mirani

Reputation: 3101

Issue related to Database Backup and Restore in .Net

I have C#.Net winform application having SQL Server Database in SQLExpress with Windows Authentication. There is a Option related to Backup and restore batabase. In DBbackup i have provided user to select folder and database will be copy to that folder. for restore database i have written following line:

System.IO.File.Copy(filePath, Application.StartupPath + "\\dbSTK.mdf",true);

it throws an exception saying dbSTK is already in use. How can i overwrite file that is already in use?

Upvotes: 2

Views: 606

Answers (3)

Code Magician
Code Magician

Reputation: 24032

A .mdf file is not a backup file, but the actual data file. You cannot simply copy a running database (or overwrite a running database.) Typically you would have to use sp_detach_db to get the file in state that you can copy/archive and sp_attach_db (or create database for attach) to restore the db.

If you just made a copy of the running .mdf as a backup, then the odds are pretty good that you won't be able to attach that file.

While the above is the correct method to detach/attach a database, I would suggest actually performing a backup/restore. It doesn't require taking the database offline and is generally a better approach to managing backups.

How to attach a data file in SQL Server: http://msdn.microsoft.com/en-us/library/ms179877.aspx

How to detach a database in SQL Server: http://msdn.microsoft.com/en-us/library/ms188031.aspx

How to perform a backup on SQLEXpress: http://msdn.microsoft.com/en-us/ms186865.aspx

How to restore a backup on SQLExpress: http://msdn.microsoft.com/en-us/ms186858

Upvotes: 3

Adam Tuliper
Adam Tuliper

Reputation: 30162

Do not copy the mdf file. If you want to backup by code, check out this posting Using Smo.Backup to backup SQL Server database to string

If you want to schedule a backup (to your backup folder) create a new maintenance plan using the maintenance plan wizard in sql management studio: http://msdn.microsoft.com/en-us/library/ms189036.aspx

Upvotes: 1

H-Man2
H-Man2

Reputation: 3189

Don't backup by just copy the database file. Use the SQL command BACKUP DATABASE. Using this, the server does not need to be stopped and the server can clean up internal stuff.

Upvotes: 1

Related Questions