在mac上恢复windows上备份的SQLServer数据库

最新推荐文章于 2024-08-19 22:30:00 发布

Andy__Han

最新推荐文章于 2024-08-19 22:30:00 发布

阅读量1.5k

点赞数

本文链接：https://blog.csdn.net/hxg117/article/details/82899318

版权

数据库备份文件为～/data/sqlserver/secureback.bak

Mac上docker运行sqlserver：

docker run -it -e SA_PASSWORD=Active#123 -e SQLSERVER_DATABASE=test -e SQLSERVER_USER=test -e SQLSERVER_PASSWORD=test -p 1433:1433  --name sqlserver -v ~/data/sqlserver:/opt/backup exoplatform/sqlserver

将mac本机目录~/data/sqlserver/，映射到docker。

docker上sqlserver启动后，链接数据库，执行下面命令恢复数据库：

RESTORE database secure FROM disk='/opt/backup/secureback.bak'
WITH MOVE 'ams_site_b' TO '/opt/data/ams_site_b.mdf', 
  MOVE 'ams_site_b_log' TO '/opt/data/ams_site_b.ldf'

数据库逻辑名为ams_site_b。如果不加WITH MOVE会报错：

Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\secure.mdf" failed with the operating system error 2(The system cannot find the file specified.).

备份成功后，恢复用户账号：

USE secure
--DROP USER secure
CREATE LOGIN secure WITH PASSWORD='secure', CHECK_POLICY=OFF, DEFAULT_DATABASE=secure
CREATE USER secure FOR login secure with default_schema=dbo
exec sp_addrolemember 'db_owner', 'secure'
alter login secure enable
GRANT ALL ON DATABASE::secure TO secure

完。