topic Re: SQL 2012 restore in Application Integration

SQL 2012 restore

p-wu57 — Mon, 24 Mar 2014 12:41:37 GMT

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;

test1

create database recoveryDB

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;

test2

CREATE DATABASE RecoveryDB

(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 — Mon, 24 Mar 2014 15:49:48 GMT

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

p-wu57 — Tue, 25 Mar 2014 05:39:18 GMT

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

p-wu57 — Wed, 26 Mar 2014 07:27:20 GMT

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_tan2 — Tue, 01 Apr 2014 22:58:00 GMT

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

edness119 — Fri, 04 Jul 2014 09:51:52 GMT

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

franksjoness6 — Thu, 27 Aug 2015 10:09:17 GMT

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

rodickwillision52 — Fri, 12 Feb 2016 06:27:17 GMT

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

billyharrington59 — Mon, 04 Jul 2016 06:32:39 GMT

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

matthewrobbin37 — Thu, 11 Aug 2016 05:16:22 GMT

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.