Backup & Recovery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Backup & Recovery

Author

Topic

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2009-11-24 : 05:07:23

Hi,
we are using SQL Server 2008 for database. We will implement database mirroring on this .
I want to ask backup strategy for database ..
The database is in Full Recovery mode .
We will take full backup of database daily like in evening .
We will take transaction log backup every after 3 hours.
We are using one data file and one transaction log file.
is it good to schedule backup from management studio or from script we need to do this. Is this can give us recovery till recent point.
if Some data file missed or damaged ..we will use following steps to recover database .
Please let me know is this correct or not ..?
OR any suggestion from your site..

--Create tail-log backup.

BACKUP LOG DB1
TO DISK = 'D:\SQLServerBackups\DB1.bak'
WITH NORECOVERY;
GO

--Restore the full database backup .

RESTORE DATABASE DB1
FROM DISK = 'D:\SQLServerBackups\DB1.bak'
WITH NORECOVERY;

--Restore the regular log backup .

RESTORE LOG DB1
FROM DISK = 'D:\SQLServerBackups\DB1.bak'
WITH NORECOVERY;

--Restore the tail-log backup .

RESTORE LOG DB1
FROM DISK = 'D:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH NORECOVERY;

GO

--recover the database:

RESTORE DATABASE DB1 WITH RECOVERY;
GO

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-11-24 : 11:00:48

Have you tested this out? Did it work?

--
Gail Shaw
SQL Server MVP

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2009-11-25 : 06:49:35

I tested this by using sql server management studio Bk wizard (just click OK).
Like taking tail log backup and then restoring database with recover option .
But i want to do it with T-sql querys..but when i am running command to backup of tail log..it is saying database is in use by this session ....or if i run this command in some other database new query window..it is doing same..So i want to ask you where to execute these commands and these commands/steps are right or wrong ...?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources