Feed: SQL Recovery | SQL Server Recovery | SQL Database Recovery - AggScore: 40.2
Summary: SQL Server Recovery | SQL Recovery | SQL Database Recovery
SQL server recovery software repairs and restores damaged or corrupt SQL server data files and objects
How to Transfer Records from One Table to another in SQL Server
Several times, database administrators have
needed to transfer records from one table to another table in
same server, not only from one table to another in same server
but also from one server to another server. We will discuss it
throughout the article…
There are so many methods available for transferring records or data from one table to another, some of the methods are given below:
- INSERT INTO Method
- DTS Import/Export Wizard Method
- BCP/Bulk Insert Method
INSERT INTO Method: As name suggest, use INSERT INTO transaction SQL statement to transfer records from one table to another. It is the easiest method to transfer records. This method works with within server and between the servers. Suppose you want to transfer records from Table A to Table B then the INSERT INTO SQL statement looks like:
INSERT INTO TABLE B SELECT * FROM TABLE A
Where Table A is the original table and table B is copied table.
DTS Import/Export Wizard Method: If you do not want to write transaction SQL statement then you have another method that is DTS Import/Export wizard method. It is a graphical user interface based method. You can start your DTS import/export wizard by opening of enterprise manage. You can perform this by follow these steps:
click on "Tools" menu, place mouse on "Data Transformation Services" item in the drop down, and then after click on either "Import" or "Export Data.
Date Published: May 29, 2012 - 11:05 pm
Easy Way to Change the Location of SQL Server Datafiles
Have you decided to change the location of your SQL server
datafiles from default location to your specified location? If
yes then I will guide to you for this throughout the article.
Before going to change the location of your SQL server data
files (mdf & ndf), you will have to check the current
location of your data files. To do this run below query:
Select Name, Physical
Name
From
sys.master-files
Where Name
= “my-database-name”
Where “my-database-name” is the name of your database.
Output of query:
C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLdatamysql-database-name.mdf
C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLdatamysql-database-name2.mdf
C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLdatamysql-database-name3.mdf
The output of query shows that you database’s data files are
stored in the C drive of your computer.
Prerequisites:
Before going to change the location of your data files, you
have to put your database in office mode. You can do this by
“ALTER” command:
ALTER Database
my-database-name
SET Offline
Go
To change location of Data Files:
You have to run “Alter” command again to change the location of
data files. The queries are:
ALTER Database
my-database-name
Modify File
(
Name =
my-databse-name,
FileName =
‘Cdatamy-database-name.mdf’
)
Go
Now use copy & paste or drag & drop method to change
the location of data files. To verify this, you have to set
your database Online.
You may like to read: SQL server Data Transfer from One server to another
Date Published: May 24, 2012 - 10:06 pm
Difference between DBMS and RDBMS
DBMS stands for database management system. It is a program that enables
user to create, retrieve, extract and modify data from
database. DBMS are of different type ranging from small system
to large system, for example computerized library system,
hospital management system, flight reservation system etc. The
DBMS hides complex detail from users that is it relieves the
use from knowing how the data is stored physically and complex
algorithms used for performing operations on the database. It
only tells how operations are performed to retrieve data from
the database.
RDBMS short for relational database
management system. It is a program
that allow user to create, update and maintain a relational
database. It stores data in the form of table. The major RDBMS
products are Oracle, DB2, SQL, MySQL, Postgresql. RDBMS
solution is required when large amounts of data are to be
stored as well as maintained.
Comparison table
|
Features
|
DBMS
|
RDBMS
|
|
Meaning
|
Database Management System
|
Relational Database Management System
|
|
Relation
|
No relationship concept
|
It shows relation between two tables |
|
Normalization
|
Support
|
Does not support
|
|
User support
|
It support single user
|
It supports multiple users |
| Client-Server model |
Does not support
|
Support
|
|
Data model
|
It supports network, hierarchical model | It supports only relational model |
|
System Requirement
|
It requires low software and hardware equipments | It requires high software and hardware equipment |
|
Internal storage
|
It internally treats data as file | It internally treats data as table |
|
E.F CODD rules
|
It follows only 4/5 rules. | It follows 12 rules. |
|
Example of database
|
FoxPro, IMS
|
SQL Server, Oracle, DB2, MySQL |
|
Constraints
|
Constraints are not imposed at file level. It is programmers job to impose it at the application level. |
Constraints are imposed at schema level.
|
|
Security
|
Less secure
|
More secure
|
|
Applications
|
It is used for small business application.
|
It is used for large and complex applications.
|
Date Published: May 08, 2012 - 4:19 am
SQL: Views creation and Restoration
A view
is basically a virtual table which does not exist physically
and contains columns and data from different tables. A
view itself does not contain any data or information; it is a
set of queries that are applied to one or more tables. The data
accessible through a view is not stored in the database as a
distinct objects rather it is stored as a SELECT statement. The
result set of the SELECT statement forms the virtual table
returned by the view. A user can use this virtual table by
referencing the view name in Transact-SQL statements the same
way a table is referenced.
Creating a View
Below
is the syntax for creating view
CREATE
VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SELECT column_name(s)
FROM table_name
WHERE condition
Example:
As
SELECT EmpID,
EmpName FROM EmpData
It will create a
View with the name SampleView
that will only contain EmpID, EMPName.
We can
query the view above as follows:
SELECT
* FROM SampleView
It
will show all the details of EmpData
Updating a View
You
can update a view by using the following syntax:
CREATE
OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SELECT column_name(s)
FROM table_name
WHERE condition
Example:
Suppose we want to add the
"Item" column to the "Current Product List" view. We will
update the view with the following SQL:
CREATE
VIEW [Current Product List] AS
SELECT ProductID,ProductName,Item
FROM Products
WHERE Discontinued=No
SELECT ProductID,ProductName,Item
FROM Products
WHERE Discontinued=No
Droping a View
You
can delete a view by using following syntax:
DROP
VIEW view_name
Corruption in Views
SQL gets
corrupted due to various reasons like virus attack meta-data
structure corruption, power failure, hardware problem, improper
system shut down etc. You can easily restore
corrupted Views from the backup, if available.
Restoration of
Views
To
restore your corrupt view, you will need to full backup. If you
don’t have full backup you can’t restore but third party SQL database recovery software can
help.
Date Published: May 03, 2012 - 2:19 am
How to repair damaged .sdf file of SQL CE?
SQL server compact is a compact relational database developed by
Microsoft Corp. for mobile & desktop applications. Initially,
it was developed for the mobile and known as SQL server mobile
application. It is freeware software & supports ACID property
but do not support durability by default. All the information of
SQL server compact edition (SQL CE) is stored in the .sdf file
and the size of this file can be up to 4 GB. You can encrypt .sdf
file by 128 bit encryption for the data security
purpose.
SQL CE database files are divided into 4 KB unit logical named pages. Each page is written to the database file. SQL CE calculates & maintains a checksum for all the pages. SQL CE pages can be corrupt or damaged due to several reasons like virus attack, power failure and many more. To verify this, scroll down.
How to Verify Corruption in .SDF file? To verify corruption in the sdf file, call System.Data.SQLserverce.SQLceengine class. It will recalculate the checksum values for all the pages in the database and match with the expected checksum value. If it returns true then your file is not corrupt. If it returns False then your file is corrupt and need to repair corrupt file.
SqlCeEngine engine = new SqlCeEngine("Data Source = Your-database-name.sdf");
if (false == engine.Verify()) {...}
How to Repair? To repair & recover SQL server compact database file, you can try repair method of SQLCeEngine. It will scan the database files and try to calculate checksum for all corrupt pages. Repair method of SQLCeEngine has two options:
engine.Repair(null, RepairOption.DeleteCorruptRows);
engine.Repair("Data Source= Test.sdf; LCID= 1033; Case Sensitive=true;", RepairOption.DeleteCorruptRows);
RepairOption.RecoverCorruptedRows: If you use option then repair method will try to read all data from the corrupted pages. Chances of data loss in this option is very minimum but not guaranteed that your database is free from logical corruption.
engine.Repair(null, RepairOption.RecoverCorruptedRows);
engine.Repair("Data Source= Test.sdf; LCID= 1033; Case Sensitive=true;", RepairOption.RecoverCorruptedRows);
Applied for: SQL server Compact 3.5
SQL CE database files are divided into 4 KB unit logical named pages. Each page is written to the database file. SQL CE calculates & maintains a checksum for all the pages. SQL CE pages can be corrupt or damaged due to several reasons like virus attack, power failure and many more. To verify this, scroll down.
How to Verify Corruption in .SDF file? To verify corruption in the sdf file, call System.Data.SQLserverce.SQLceengine class. It will recalculate the checksum values for all the pages in the database and match with the expected checksum value. If it returns true then your file is not corrupt. If it returns False then your file is corrupt and need to repair corrupt file.
SqlCeEngine engine = new SqlCeEngine("Data Source = Your-database-name.sdf");
if (false == engine.Verify()) {...}
How to Repair? To repair & recover SQL server compact database file, you can try repair method of SQLCeEngine. It will scan the database files and try to calculate checksum for all corrupt pages. Repair method of SQLCeEngine has two options:
- RepairOption.DeleteCorruptedRows
- RepairOption.RecoverCorruptedRows
engine.Repair(null, RepairOption.DeleteCorruptRows);
engine.Repair("Data Source= Test.sdf; LCID= 1033; Case Sensitive=true;", RepairOption.DeleteCorruptRows);
RepairOption.RecoverCorruptedRows: If you use option then repair method will try to read all data from the corrupted pages. Chances of data loss in this option is very minimum but not guaranteed that your database is free from logical corruption.
engine.Repair(null, RepairOption.RecoverCorruptedRows);
engine.Repair("Data Source= Test.sdf; LCID= 1033; Case Sensitive=true;", RepairOption.RecoverCorruptedRows);
Applied for: SQL server Compact 3.5
Date Published: Apr 23, 2012 - 10:44 pm
How to Prevent SQL Server LDF file growing unexpectedly problem?
Each database in SQL server contains at least one data file and
one transaction log file. SQL server database stores data
physically in the data file. A transaction log file contains all
the modification related information performed on the database.
It is logically divided into small segments that are known as
virtual log files. Database administrator is responsible to
maintain the size of transaction log file. Sometimes, size of
transaction log file grows unexpectedly because database
administrator has not planned for the transaction log files
grow.
I have seen several related questions on the different forums like how to shrink transaction log file? My transaction log file is growing unexpectedly, what to do? How to reduce the size of transaction log file? Before going to solve these problems, first we will discuss about how to prevent this problem because prevention is the best method to avoid problems. Following points are very helpful to prevent transaction log file growing unexpectedly….
I have seen several related questions on the different forums like how to shrink transaction log file? My transaction log file is growing unexpectedly, what to do? How to reduce the size of transaction log file? Before going to solve these problems, first we will discuss about how to prevent this problem because prevention is the best method to avoid problems. Following points are very helpful to prevent transaction log file growing unexpectedly….
- Avoid auto grow of transaction log file.
- Take back-up of transaction log file regularly and delete the inactive transaction log file of your database.
- Stop the uncommitted running transaction.
- Design the transaction log file to be small.
- Change the recovery model; there are three types of recovery models available in the SQL server, simple, bulk logged and full recovery model. Simple recovery model automatically shrink the transaction log file of your database while bulk-logged and full recovery model does not. If you use simple recovery model then you can recover SQL database from most recent backup. By the using of bulk-logged & full recovery method, you can recover your database to the point of failure.
If you have followed above points before creating your database
then I am quite sure you will not face above discussed
problems. In case still facing same problem then run below
command to shrink your transaction log files.
DBCC SHRINKFILE
(
{File name | file id}
{[, emptyfile] | [[, tragetsize] [, {notruncate | truncateonly}]]}
)
[With no-infomsgs]
DBCC SHRINKFILE
(
{File name | file id}
{[, emptyfile] | [[, tragetsize] [, {notruncate | truncateonly}]]}
)
[With no-infomsgs]
Date Published: Apr 10, 2012 - 2:48 am
Database mirroring & log shipping in MSFT SQL server
Microsoft SQL server offers a high
availability solution for servers or databases that reduce the effect of software or
hardware failure and maintain high availability of databases.
It also minimizes the downtime of database. The main motive of
high availability solution is to recover SQL
server database from any corruption. A SQL
server database can be corrupt or damaged due to several
reasons like virus attack, hardware problem, power outage and
so on.
SQL server provides many options for
maintaining high availability solution like database mirroring, log
shipping, failover clustering, replication,
scalable shared databases,
and more. In this article will try to
explain two of them: Database Mirroring and Log Shipping
Database Mirroring: It is a software solution to increase the database
availability and support almost instantaneous failover. It is
used for single standby
database that is also known as
mirrored database. In this, production database is known
as principle
database. To illustrate it, see
the below image.
Server A------ Witness----Server
B
Where server A is
the principle server and server B is the mirrored
server.
If server A gets corrupt due to any above mentioned reasons
then database administrator can restore corrupt database from
mirrored server that is server B.
Server
A
Witness---Server B
In the above figure, server B works like principle database
because server A has been corrupted.
Note: To
use database mirroring solution on database, your database must
be in full recovery
mode.
Log Shipping: Like mirroring, it also works on the database levels
but it is used to maintain one or more standby databases. In
this, production server is known as primary server and the log
shopped (standby) servers are known as secondary
servers.
Server
A
!
Backup share
!
Server B Server C
Where server A is the primary server and server B & server
C are the secondary servers
Note: It
offers flexibility for supporting multiple standby or secondary
databases.
How to choose high availability solution
for your database? If you have a
database and want a high availability solution then you can
choose database mirroring or log shipping. A given database can
be mirrored or log shipped, even can simultaneously mirrored or
log shipped. To choose what high availability solution to use,
consider the below points:
How many designation (secondary) servers do you need?
-
If you have required only one designation server then mirroring is recommended.
-
If you have required more than one designation servers then log shipping is recommended.
If you need to delay restoring on the designation server then
use log shipping with or without database mirroring.
Date Published: Mar 21, 2012 - 3:43 am
Step-by-step instructions on how to to perform Online Page Restore in SQL Server 2005
It was always painful for me to take backups in SQL Server and
then performing a safe restoration of those backups. Perhaps,
restoration is the most important job that should be performed
cautiously in order to save time and effort. In a demanding
business environment, a user may be required to create multiple
backup copies and restore critical database pages. In such
circumstances, the need for a standard restoration technique
comes into picture.
I was using SQL Server 2005 version and 'Online Page Restore' is one of the
functionalities included with its Enterprise edition. This
feature is immensely helpful when only a few pages are damaged
in the database. However, it has a downside as this feature
doesn't work for transaction logs, file header page (PageId =0
), allocation pages (GAM, SGAM, PFS, DIFF, ML), full-text and
boot page. Moreover, the restoration procedure should start
with the first backup as a full, file or filegroup backup and
all the log backups should form an uninterrupted chain up to
the most recent log. This helps to keep the database page
undated and fully consistent.
It was important to first examine the database and determine
all corrupt pages. In any event of database corruption, the two
most prominent errors that show up frequently are 'Error 823'
and 'Error 824'. As I took snapshots at each occurrence of
these errors, I already gathered sufficient information. They
provided the database id and the offset or number of the
damaged page. Also, I checked the SQL Server Error Log and ran
the DBCC CHECKDB command. The 'MSDB.dbo.suspect_pages' table
has a row corresponding to each page that encountered error 823
or 824 during its read operation. This helped me to ensure that
I didn't miss out on a page.
I had the full database backup and also, other differential and
log backups up to now. I started with the restore operation
using the 'RSTORE DATABASE' command with page clause as
follows:
For restoring database-
RESTORE DATABASE CorruptDB PAGE =
'1:80'
FROM DISK =
'D:BackupCorruptDB_full.bak'
WITH NORECOVERY
For log-
RESTORE LOG CorruptDB
FROM DISK =
'D:BackupCorruptDB_log1.bak'
WITH NORECOVERY
Here, I used 'norecovery' option to
restore a full backup with pageid '80'. This was successful and
I got back all the data intact. At the same time, I learned
about commercial SQL
database recovery tools. These utilities prove to be
worthwhile in such cases of corruption and help to easily
recover all inaccessible or damaged SQL database
objects.
Date Published: Mar 17, 2012 - 4:11 am
How to fix Error 'Restore failed for Server 'SQLSERVER'
Backup & restore are the most important task of any
database administrator. A good backup & restore plan will
help you to save your valuable data as well as business. If you
have not maintained backup of your database and suddenly
disaster occurs on the database then you will be in nightmare
situation. If you are working for a company then you may loose
your job also; therefore always maintain and update your backup
properly.
Consider a scenario wherein you have two
server, server A
& server
B and both the servers have SQL
server version 10.50.1600. You have
taken the backup of your data & log file from server A and
trying to restore on server B but got following error
message
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'SQLSERVER'.
(Microsoft.SqlServer.SmoExtended)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The database was backed up on a
server running version 10.50.1600. That version is incompatible
with this server, which is running version 10.00.1600. Either
restore the database on a server that supports the backup, or
use a backup that is compatible with this server.
(Microsoft.SqlServer.Smo)
Cause: The
most possible cause for above error message is that your
destination server (server B) may have connected to lower
version of SQL server as compared to source server (server
A).
Note:
Restoring a SQL server Database from lower version to higher
version or same version would not give error message but
restoring from higher to lower version will always result error message.
How to Fix:
To fix above error message, you will have to perform below
steps.
-
First of all open SQL query window
-
Run “Select @@Version” command
-
See the Output of command
-
Match current version of server B with old connected server
-
If versions are different for both servers then update the version of old connected server
-
Now perform restore operation
-
Successfully done
Date Published: Mar 12, 2012 - 11:54 pm
Microsoft (MSFT) Corp. Releases SQL Server 2012
Microsoft Corp. announced that the latest version of the
world's most widely deployed data platform, Microsoft SQL Server
2012, has released to manufacturing. SQL Server 2012 helps address the challenges
of increasing data volumes by rapidly turning data into
actionable business insights. Expanding on Microsoft's commitment
to help customers manage any data, regardless of size, both
on-premises and in the cloud, the company today also disclosed
additional details regarding its plans to release an Apache
Hadoop-based service for Windows Azure.
"Data is being generated faster than ever before, and organizations need a way to process and analyze all that data," said Ted Kummert, corporate vice president at Microsoft. "Whatever the type or size of data, SQL Server 2012 delivers the platform and familiar tools to manage data, generate actionable insights and help drive business impact." Continue reading......
"Data is being generated faster than ever before, and organizations need a way to process and analyze all that data," said Ted Kummert, corporate vice president at Microsoft. "Whatever the type or size of data, SQL Server 2012 delivers the platform and familiar tools to manage data, generate actionable insights and help drive business impact." Continue reading......
Date Published: Mar 12, 2012 - 11:42 pm
Complete overview on SQL database recovery advisor
SQL database recovery advisor is a newly
introduced feature in SQL server 2012 (denali). It helps
database administrator (DBA) to maintain correct restore
sequence from an existing backups. Now DBA can restore database
from the SQL server management studio (SSMS). By default,
database recovery advisor will try to restore database to the
last backup taken. If you want to restore another backup
instead of last backup taken then use Timeline feature of
database recovery advisor. Timeline displays the backup history
of the database which helps DBA to
restore database backup.
To illustrate the feature of database
recovery advisor, let’s create
a database & maintain backups to recover SQL database.
Create database employee-details
-- To create employee details
database
GO
Use employee-details
GO
Create table age (c int)
-- To create table age
GO
BACKUP DATABASE [employee-details] TO DISK = N'C:Program
FilesMicrosoft SQL
ServerMSSQL11.MSSQLSERVERMSSQLBackupemployee-details.bak' WITH
NOFORMAT, NOINIT, NAME = N'employee-details-Full Database
Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take full
backup of employee details database (First Backup)
Insert into age values (1)
-- First value is inserted in age
table
BACKUP DATABASE [employee-details] TO DISK = N'C:Program
FilesMicrosoft SQL
ServerMSSQL11.MSSQLSERVERMSSQLBackupemployee-details.bak' WITH
DIFFERENTIAL , NOFORMAT, NOINIT, NAME =
N'employee-details-Differential Database Backup', SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take
differencial backup of employee details database
(Second Backup)
Insert into age values (2)
-- Second value is inserted in age
table
BACKUP LOG [employee-details] TO DISK = N'C:Program
FilesMicrosoft SQL
ServerMSSQL11.MSSQLSERVERMSSQLBackupemployee-details.bak' WITH
NOFORMAT, NOINIT, NAME = N'backup_employee-Transaction Log
Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take
transaction log backup of employee details database
(Third Backup)
-- Now recover to the
differential backup
USE [master]
ALTER database [employee-details] SET SINGLE_USER WITH ROLLBACK
IMMEDIATE
GO
RESTORE Database [employee-details] FROM DISK = N'C:Program
FilesMicrosoft SQL
ServerMSSQL11.MSSQLSERVERMSSQLBackupemployee-details.bak' WITH
FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
RESTORE Database [employee-details] FROM DISK = N'C:Program
FilesMicrosoft SQL
ServerMSSQL11.MSSQLSERVERMSSQLBackupemployee-details.bak' WITH
FILE = 2, NOUNLOAD, STATS = 5
GO
ALTER Database [employee-details] SET MULTI_USER
GO
Use employee-details
GO
Insert into age values (3)
BACKUP LOG [employee-details] TO DISK = N'C:Program
FilesMicrosoft SQL
ServerMSSQL11.MSSQLSERVERMSSQLBackupemployee-details.bak' WITH
NOFORMAT, NOINIT, NAME = N'employee-details-Transaction Log
Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take
backup of transaction log of employee detail database
(Fourth Backup)
How to Perform Database
Restore? To perform database
restore, go to the employee details node in object explorer,
right click on the database node and select restore task. By
default, database recovery advisor will restore database to the
last backup taken i.e. Fourth
Backup. To restore full,
differential or first transaction log backup use timeline
feature of recovery advisor.
You may like: How to backup the Transaction log in SQL
Server?
Date Published: Mar 05, 2012 - 4:26 am
Available methods to repair Corrupt or Damaged SQL Server data files
Sometime while opening SQL Server database
files (MDF & NDF), Microsoft SQL server shows an error
message that is “unable to open
desired file because file is corrupt or
damaged”. If you have maintained
an updated backup for file then this problem will not have
enough severity. But in case backup is not available anywhere
of corrupt file, then you are left with only one option that is
repairing of mdf or ndf files.
This post is all about how to
repair corrupt SQL Server data files.
Repair Methods for corrupt MDF & NDF
files: There are two methods to
repair corrupt or damaged SQL server data file (MDF & NDF).
Methods that I will describe in this post will be suitable for
most of the SQL server version.
-
Repair with DBCC CheckDB Utility
-
Repair with Stellar SQL Recovery utility
Repair with DBCC CheckDB
Utility: Microsoft SQL server
offers an utility to repair corrupt SQL server that is
DBCC CHECKDB. It has two options to repair corrupt file,
repair-rebuild & repair-allow-data-loss.
-
Repair-Rebuild: If level of corruption is less then DBCC CHECKDB will try to repair and rebuild your corrupt file with no data loss.
-
Repair-allow-data-loss: In case level of corruption is very high then DBCC CHECKDB will try to repair your corrupt database file with minimum data loss. If your database is very critical and you can't afford any single data loss then go with second method.
Note: Your
database should be in single user mode before running DBCC
CHECKDB on database.
Repair with Stellar SQL Recovery
Utility: Stellar SQL recovery
utility is an robust tool to repair corrupt SQL server database
files (MDF & NDF). It repairs tables, views, indexes,
stored procedures, triggers, default constraints, primary keys,
unique keys, foreign keys etc. You will have to perform some
simple steps to repair your corrupt files by Stellar SQL
recovery and the steps are....
Download-->Run-->Open Stellar Phoenix SQL
recovery-->Choose your corrupt file-->Start scan-->See
preview-->Start Repair-->Save Repaired File-->Done
You can see complete software details & working steps of
Stellar Phoenix SQL recovery @
http://www.stellarsqldatabaserecovery.com/screenshot.php
Date Published: Mar 01, 2012 - 12:15 am
Two ways to Change the Recovery Model
Microsoft SQL server offers a facility to
manage transaction log of SQL server that is called
Recovery
Model. It is a database
property and helps in deciding whether transaction log will
require or not for the database. There are three types of
recovery models: Simple, Full & Bulk logged. In general,
database uses simple or full recovery model.
In this section, we will discuss about how
to see & change the recovery model of a SQL server
database.
There are two ways to see and change the recovery model
-
By SQL server management studio.
-
By Transact SQL
By SQL
server management studio: To do
this, follow below steps
-
Connect with appropriate instance of SQL server database engine
-
Go to the object explorer & click on the server name
-
Expand the server tree
-
Now expand the database and choose your desired database (user or system database)
-
Right click on the desired database and select properties
-
Click on the options in display panel
-
See current Recovery Model from the recovery model dialog box
-
To Change recovery model, select from available options simple, full or bulk-logged
By
Transact SQL: To do this, run the
below commands
To See,
SELECT name, recovery_master_desc
FROM sys.databases
WHERE name = 'master' ;
GO
Note: Above
query will show the recovery model for the master
database.
To Change,
USE model;
ALTER DATABASE model SET RECOVERY FULL;
Note: Above
command will set the full recovery model for model
database.
Note: First
method is application for all version of SQL server but second
method is application for SQL server 2012 only.
Date Published: Feb 28, 2012 - 3:05 am
How to Fix SQL Server Database Error 3707?
Yesterday night, I
had got a mail from one of my friend. He has explained about
getting an SQL server error message 3707 on his SQL server
database. After reading full mail, I went there and try to fix
his problem as soon as possible. Here I will share all the
steps that have performed to resolve SQL server error message
3707.
See what the error message was:
Cannot detach a
suspect or recovery pending database
It must be repaired or dropped.
(Microsoft SQL Server, Error: 3707)
Causes for the
error: He had got above
error message due to connectivity problem due to which MSSQL
server marks his database in suspect mode. SQL server marks
database in suspect mode due to several reasons which could be
like connectivity problem, power outage, memory outage etc. In
your case, the cause may be different.
1. Set Database to
Emergency: First of all, set your
database in emergency mode. It will mark your database read
only, limited access to sysadmin & disabled logging. You
can do it with the help of below query.
ALTER Database Employee_Details SET
Emergency;
GO
2. Set Database to Single-User
Mode: Now change the access
control of your database from multi-user to single user by this
query.
ALTER Database Employee_Details Set
Single_User
GO
3. Run DBCC CHECKDB on
Database: After setting your
database in single user mode, Run DBCC CHECKDB command with
repair_allow_data_loss option. It will repair your database
with minimum data loss.
DBCC CHECKDB (Employee_Details,
REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;
GO
Note: This
command will try to repair and recover data as much as possible
but you might be loss some amount of data.
PS: I will recommend you, if you
are not the boss of the database then consult your supervisor
and discuss the effect of above command.
4. Set Database to Multi-User
Mode: Now again change the access
control of your database from single-user mode to multi-user
mode by this command.
ALTER Database Employee_Details set
multi_user
GO
5. Successfully done.
Share Your Opinion: You can share your experience with this error through
comments.
Date Published: Feb 17, 2012 - 1:14 am
FAQs for SQL Server Database Corruption
Question: Does a missing
file (.mdf) send a database in suspect mode?
Answer: NO. It may have true with the earlier (SQL 2000 & earlier) versions of SQL server but it is not true in current versions. Your database may be inaccessible but not in suspect mode in case of missing data files.
Question: How many tools are available to check the database integrity?
Answer: CheckDB is the only available tool to check the database integrity.
Question: How to bring database online from suspect mode?
Answer: If you database has gone in suspect mode then you are not able to do any operation on the database. To bring database online, ALTER Database Set Emergency. Once your database in emergency then RUN CheckDB with repair_allow_data_loss on the database and bring online.
Question: Do you loss data after running CheckDB with repair_allow_data_loss?
Answer: The short answer is YES. As name suggests, you will loss some amount of data after running CheckDB with repair_allow_data_loss.
Question: What are options available in CheckDB tool?
Answer: There are two repair options available in CheckDB tool. One is Repair_Rebuild and another is Repair_Allow_Data_Loss.
Question: What are the reasons for database corruption?
Answer: There are ton of reasons for database corruption. Some popular reasons are hardware failure, virus attack, power failure, meta-data structure corruption etc.
Note: 99% SQL database corruption happens due to faulty hardware.
Question: What to do after database corruption?
Answer: Don't panic; if possible take backup of corrupt database.
Question: How to recover corrupt database?
Answer: It is a very brought question but we can classified it in three simple steps.
Question: Which is the best third party SQL recovery software?
Answer: The short answer is Stellar Phoenix SQL recovery software. This recovery software recovers and repairs.mdf and.ndf files of SQL server. It supports all latest version of SQL server including SQL Server 2008 R2, 2008, 2005, 2000 & 7.0.
Question:Does simple recovery model store log files?
Answer: No, Simple recovery model does not store log files of SQL server database but full recovery model stores all the log records of SQL server database.
Answer: NO. It may have true with the earlier (SQL 2000 & earlier) versions of SQL server but it is not true in current versions. Your database may be inaccessible but not in suspect mode in case of missing data files.
Question: How many tools are available to check the database integrity?
Answer: CheckDB is the only available tool to check the database integrity.
Question: How to bring database online from suspect mode?
Answer: If you database has gone in suspect mode then you are not able to do any operation on the database. To bring database online, ALTER Database Set Emergency. Once your database in emergency then RUN CheckDB with repair_allow_data_loss on the database and bring online.
Question: Do you loss data after running CheckDB with repair_allow_data_loss?
Answer: The short answer is YES. As name suggests, you will loss some amount of data after running CheckDB with repair_allow_data_loss.
Question: What are options available in CheckDB tool?
Answer: There are two repair options available in CheckDB tool. One is Repair_Rebuild and another is Repair_Allow_Data_Loss.
Question: What are the reasons for database corruption?
Answer: There are ton of reasons for database corruption. Some popular reasons are hardware failure, virus attack, power failure, meta-data structure corruption etc.
Note: 99% SQL database corruption happens due to faulty hardware.
Question: What to do after database corruption?
Answer: Don't panic; if possible take backup of corrupt database.
Question: How to recover corrupt database?
Answer: It is a very brought question but we can classified it in three simple steps.
- If backup is available then restore corrupt database from backup.
- Run CheckDB on the database, see the SQL server error log & Run CheckDB with repair_allow_data_loss.
- Try any third party MS gold partner's SQL recovery software.
Question: Which is the best third party SQL recovery software?
Answer: The short answer is Stellar Phoenix SQL recovery software. This recovery software recovers and repairs.mdf and.ndf files of SQL server. It supports all latest version of SQL server including SQL Server 2008 R2, 2008, 2005, 2000 & 7.0.
Question:Does simple recovery model store log files?
Answer: No, Simple recovery model does not store log files of SQL server database but full recovery model stores all the log records of SQL server database.
Date Published: Feb 15, 2012 - 9:52 pm
Visitor Rating: 5 (1) (Rate)
 |
|
Story Clicks: 0
Feed Views: 105
Lenses (Add|?)
Comments (Log in to add)
Feed Details
Search FeedAgg.com
8035 serv 0.2101 seconds to generate.