September 6, 2008 at 9:43 pm
hello all,
Is it possible to take a new full bacup while we restore database
with standyby option. i know in standby the database is in read only mode, can we take a new full back up while in standby mode or we have to change it in recovery mode to take a full back up ??
Thanks in advance.
Bobby
September 7, 2008 at 3:25 am
I think you can create a snapshot database, and make a backup of that.
TEST IT - TEST IT
/* Database Snapshots */
CREATE DATABASE Adventureworks_ss1430
ON (NAME = AdventureWorks_Data,
FILENAME = 'C:\Backups\AdventureWorks_data_1430.ss')
AS SNAPSHOT OF AdventureWorks;
DROP DATABASE Adventureworks_ss1440;
RESTORE DATABASE Adventureworks
FROM DATABASE_SNAPSHOT = Adventureworks_ss1430;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 7, 2008 at 9:40 am
Some more points adding to previous reply...
Database Snapshots are available from SQL 2005.
It is available only in Enterprise Edition.
Snaphshots will create Readonly database which cannot be UPDATED..
Database can be restored from Snapshot... Nice option to learn... It can be done only through T-SQL since there is no support from SSMS..
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 7, 2008 at 7:37 pm
Hi,
Thanks alot guys for the fast reply,
Bobby.
September 8, 2008 at 1:59 am
Note: Snapshots are not a backup solution. You cannot backup a snapshot. A a result, you cannot restore a database from a snapshot if your original database is gone.
See http://msdn.microsoft.com/en-us/library/ms189940.aspx for more info about limitations
Wilfred
The best things in life are the simple things
September 8, 2008 at 3:31 am
Thank you for pointing to that Wilfred.
The data is in a consistend state at the standby database, so data is available.
I've tested a snapshot scenario, and it got me totaly cracked :crazy:
Must be some kind of bug :blink:
No, it's a limitation (BOL)[/b]
In a log shipping configuration, database snapshots can be created only on the primary database,
not on a secondary database.
If you switch roles between the primary server instance and a secondary server instance,
you must drop all the database snapshots before you can set the primary database up as a secondary database.
Here 's the scenario I tested
Create database SSC01
go
use SSC01
go
Create table dbo.TSSC01 (Tid int not null identity(1,1) primary key
, Remark varchar(128) not null default ''
, tsInsert datetime not null default getdate()
)
go
set nocount on
go
-- perform insert 100 times
insert into dbo.TSSC01 (Remark) values ('myremark')
go 100
set nocount off
go
Select * from dbo.TSSC01 order by tsInsert ;
go
Backup database SSC01 to disk='X:\MSSQL.1\MSSQL\Backup\SSC01Full.bak' ;
go
Restore database [ssc01_RESTORED]
from disk='X:\MSSQL.1\MSSQL\Backup\ssc01Full.bak'
with MOVE N'SSC01' TO N'X:\MSSQL.1\MSSQL\Data\ssc01_RESTORED.mdf'
, MOVE N'SSC01_log' TO N'X:\MSSQL.1\MSSQL\Data\ssc01_RESTORED_log.LDF'
, replace
, STANDBY = 'X:\MSSQL.1\MSSQL\Backup\SSC01_Restored_Standby.rst';
go
CREATE DATABASE [ssc01_Snap]
ON (NAME = SSC01,
FILENAME = N'X:\MSSQL.1\MSSQL\Data\ssc01_Snap.ss')
AS SNAPSHOT OF [ssc01_RESTORED];
go
drop database [ssc01_RESTORED];
go
/*
Msg 3709, Level 16, State 1, Line 1
Cannot drop the database while the database snapshot "ssc01_Snap" refers to it. Drop that database first.
*/
Backup database [ssc01_RESTORED] to disk='X:\MSSQL.1\MSSQL\Backup\ssc01_RESTOREDFull.bak' ;
/*
Msg 3036, Level 16, State 4, Line 1
The database "ssc01_RESTORED" is in warm-standby state (set by executing RESTORE WITH STANDBY) and
cannot be backed up until the entire restore sequence is completed.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
*/
go
Backup database [ssc01_Snap] to disk='X:\MSSQL.1\MSSQL\Backup\ssc01_SnapFull.bak' ;
go
/*
Msg 3002, Level 16, State 1, Line 1
Cannot BACKUP or RESTORE a database snapshot.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
*/
go
restore database [ssc01_RESTORED]
with recovery;
go
/*
Msg 5094, Level 16, State 2, Line 1
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*/
RESTORE DATABASE [ssc01_RESTORED]
FROM DATABASE_SNAPSHOT = 'ssc01_Snap' ;
go
/*
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 32(error not found) while attempting to open or create
the physical file 'X:\MSSQL.1\MSSQL\Data\SSC01_log.LDF'.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*/
RESTORE DATABASE [ssc01_RESTORED];
go
/*
Location: recoveryunit.cpp:1887
Expression: IS_OFF (DBT_INLDDB, m_Dbtable->dbt_stat) && IS_OFF (DBT_USE_NOTREC, m_Dbtable->dbt_stat)
SPID: 54
Process ID: 1020
RESTORE DATABASE successfully processed 0 pages in 3.828 seconds (0.000 MB/sec).
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details.
Typically, an assertion failure is caused by a software bug or data corruption.
To check for database corruption, consider running DBCC CHECKDB.
If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft.
An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
YOUR CONNECTION GETS DISCONNECTED AT THIS POINT !!!!
*/
Select *
from ssc01_RESTORED.dbo.Tssc01;
go
Select *
from ssc01_Snap.dbo.Tssc01;
go
/* Cleanup */
use master
go
/* drop DATABASE [ssc01_snap]; */
/* drop DATABASE [ssc01_Restored]; */
/* drop DATABASE [ssc01]; */
go
My conclusion: Snapshot on a standby database is a big NONO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2008 at 8:29 am
The only time you can use a snapshot like that is if you have database mirroring. Providing the mirror DB is synchronised (note, synchronised, not running in synchronous mode), then you can create a snapshot on the mirror DB and read that snapshot.
Other than that's the DB has to be online to create a snapshot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply