Two ways to Centos7.3 SQL Server backup and restore
Two of our previous articles described the installation configuration of SQL Server under Centos7.3 and the management of using PowerShell, and today we go on to implement SQL Server backup and restore under Centos7.3 in two ways: 1. Using SSMs for backup and restore , this mode is the simplest and most convenient way to operate, 2, the use of Linux under the SQL Server features command backup, specifically see below:
We created a test database in the previous article, and we went on to test the database, we first used the first way to connect the backup with SSMs;
We first use SSMS to connect to the database, then right-click the database---tasks---back up
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9TyygXYq8AAJc91eRepc296.png" height= "472"/>
We can see the default backup path: The default backup path is/var/opt/mssql/data/ ; The database format is the same as on Windows. bak format;
Click--ok to start the backup
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/52/wKioL1i9TzHzWXg-AAGwS-MZwDQ791.png" height= "467"/>
Backup complete
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9TzLAvJfRAAG9oXHmxTc802.png" height= "465"/>
We view backup files
cd/var/opt/mssql/data/
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9TzOjzpqAAAF6K4vttiQ147.png" height= "269"/>
Next we look at the default data and then try to restore it;
We operate via PowerShell, and by default there are two data
SELECT * FROM Xllinfo;go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9TzSCfNvyAABRbPBFJOM187.png" height= "202"/>
Next we insert a piece of data
INSERT into Xllinfo values (3, ' Gavin ', +); go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/52/wKioL1i9TzXxa9NEAAB9skeRPzs489.png" height= "182"/>
Next we'll take a look through SSMs
SELECT * FROM Xllinfo
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/52/wKioL1i9TzbTay1CAAERoJw2uzg068.png" height= "352"/>
Next we try to use SSMS to restore it with just a backup
Right-click Database-session-Restore--database
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/54/wKiom1i9TziBRK-uAAIQQUMoM-Y045.png" height= "484"/>
We can see the default source, but we don't use this way
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9TzrxLkNVAAGK8uFr1Zo844.png" height= "410"/>
We choose device---Browse
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/52/wKioL1i9Tz7SMrAmAAFR0eA7js8025.png" height= "406"/>
Select Add File
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T0XwZTapAAE9CnQ3Z8k873.png" height= "406"/>
Select backup files to back up the path
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/54/wKiom1i9T0bCpFe9AAF05v7n9h4980.png" height= "419"/>
Click Confirm
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9T0iyQoGYAAE8e-qA0z0540.png" height= "399"/>
If you confirm the information, you can click Restore
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T1jgPf2OAAGNbsVeMYU158.png" height= "394"/>
We restore the hint that the database is in use, so we need to stop accessing the database process
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/54/wKiom1i9T2HxYjFoAAG5BkzeQAs518.png" height= "405"/>
We use scripts
We execute it under the system database.
ALTER DATABASE [Xll]set OFFLINE with ROLLBACK IMMEDIATE
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/52/wKioL1i9T2Ti7P2ZAAG2RaHLOSE029.png" height= "432"/>
Next we try to restore again, but we need to tick--the option--Overwrite the existing database
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9T2eCeWlVAAHzqVLiCTI746.png" height= "399"/>
Restore succeeded
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9T2miT-ymAAFvLVJCiLE404.png" height= "407"/>
Next we look at the data, after we restore, we found that a new piece of data is gone;
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T2qCUEcvAAIAZ51jJLo938.png" height= "/>"
Next we use the second method to do the backup, using the backup under Linux
We need to re-insert a few data before we backup, because the above restoration has already overwritten the data that was previously inserted; we use SQLCMD to insert data
Sqlcmd–s localhost–u SA Enter password
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/54/wKiom1i9T2uAexIFAAAttsFjjdo651.png" height= "/>"
Use the XLL database, and then query the default data
Use Xll;select * from Xxlinfo;go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T2uRPTLHAABZg_JZbKQ346.png" height= "171"/>
Next we insert the data
INSERT into Xllinfo values (4, ' User01 ', +), insert into Xllinfo values (4, ' user02 ', +); insert into xllinfo values (4, ' User0 3 ', +); go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/52/wKioL1i9T2zgKRJQAACqZr56SlE962.png" height= "297"/>
The next step is to back up through sqlcmd
Sqlcmd-h localhost-u sa-q "BACKUP DATABASE [xll] to DISK = N '/var/opt/mssql/data/xlldb01.bak ' with Noformat, Noinit, NA ME = ' Xll-full ', SKIP, Norewind, nounload, STATS = 10 "
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T3ChXyY3AAECDG5qy8M442.png "height=" 209 "/>
We'll look at the backup file next.
cd/var/opt/mssql/data/
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://s3.51cto.com/wyfs02/M02/8E/54/wKiom1i9T3HCWXjyAAECDG5qy8M286.png "height=" 209 "/>
Next we back up log
Sqlcmd-h localhost-u sa-q "BACKUP LOG [xll] to DISK = N '/var/opt/mssql/data/xlldb01_logbackup_2017-03-04_12-10-10.bak ' With Noformat, noinit, NAME = N ' xlldb01_logbackup_2017-03-04_12-10-10 ', Noskip, Norewind, Nounload, NORECOVERY, STATS = 5 "
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/52/wKioL1i9T3Kj5cY-AABi49343ic373.png" height= "/>"
Next we delete the data, then we look at it,
Use Xll;select * from Xllinfo;go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/54/wKiom1i9T3Pjspc8AABZxOOSpNY205.png" height= "191"/>
Then we delete the data
Delete xllinfo where name = ' ls ';d elete xllinfo where name = ' ZS '; go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T3Pg2w8mAABWJjbVBt0179.png" height= "142"/>
We look again
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/54/wKiom1i9T3SQ_ZLYAABJXKAIo0g062.png" height= "144"/>
Next we have to restore;
You are prompted that the database is in use and cannot be restored
Sqlcmd-h localhost-u sa-q "RESTORE DATABASE [xll] from DISK = N '/var/opt/mssql/data/xlldb01.bak ' with FILE = 1, Nounloa D, REPLACE, STATS = 5 "
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/52/wKioL1i9T3XQigDNAABa9gYm3E0134.png" height= "Up"/>
So we need to port all sessions connected to the database to
We need to do a little bit of work
ALTER DATABASE [Xll]set single_user with ROLLBACK immediatego or ALTER DATABASE [Xll]set OFFLINE with ROLLBACK Immediatego
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/54/wKiom1i9T3mzQ8D7AACRlB8JOZ8997.png" height= "/>"
Both of these methods can be executed; next we try to restore
Sqlcmd-h localhost-u sa-q "RESTORE DATABASE [xll] from DISK = N '/var/opt/mssql/data/xlldb01.bak ' with FILE = 1, Nounloa D, REPLACE, STATS = 5 "
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/52/wKioL1i9T3nDPHJdAADDoPa_dHA576.png" height= "217"/>
We last Viewed
Use Xll;select * from Xllinfo;go
650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://s3.51cto.com/wyfs02/M00/8E/52/wKioL1i9T3qCziiFAABpTlBjKDg754.png "height=" 207 "/>
This article from "Gao Wenrong" blog, declined reprint!
Two ways to Centos7.3 SQL Server backup and restore