MSSQL的一些操作备份;T-SQL;SQLSERVICE;MSSQL

Linux

#--创建docker mssql
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=sn#gQZySY83@MfWM' -v $PWD/data:/var/opt/mssql/data --name sql --hostname sql --restart always -p 1433:1433 -d justin2004/mssql_server_tiny

#--进入sqlcmd
sqlcmd -S localhost -U sa

Sql

#--断开数据库连接并回滚事务
ALTER DATABASE test
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

#--删除数据库
drop database test
GO

#--备份数据库
BACKUP DATABASE test TO DISK = N'/backup/test20240708.bak' WITH NOFORMAT, NOINIT, NAME = N'test_Backup_20240708', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,STATS = 10
GO

#--查看备份文件，数据库原始名称
RESTORE HEADERONLY FROM DISK = '\backup\test.bak'
GO

#--还原数据库
RESTORE DATABASE test
FROM DISK = '/backup/K3/K320240616075550.bak'
WITH MOVE 'SqlServer_BC_20120727194252_Data' TO '/backup/test.mdf',
MOVE 'SqlServer_BC_20120727194252_Log' TO '/backup/test_Log.ldf'
GO