本文所有操作基于文档AdventureWorks sample databases。整个过程比较顺利。
本文的前提是已经有了一个SQL Server数据库，如果你没有，可以参考文档：如何快速获得一个测试用SQL Server企业版。
下载示例数据库，并移动到mssql用户有权访问的目录：
$ wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak
$ ls -l
total 203896
-rw-rw-r--. 1 opc opc 208789504 May 8 2023 AdventureWorks2019.bak
$ sudo mkdir -p /var/opt/mssql/backup
$ sudo mv AdventureWorks2019.bak /var/opt/mssql/backup
编写恢复数据库脚本，你可以按操作系统和数据库版本进行修改：
$ cat sampledb.sql
USE master;
GO
RESTORE DATABASE AdventureWorks2019
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2019.bak'
WITH
MOVE 'AdventureWorks2019' TO '/var/opt/mssql/data/AdventureWorks2019_Data.mdf',
MOVE 'AdventureWorks2019_log' TO '/var/opt/mssql/data/AdventureWorks2019_log.ldf',
FILE = 1,
NOUNLOAD,
STATS = 5;
GO
运行脚本，恢复示例数据库：
$ sqlcmd -S localhost -No -U sa -i sampledb.sql
Password:
Changed database context to 'master'.
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 25296 pages for database 'AdventureWorks2019', file 'AdventureWorks2019' on file 1.
Processed 2 pages for database 'AdventureWorks2019', file 'AdventureWorks2019_log' on file 1.
RESTORE DATABASE successfully processed 25298 pages in 7.403 seconds (26.696 MB/sec).
查看示例数据库中的表：
1> SELECT name, database_id, create_date
2> FROM sys.databases;
3> go
name database_id create_date
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------------------
master 1 2003-04-08 09:13:36.390
tempdb 2 2024-08-01 01:15:54.893
model 3 2003-04-08 09:13:36.390
msdb 4 2024-06-10 18:24:52.523
AdventureWorks2019 5 2024-08-01 01:58:50.600
(5 rows affected)
1> use AdventureWorks2019
2> go
Changed database context to 'AdventureWorks2019'.
1> select table_name from information_schema.tables where table_type = 'BASE TABLE';
2> go
table_name
--------------------------------------------------------------------------------------------------------------------------------
SalesTaxRate
PersonCreditCard
PersonPhone
SalesTerritory
PhoneNumberType
Product
SalesTerritoryHistory
ScrapReason
Shift
ProductCategory
ShipMethod
ProductCostHistory
ProductDescription
ShoppingCartItem
ProductDocument
DatabaseLog
ProductInventory
SpecialOffer
ErrorLog
ProductListPriceHistory
Address
SpecialOfferProduct
ProductModel
AddressType
StateProvince
ProductModelIllustration
AWBuildVersion
ProductModelProductDescriptionCulture
BillOfMaterials
Store
ProductPhoto
ProductProductPhoto
TransactionHistory
ProductReview
BusinessEntity
TransactionHistoryArchive
ProductSubcategory
BusinessEntityAddress
ProductVendor
BusinessEntityContact
UnitMeasure
Vendor
ContactType
CountryRegionCurrency
CountryRegion
WorkOrder
PurchaseOrderDetail
CreditCard
Culture
WorkOrderRouting
Currency
PurchaseOrderHeader
CurrencyRate
Customer
Department
Document
SalesOrderDetail
EmailAddress
Employee
SalesOrderHeader
EmployeeDepartmentHistory
EmployeePayHistory
SalesOrderHeaderSalesReason
SalesPerson
Illustration
JobCandidate
Location
Password
SalesPersonQuotaHistory
Person
SalesReason
(71 rows affected)
1> select TABLE_SCHEMA from information_schema.tables where table_type = 'BASE TABLE' and table_name = 'Employee';
2> go
TABLE_SCHEMA
--------------------------------------------------------------------------------------------------------------------------------
HumanResources
(1 rows affected)
1> select count(*) from HumanResources.employee;
2> go
-----------
290
(1 rows affected)
1> select top 5 * from HumanResources.employee;
2> go
BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- -------------------------------------------------- ---------------- ------------- ------ ---------------- ------------ ------------- -------------- ----------- ------------------------------------ -----------------------
1 295847284 adventure-works\ken0 NULL NULL Chief Executive Officer 1969-01-29 S M 2009-01-14 1 99 69 1 F01251E5-96A3-448D-981E-0F99D789110D 2014-06-30 00:00:00.000
2 245797967 adventure-works\terri0 0x58 1 Vice President of Engineering 1971-08-01 S F 2008-01-31 1 1 20 1 45E8F437-670D-4409-93CB-F9424A40D6EE 2014-06-30 00:00:00.000
3 509647174 adventure-works\roberto0 0x5AC0 2 Engineering Manager 1974-11-12 M M 2007-11-11 1 2 21 1 9BBBFB2C-EFBB-4217-9AB7-F97689328841 2014-06-30 00:00:00.000
4 112457891 adventure-works\rob0 0x5AD6 3 Senior Tool Designer 1974-12-23 S M 2007-12-05 0 48 80 1 59747955-87B8-443F-8ED4-F8AD3AFDF3A9 2014-06-30 00:00:00.000
5 695256908 adventure-works\gail0 0x5ADA 3 Design Engineer 1952-09-27 M F 2008-01-06 1 5 22 1 EC84AE09-F9B8-4A15-B4A9-6CCBAB919B08 2014-06-30 00:00:00.000
(5 rows affected)
参考
- https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=tsql
- https://github.com/microsoft/sql-server-samples
-
- https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-run-transact-sql-script-files?view=sql-server-ver16
扫一扫
342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
