恢复数据库时出错 [英] Error on restore of database

问题描述

当我在sqlserver2005 express中恢复数据库并运行新查询时，sqlserver不允许我登录.
下面是我的代码:

Hi,
When i restore my database in sqlserver2005 express and run a new query, sqlserver don''t allow login for me.
Below is my code:

USE MASTER BACKUP DATABASE DB TO DISK ='DB.BAK' WITH INIT;"

USE MASTER RESTORE DATABASE DB FROM DISK = 'DB.BAK'

我发现必须手动删除日志文件才能登录.
请帮帮我.

谢谢.

I found that I must delete log files manually to login.
Please help me.

Thanks.

推荐答案

如果要刷新事务日志，请运行这两个项目.还原MASTER数据库虽然很棘手.在下面查找主还原过程.

如果我假设DB是您的数据库实例的名称.

带有NO_LOG的转储交易数据库

DBCC SHRINKFILE(DB)

-------------------------------------------

还原SQL Server 2000 MASTER数据库
仅当数据库处于单用户模式时，才能还原MASTER数据库.要将其置于单用户模式:
1.启动命令会话.
2.要确保SQL Server未运行，请输入:
净停止MSSQLServer
这可以说这也将停止另一项服务，在这种情况下允许其继续.它还将指示服务是否已停止.
3.要以单用户模式启动SQL Server，请输入:
sqlservr -m
如果无法识别，则将其cd到"C:\ Program Files \ Microsoft SQL Server \ MSSQL \ BINN"，然后重试.
要从备份还原MASTER数据库，请使用以下脚本(在SQL Server查询分析器中运行):
使用MASTER
去
使用REPLACE从磁盘= N''D:\ Path \ MASTER.BAK''恢复数据库主数据库，恢复
去
注意:当SQL Server处于单用户模式时，如果使用"sa"帐户进行连接，您仍应该能够从SQL Server查询分析器进行连接.
上面假设MASTER数据库的文件号是3.如果不确定文件编号，则可以通过发出以下命令找到文件编号列表:
使用名词加载从Tape =''\\.\ Tape0''仅还原HeaderOnly
结果集中要查看的两个字段是"BackupName"和"Position"，这是上面要使用的文件号.
一旦还原了MASTER数据库(还原将导致数据库关闭)，请在命令行中发出以下命令来启动数据库:
净启动MSSQLServer
请记住，"sa"帐户的密码现在将与原始备份时的密码相同.

Run these two items if you are attempting to flush out transaction log. To restore the MASTER Database though that is tricky. Look Below for Master Restore Process.

If I assume that DB is the name of your Database instance.

DUMP TRANSACTION DB WITH NO_LOG

DBCC SHRINKFILE (DB)

-------------------------------------------

Restore SQL Server 2000 MASTER Database
The MASTER database can only be restored when the database is in single-user mode. To place it in single user mode:
1. Start a command session.
2. To ensure that SQL Server is not running type:
net stop MSSQLServer
This may say that this will also stop another service, in which case allow it to continue. It will also indicate if the service is already stopped.
3. To start SQL Server in single user mode type:
sqlservr -m
If this is not recognised then cd to ''C:\Program Files\Microsoft SQL Server\MSSQL\BINN'' and try again.
To restore the MASTER database from the backup use the following script (run in SQL Server Query Analyzer):
use MASTER
go
Restore Database MASTER from Disk =N''D:\Path\MASTER.BAK'' with REPLACE, recovery
go
Note: While SQL Server is in single user mode you should still be able to connect from SQL Server Query Analyzer if you connect using the "sa" account.
The above assumes that the file number is 3 for the MASTER database. If unsure on the file number then the list of file numbers can be found by issuing the command:
Restore HeaderOnly from Tape=''\\.\Tape0'' with nounload
the two fields to look at in the result set are the ''BackupName'' and ''Position'' which is the file number to be used in the above.
Once the MASTER database has been restored (the restore will have caused the database to be shutdown) start the database by issuing the following command at the command line:
net start MSSQLServer
Remember that the password for the ''sa'' account will now be what it was at the time of the original backup.

这篇关于恢复数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！