SQL Server Remote Restore

1, fake device files Xxxx.bak size of about 300G, the space occupied after restoration is 900G

2, disk space only 1 T, if the backup files copied, the space remaining 700G, can not be restored successfully, so the remote way to restore.

Examples are as follows:

SQL Server Service Instance name: 192.168.12.163
Name of database to be backed up: a
Backup machine name (client side): 192.168.12.30
Backup machine User: Administrator password: 123
Backup machine domain name: b28-1230
folder where the backup machine provides backup requirements: D:\backup

Backing Up the database
First step: Create a shared folder on the backup machine
Call in program code (or CMD window)
NET share sqlbakup=d:\backup or with Netshareadd this API
Brief description:
NET share: is a network command inside windows.
Role: Establish a local shared resource that displays the shared resource information for the current computer.
Step two: Establish a shared credit relationship
EXEC master. xp_cmdshell ' net use \\192.168.12.30\backup 123/user:b28-1230\administrator '

If the error indicates this: "SQL Server has blocked access to the process ' Sys.xp_cmdshell ' of component ' xp_cmdshell ' because this component has been shut down as part of this server's security configuration."
System administrators can enable ' xp_cmdshell ' by using sp_configure. For more information about enabling ' xp_cmdshell ', see "surface Area Configurator" in SQL Server Books Online. ”

Enable ' xp_cmdshell ':
Use master
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE with OVERRIDE
EXEC sp_configure ' xp_cmdshell ', 1
RECONFIGURE with OVERRIDE
EXEC sp_configure ' show advanced options ', 0
RECONFIGURE with OVERRIDE

Close ' xp_cmdshell ':
Use master
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE with OVERRIDE
EXEC sp_configure ' xp_cmdshell ', 0
RECONFIGURE with OVERRIDE
EXEC sp_configure ' show advanced options ', 0
RECONFIGURE with OVERRIDE

After opening ' xp_cmdshell ', then proceed to establish credit relationship:
EXEC master. xp_cmdshell ' net use \\192.168.12.30\backup 123/user:b28-1230\administrator '

Restore Backup Library A:
Restore Database peking from disk = ' \\192.168.12.30\backup\a.bak '
With move ' a_data ' to ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\a.mdf ',
Move ' a_log ' to ' C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\a_log.ldf '

Use the SQL Server restore FILELISTONLY command to view the logical file name
Restore filelistonly from disk= ' \\192.168.12.30\backup\a.bak '

Restore with Move usage:
Restore Database Northwind from disk = ' C:\Northwind.bak '
With move ' Northwind ' to ' D:\microsoft SQL Server\mssql.1\mssql\data\northwind.mdf '
, move ' northwind_log ' to ' D:\microsoft SQL Server\mssql.1\mssql\data\northwind.ldf '


Brief description:
1:xp_cmdshell: is an extended stored procedure for SQL Server.
Function: Executes the given command string as an operating system command-line interpreter and returns any output as a text line.
Syntax: See SQL Server online Help

2:net use: is a network command inside windows.
Function: Connect or disconnect a computer from a shared resource, or display information about a computer connection.
This command also controls persistent network connections.

3: Backing Up the database
Backup database A to disk= ' \\192.168.12.30\sqlbak\a.bak '
Backup database A to disk= ' \\192.168.12.30\sqlbak\a1.bak ' with differential (differential backup)

4: Delete shared folders
Call in program code (or CMD window) net share Sqlbakup/delete
Or use the Netsharedel API
Recovering a Database
Restore database A from disk= ' \\192.168.12.30\backup\a.bak '

The following is a differential restore
Restore Database SCS from disk= ' \\192.168.12.30\backup\a.bak ' with NORECOVERY
Restore Database SCS from disk= ' \\192.168.12.30\backup\a1.bak ' with NORECOVERY
Restore Database SCS from disk= ' \\192.168.12.30\backup\a2.bak '

SQL Server Remote Restore