在服务器正在使用时复制SQL Server MDF和LDF文件 [英] Copy SQL Server MDF and LDF files while server is in use

问题描述

  Public Shared Sub CopyFlashScriptFile（ByVal SourceDirectory As String，ByVal DestinationDirectory As String）
尝试
 Dim f（）As String = Directory.GetFiles（SourceDirectory）
 For i As Integer = 0 To UBound（f）
 File.Copy（f（i），DestinationDirectory&\& System.IO.Path.GetFileName（f（i）），True）
下一个
作为异常捕获
 MsgBox（ex.Message）
结束尝试
结束小组
  

我正在复制数据库文件， .mdf 和 .ldf 。哪些正在被应用程序使用。现在的问题是，当我尝试复制文件，它会引发错误
$ b

文件正在被另一个进程使用

任何人都可以帮我解决这个问题吗？

是否有我可以编程方式停止SQL Server和复制然后启动服务器？

解决方案

我正在使用.mdf文件应用程序...在系统崩溃或格式的情况下，用户将失去数据...如果用户将数据（.mdf）复制到其他驱动器..他/她可以用新的.mdf文件替换这是一个有所有数据的老年人...纠正我，如果我错了...谢谢。

这正是正常备份是为了。

正如你自己所注意到的，你可以通过简单地复制 .mdf 来备份SQL Server数据库。和 .ldf 文件，但缺点是只能在SQL Server服务没有运行。

停止SQL Server服务只是为了备份数据库不是一个好主意，因为你的用户不能访问数据库，而服务是停止。

在数据库是正常的情况下（通常是 .bak 文件）运行，所以不需要每次都要停止SQL Server的备份。

有多种方法可以做备份：

a）手动在SQL Server Management Studio中：

请参阅中的第一个链接杰森·埃文斯的答案

b）如果你想定期进行备份（比如每天一次），你需要使用 sqlcmd 。

杰森·埃文斯在他的回答中也描述了这一点，但国际海事组织有一个更简单的方法 - 你只需要两个文件，每行一行。请参阅如何在SQL Server Express版本中创建作业。

（如果您使用的是完整的SQL Server版本，而不仅是Express，则可以 set而不是在SQL Server Express中，所以你必须像上面描述的那样手动完成）。

I am using the following code to copy files from one folder to another...

Public Shared Sub CopyFlashScriptFile(ByVal SourceDirectory As String, ByVal DestinationDirectory As String)
   Try
      Dim f() As String = Directory.GetFiles(SourceDirectory)
      For i As Integer = 0 To UBound(f)
         File.Copy(f(i), DestinationDirectory & "\" & System.IO.Path.GetFileName(f(i)),True)
         Next
   Catch ex As Exception
      MsgBox(ex.Message)
   End Try
End Sub

The files I am copying are database files, .mdf and .ldf. Which are being used by the application. Now the problem is when I try to copy the files it throws an error

file is being used by another process

Can anyone help me with this?

Is there anyway I can programmatically stop SQL Server and copy the files, then start the server again?

解决方案

I am using the .mdf file in my application...in case of a system crash or format the user is going to loose the data..so if the user copies the data(.mdf) to some other drive ..he/she can replace the new .mdf file with the old one which has all there data...correct me if i am wrong...thanks.

That's exactly what "normal" backups are for.
As you noticed yourself, you can backup a SQL Server database by simply copying the .mdf and .ldf files, but the downside is that you can only do this when the SQL Server service is not running.

And stopping the SQL Server service just to backup the database is not a good idea, because your users can't access the database while the service is stopped.

Taking a "normal" backup (usually a .bak file) can be done while the database is running, so there's no need to stop SQL Server every time you want to make a backup.

There are several ways how to do a backup:

a) Manually in SQL Server Management Studio:
see the first link in Jason Evans' answer

b) If you want to take a backup regularly (say, once a day) you need to use sqlcmd.
Jason Evans described this in his answer as well, but IMO there's an easier way - you need only two files with one line each. See How to create jobs in SQL Server Express edition.

(if you were using a full SQL Server edition and not only Express, you could set up a Maintenance Task in Management Studio instead, but that's not possible in SQL Server Express, so you have to do it manually like described above).

这篇关于在服务器正在使用时复制SQL Server MDF和LDF文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！