SQL database volume backup and Restoration

Database restoration is part of DBA's daily life. A dba may need to perform recovery for many times due to various reasons, such as recovery and refresh database for testing purposes. It may be difficult to perform recovery due to corrupted media, the disk space on the server is insufficient. In this article, I will outline one of the methods I used to restore the backup production database

Database restoration is part of DBA's daily life. A dba may need to perform recovery for many times due to various reasons, such as recovery and refresh database for testing purposes. It may be difficult to perform recovery due to corrupted media, the disk space on the server is insufficient. In this article, I will outline one of the methods I used to restore the backup production database

Solution
In the couple's days, my support team members came to me and said they could not refresh ABC's same backup copy from the production server named OLTP development environment database. The backup copy from the production server is about 75 GB. In our development machine, we only have 1 dedicated for SQL Server, which leaves free space for only 70 GB drive (D ).

Recovery Solution
After a detailed understanding, I came to the conclusion that I could not release the D drive development machine in any space. It is very important to mention that our entire development machine is in different domain comparison stages and production boxes.

I logged on to the production server and decided to split it into two equal parts using the following T-SQL database backup named ABC:

The Code is as follows:
BACKUP DATABASE ABC To disk = 'B: DB BackuABC_1.bak ', DISK = 'B: DB BackupsABC_2.bak' GO

Once the preceding T-SQL statement block is executed, it is split into two equal parts and named as the complete backup of the database currently on the production server of ABC. For example, if the database size is 76 GB, it will be divided into two equal parts, each of which is 36 GB.

Once split into two equal copies of the complete backup, then I can execute the RAR for them. Obviously, the first RAR to be executed, and then moving them to different servers will be faster than moving a larger original. Once the split file is compressed successfully, I stepped onto my temporary server. This is because the temporary box is good. In terms of resources, it is also because the production and temporary Server replication processes are in different data centers, because good hardware works faster. As discussed earlier, the free space development OLTP only has 70 GB, And the backup copy is 75 GB. Therefore, it is impossible to transmit a full backup compressed copy in the Development OLTP. I have another box, which is used as an SSIS development server with a lot of free space. They are as follows:

Drive D has 49.9 GB space available

Drive C has 55 GB space available

The development machine is an independent machine, rather than a cluster. Therefore, there is no problem in copying 1 backup of compressed files to the C drive.

Then, I stepped onto the development SSIS server and named Backup_03102011_DB. Currently, I want to compress a copy of the file to the name backup folder of drive C of the d drive to other compressed files. I grant all permissions to these two folders.

The Code is as follows:
RESTORE DATABASE ABC From disk = '\ 10. A. AbackupsABC_1.bak ', DISK = '\ 10. A. abackup_0310201511dbabc_2.bak' With move 'abc _ data' TO 'd: Program FilesMicrosoft SQLServerMSSQL.1MSSQLDataABC _ Data_1.mdf ', MOVE 'abc _ log' TO 'd: Program FilesMicrosoft SQLServerMSSQL.1MSSQLDataABC _ Log. ldf' GO

Where 10. AAA is the IP address of the dev machine.

Once the preceding T-SQL code block is successfully executed, I changed the owner of the SA database for the database named ABC to execute the following query.

The Code is as follows:
Exec sp_changedbowner 'sa'

The next step involves ing isolated users. To find isolated users, You need to execute the T-SQL query under the OLTP machine developed on the ABC database.

The Code is as follows:
Sp_change_users_login @ Action = 'report'

Once the preceding query is executed, it lists all databases with the isolated username ABC. To solve this problem, execute the following T-SQL query:

The Code is as follows:
Exec sp_change_users_login @ Action = 'Update _ one', @ UserNamePattern = 'user name', @ LoginName = 'login name ';