9 Replies Latest reply: Aug 10, 2016 10:16 PM by Matthe wrobbin RSS

    SQL 2012 restore

    Peggy Wu Wayfarer

      Hi,

      I am doing restore SQL DB, following the bpg for Microsoft SQL server.Best Practices for Microsoft SQL Server

      I am stocking at object-level restoration, describing in page10.

       

      test1

      create database recoveryDB

      on

      name recoveryDB  filename

      'X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DB.mdf'

      name recoveryDB_log filename

      'Y:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DB_log.ldf'

      for attach;

      failed to execute it, because of there is wrong arround 'name'

       

      test2

      CREATE DATABASE RecoveryDB

          ON

        (FILENAME = 'X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DB.mdf'),

        (FILENAME = 'Y:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DB_log.ldf')

          FOR ATTACH;

      give up test1, I tried test2, but still fail!!

      error message:

      FixupLogTail .....Y:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\DB_log.ldf is not able to be written.

       

      does anyone successfully to do restore follow the bpg?

      Please someone advise me where I made mistake.

       

      OS: Windows 2008 R2

      SQL 2012

      NOS 1.4.11

      NPM 1.4.62.2

        • Re: SQL 2012 restore
          Nick Dyer Navigator

          Hello Peggu, welcome to Nimble Connect.

           

          Are you presenting the SQL log and database volumes directly from snapshots (ie snapshot "online" then mount), or are you using the zero-copy clone method (select snapshot, clone to new volume, mount to server)?

           

          It looks like from your error the SQL log cannot be written to. To me this may indicate that the volume is in a read-only state. Nimble snapshots by default are only ever read-only (rather than writable) if taken as part of a schedule, in order to make sure the backup is not changed or comprimised. Therefore the best practice is to clone the snapshots to new volumes and mount those, as they will have full read-write permissions. Any changes made to these clones will not effect the parent snapshot.

           

          Can you try that and see if that fixes the error?

            • Re: SQL 2012 restore
              Peggy Wu Wayfarer

              Hi Nick,

              I am using zero-copy clone.

              I created a volume collection for db and log volumes of SQL 2012. The snapshots take by volume collection are all writable by default.

               

              I also tried online the snapshot directly. however, even the snapshot is writable, it still can not be attached to SQL as a recoveryDB.

              I am still trying to identify the problem. here is my test environment. any idea will be welcome.

               

              / $ vol --list

              sqldb                    204800 Yes    N/A            143       0.00     100.00

              sqldb-clone              204800 Yes    N/A              0       0.00     100.00

              sqlLogs                  204800 Yes    N/A            131       0.00     100.00

              sqlLogs-clone            204800 Yes    N/A              0       0.00     100.00

               

              / $ vol --info sqlLogs-clone

              Name: sqlLogs-clone

              Serial number: cd99959e9b8bb9b56c9ce9003d4700a6

              iSCSI target: iqn.2007-11.com.nimblestorage:sqllogs-clone-v07309110a130d62d.00000127.a600473d

              Description:

              Owned by: nimble-s

              Size (MB): 204800

              Performance policy: SQL Server Logs

              Block size (bytes): 4096

              Reserve: 0.00%

              Warn level: 80.00%

              Quota: 100.00%

              Snapshot reserve: 0.00%

              Snapshot warn level: N/A

              Snapshot quota: unlimited

              Volume usage (MB): 0

              Volume compression: 8.00X

              Volume space saved (MB): 0

              Snapshot usage (MB): 0

              Snapshot compression: N/A

              Snapshot space reduction: N/A

              Snapshot space saved (MB): 0

              Read only: No

              Multi-initiator: No

              Online: Yes

              Offline reason: N/A

              Clone: Yes

              Parent volume: sqlLogs

              Base snapshot: SQLDB-five-2014-03-24::17:00:21.916

              Volume collection: none

              Number of connections: 2

              Created: Mar 24 2014 21:13:15

              Last configuration change: Mar 24 2014 21:13:15

              Access Control List:

                      Apply to: volume & snapshot

                      Initiator Group: sql

                      CHAP user: *

              Connected Initiators:

              Initiator:iqn.1991-05.com.microsoft:w2k8r2-sql

                      PR Key:139689803850706

                      Connection Count:1

                      Initiator IP Addr:10.110.10.147

                      Target IP Addr:10.110.10.22

              Initiator:iqn.1991-05.com.microsoft:w2k8r2-sql

                      PR Key:139689803850706

                      Connection Count:1

                      Initiator IP Addr:10.110.20.147

                      Target IP Addr:10.110.20.22

               

               

              / $ snap --info SQLDB-five-2014-03-24::17:00:21.916 --vol sqlLogs

              Name: SQLDB-five-2014-03-24::17:00:21.916

              Volume name: sqlLogs

              Serial number: 606b0c22495d709a6c9ce9003d4700a6

              iSCSI target: iqn.2007-11.com.nimblestorage:sqllogs-sqldb-five-2014-03-24::17:00:21.916-v07309110a130d62d.00000121.a600473d.s07309110a130d62d.00000139.0000acc9

              Description:

              Size (MB): 204800

              Status: Okay

              Allow writes: Yes

              Online: No

              Offline reason: User

              New data (MB): 132

              New data compression: 1.97X

              Number of connections: 0

              Schedule: five

              Origination group name: nimble-s

              Is replica: No

              Replication status: N/A

              Created: Mar 24 2014 17:00:21

              Last configuration change: Mar 24 2014 17:00:21

              Initiators connected:

              • Re: SQL 2012 restore
                Peggy Wu Wayfarer

                Hi,

                 

                I found a SQL recovery step by step.

                Nimble Storage Demo: SQL Server Recovery - YouTube

                 

                I created clone volumes from snapshots, same as the video.

                The clone volumes are read only, in fact. User has to modify the attribute via diskpart command.

                And then I attached clone volumes as recovery DB successfully.

                  • Re: SQL 2012 restore
                    David Tan Adventurer

                    Actually the steps involved with the disk presentation change depending on if it's a snapshot vs clone, if it's presented to same or different host and also the windows operating system. I have even found that the order of clearing the hidden and readonly attributes can affect results. Could be related to windows hot fixes as well :)

                • Re: SQL 2012 restore
                  edward schauer Newbie

                  Hello!

                   

                  You can try executing Dbcc checkdb and dbrepair statements in SQL Server Management Studio. This statements restores SQL database which are less corrupted. But if this doesn't works, you need to use more effective solution. Its very easy to get the solutions as many are available in the internet but you need to choose the right one. You can get one such solution from here: http://repairsqldatabase.wordpress.com

                   

                  Read the above post and try this solution, it will solve your problem as it a recommended solution.

                   

                  Thanks!

                  • Re: SQL 2012 restore
                    franks joness Newbie

                    You can run the DBCC CHECKDB utility to repair the damaged and corrupt SQL database files. However, the DBCC CHECKDB is not always able to repair the damaged and corrupt SQL database files.In case you are not able to repair the damaged and corrupt SQL database files using any of the above mentioned methods, then you can use a professional and proficient third party Sql database recovery software which helps to get back all the SQL components like Stored Procedures, Triggers, Primary Keys, Unique Keys, Foreign Keys, Indexes, Tables, Views and Predefined Defaults. After trying the free demo version, if you are satisfied with its ability, it’s a perfect time to purchase the licensed version for here: SQL Database Recovery tool to Repair Corrupt SQL Server .MDF Files

                      • Re: SQL 2012 restore
                        rodick willision Newbie

                        To repair corrupt, damaged MS SQL Server database, I would like to refer more trusted SQL recovery software, use Kernel for SQL Database Recovery Tool. The software recovers lost data, tables, views, stored procedures, rules, defaults, user defined data types and triggers from corrupt MDF database. It supports all version of MS SQL servers such as 2000, 2005, 2008, 2008 R2, 2012 and 2014. Visit here - http://www.sqlrecoverytool.mdfrecovery.org/

                          • Re: SQL 2012 restore
                            Billy Harrington Newbie

                            Relevant solution to recover damaged SQL MDF & NDF file and doesn’t make any kind of changes to SQL database, while repairing them it also recover all components like tables, functions, stored procedures, views, constraints, triggers and more.This corrupted due to several reasons like accidental deletion of data, Virus attack, invalid database file header, insufficient available space, corruption of media, driver and partition inaccessibility etc.Use SQL Database Recovery software which can easily repair all damaged MDF files of SQL Server database including triggers, tables, keys, procedures, indexes etc within few clicks.It can automatically repair MDF files in MS SQL Server 2012, 2008 R2, 2008, 2005, and 2000.

                             

                            To download visit: -  http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html

                        • Re: SQL 2012 restore
                          Matthe wrobbin Newbie

                            SQL Database Recovery Software to Rebuild Corrupt or Inaccessible MDF Database Files designed for such purposes like to fix severely corrupted database files (MDF) of SQL Server. It thoroughly scans damaged MDF file within a click and displays all the recovered tables including objects such as views, triggers, etc.