SQL Server MDF 文件 - 如何备份 [英] SQL Server MDF Files - How to back up

问题描述

场景如下:

我们有一个包含三个数据库的公司网站.它运行 SQL Server 2005.这三个数据库链接到 MDF 文件.有人试图直接复制/粘贴这些 MDF 文件之一，该文件对导致只读错误的文件进行了某种锁定.我们通过分离/重新连接解决了这个问题.

We have a corporate website with three databases. It's running SQL Server 2005. The three databases are linked to MDF files. Someone tried to directly copy / paste one of these MDF files that did some kind of lock on the file that caused a read-only error. We fixed this by detaching / reattaching.

为了防止再次发生这种情况，我们希望获取这些数据库的副本以降级到我们的 QA 数据库.

To prevent this again, we're looking to get copies of these databases to demote to our QA database.

我们可以在不关闭公司网站的情况下制作副本的最佳方式是什么?我试图搜索谷歌并没有想出一个像样的方法.数据库相当小，我能想到的最坏情况就是关闭 SQL Server 服务，然后复制 MDF，然后重新启动服务.我假设最多停机时间为 10 秒.

What's the best way we can make copies without taking the corporate site down? I tried to search google and didn't come up with a decent way. The databases are fairly small, and the worst case I can think of is just to shut down the SQL Server Service, then just copy the MDF's, then restart the service. I'm assuming that's 10 seconds at the most downtime.

[阅读本文的人请注意，不要直接复制 MDF 文件，请确保您至少将 DB 脱机或 DB 可以被锁定]

推荐答案

与其尝试复制和锁定 MDF 文件，不如将数据库备份到文件系统，然后将其复制.

Rather than trying to copy and locking the MDF files, just take backups of the database to the filesystem and then copy those off.

您询问备份期间用户是否会受到影响...

You ask whether users will be affected during a backup...

我们不能说不会对用户产生任何"影响，因为我们当然是在处理数据库.但是，备份实时数据库是常态.通常，您会在夜间或低使用率期间安排备份，以尽量减少中断.

We can't say that there won't be "any" affect on the users because of course we are doing things with the database. However, backing up live databases is the norm. Generally, you would schedule the backup overnight or during a period of low use so that there is minimal disruption.

看看这个问题，它提供了一些很好的信息:https://serverfault.com/questions/100490/what-happens-during-a-live-sql-server-backup

Check out this questions which has some good information provided: https://serverfault.com/questions/100490/what-happens-during-a-live-sql-server-backup

这篇关于SQL Server MDF 文件 - 如何备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！