Application Integration
1748204 Members
3645 Online
108759 Solutions
New Discussion

SQL 2012 restore

 
SOLVED
Go to solution
p-wu57
Advisor

SQL 2012 restore

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

9 REPLIES 9
Nick_Dyer
Honored Contributor

Re: SQL 2012 restore

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?

Nick Dyer
twitter: @nick_dyer_
p-wu57
Advisor

Re: SQL 2012 restore

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:

p-wu57
Advisor
Solution

Re: SQL 2012 restore

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.

david_tan2
Valued Contributor

Re: SQL 2012 restore

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 :)

edness119
New Member

Re: SQL 2012 restore

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!

franksjoness6
New Member

Re: SQL 2012 restore

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

rodickwillision52
New Member

Re: SQL 2012 restore

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/

billyharrington59
New Member

Re: SQL 2012 restore

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

matthewrobbin37
New Member

Re: SQL 2012 restore

  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.