亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

數據庫分卷備份和還原

系統 1981 0

from: http://www.sql-server-performance.com/2011/restoring-sql-server-database-low-disk-space/

Performing a Database Restore is a part of a DBA’s daily life. A DBA may need to perform a Restore due to various reasons such as recovery, refreshing a database for testing purpose etc. Many times it can be difficult to perform a Restore due to corrupted media, low disk space on the server and so on. In this article, I will outline one approach which I used to Restore the backup of the Production Database a test environment.

Scenario

A couple of days back, members of my support team approached me saying that they are unable to refresh a database named ABC on the OLTP development environment with the copy of the backup of the same database from the Production server. The backup copy taken from the Production server was around 75 GB in size. On our dev machine we were had just 1 dedicated drive(D) for SQL Server which was having just 70 GB of free space left on it.

Restore Solution

After having a detailed look, I came to a conclusion that I could not free up any space on the D drive of the dev machine. One important point to mention is that our entire dev machines were in a different domain than the staging and production boxes.

I logged on to the Production Server and decided to split the backup of the database named ABC into two equal parts using the T-SQL as shown below:

                
                  BACKUP
                
                
                  DATABASE
                
                 ABC
                
TO DISK = ' B:\DB Backups\ABC_1.bak ' ,
DISK = ' B:\DB Backups\ABC_2.bak '
GO
              

Once the above block of T-SQL statement is executed, it splits the Full Backup of the database named ABC present on the Production server into two equal parts. For example, if the size of a database is 76 GB then it will be divided into two equal parts each of size 36 GB.

Once the full backup gets split into two equal parts, I can then perform the RAR on them. Obviously first performing the RAR and then moving them to the different server would be much faster than moving the original copy of a much bigger size. Once the split files were zipped successfully I then moved them onto my staging server. This is because the staging box was pretty good in terms of resources and also since both the Production and Staging servers were in different Data Centers, due to good hardware the copying process worked much faster. As discussed earlier the free space available on the dev OLTP box was just 70 GB whereas the backup copy was 75 GB therefore it was not possible to transfer the zipped copies of the full backup to the dev OLTP box. I was having another box which was used as a SSIS Dev server with large amounts of free space. They are as follows:

Drive D had 49.9 GB free space

Drive C had 55 GB free space

The dev machines were standalone machines and not a cluster, therefore there were no issues for in copying 1 zipped file of the backup onto the C drive.

I then moved one copy of the zipped file onto the folder named? backups ?which was on the C drive of the dev SSIS server and the other zipped file onto the folder named? Backup_03102011_DB? present on the D drive. I gave Full permissions on both these folders.

I then unzipped the two backup files on the dev SSIS server. Once done, I then logged on to the Dev OLTP machine and against the database named ABC I executed the following T-SQL statement to restore the database.

                          
                            RESTORE
                          
                          
                            DATABASE
                          
                           ABC
                          
FROM DISK = ' \\10.A.A.A\backups\ABC_1.bak ' ,
DISK = ' \\10.A.A.A\Backup_03102011_DB\ABC_2.bak '
WITH MOVE ' ABC_Data ' TO ' D:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ABC_Data_1.mdf ' ,
MOVE ' ABC_Log ' TO ' D:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ABC_Log.ldf '
GO
                        

Where 10.A.A.A is the IP Address of the dev machine.

Once the above block of T-SQL code was executed successfully, I then changed the owner of the database to sa by executing the following query against the database named ABC.

                          
                            Exec
                          
                           sp_changedbowner ‘sa’
                        
                        

The next step involved mapping the Orphaned users. In order to find the Orphaned users, you need to execute the below T-SQL query against the ABC database on dev OLTP machine

                          sp_change_users_login 
                          
                            @Action
                          
                          
                            =
                          
                          
                            '
                          
                          
                            Report
                          
                          
                            '
                          
                        


Once the above query is executed, it will list all the Orphaned users present in the database named ABC. In order to fix this you will need to execute the below T-SQL query:

                          
                            exec
                          
                           sp_change_users_login 
                          
                            @Action
                          
                          
                            =
                          
                          
                            '
                          
                          
                            update_one
                          
                          
                            '
                          
                          , 
                          
                            @UserNamePattern
                          
                          
                            =
                          
                          
                            '
                          
                          
                            User Name
                          
                          
                            '
                          
                          , 
                          
                            @LoginName
                          
                          
                            =
                          
                          
                            '
                          
                          
                            Login Name
                          
                          
                            '
                          
                          ;
                        


I hope you all have enjoyed reading this article. Any suggestions or comments on this process would be most welcome.

數據庫分卷備份和還原


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 国产成人综合高清在线观看 | 五月国产综合视频在线观看 | 日韩欧美理论片 | 久久综合给合久久狠狠狠色97 | 91视频老司机 | 久久99热精品免费观看无卡顿 | 99久久亚洲国产高清观看 | 国产欧美成人xxx视频 | 亚洲黄色自拍 | 老头老太做爰xxx视频 | 97视频在线观看免费播放 | 亚洲精品日韩中文字幕久久久 | 成年女人永久免费观看片 | 精品国产一区二区三区2021 | 久操视频在线观看 | 免费看aa | 久久永久视频 | 全黄大全大色全免费大片 | 亚洲国产精品67194成人 | 欧美日韩国产中文字幕 | 亚洲综合站| 国产日韩久久久久69影院 | 日韩在线中文字幕 | 999精品视频这里只有精品 | 色综合在 | 久久伊人久久亚洲综合 | 奇米777狠狠色噜噜狠狠狠 | 精品哟啊呦v视频在线观看 精品哟哟国产在线观看 | 日韩精品一区二区三区四区 | 中文字幕中文字幕在线 | 亚洲精品一区二区三区不卡 | 精品国产97在线观看 | 国产乱码精品一区二区三区卡 | 全部免费特黄特色大片中国 | 日韩在线观看网站 | 国产一区二区三区在线影院 | 青青99| 伊人色色网| 国产综合网站 | 亚洲美女视频在线观看 | 久久成人免费播放网站 |