Two ways to Centos7.3 SQL Server backup and restore

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