December 22, 2008 at 3:59 am
Hi Guys,
This is Pradeep, i need small request , how to configure copy_only(Back up & Restore i read some articals) that command eaisly contionous back up in sql server 2005. Colud plz guide to me and how configure copy_only in sql server 2005, i tried 4 times not yet getting , if any body guide to me, detail step by step i have got problem back up & restore . i read some artical copy_only command eaisly back up & restore. plz provide related information.
Regards,
Pradeep.
December 22, 2008 at 4:11 am
refer BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f82d6918-a5a7-4af8-868e-4247f5b00c52.htm
example-
backup database northwind to disk='d:\backupwind.bak' with copy_only
The only difference is it doesnot reset the LSN during its backup procedures; which means id u're taking an ad-hoc request of a recent database copy, it wont disturb your differential backups LSNs.
Restoration syntax is same.
December 22, 2008 at 4:36 am
Hi Pradeep gi,
Thz for sending mail, but u have send one link not yet openend , what should i do pradeep gi.Could plz give some guidences gi.
Regards,
Pradeep.
December 22, 2008 at 5:07 am
That link is from BOL(SQL Server 2005 Books Online) and not an internet url.
Refer to following URLs
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://technet.microsoft.com/en-us/library/ms191495.aspx
btw i never mailed you!!
December 23, 2008 at 2:23 am
how to configure copy_only(Back up & Restore i read some articals) that command eaisly contionous back up in sql server 2005. Colud plz guide to me and how configure copy_only in sql server 2005,
COPY_ONLY is not a command. It is an option in BACKUP DATABASE command.
There is no special configuration for this. You have to just add WITH COPY_ONLY as mensioned in the earlier reply.
i read some artical copy_only command eaisly back up & restore.
NO. It does not EASILY backup/restore.
i tried 4 times not yet getting , if any body guide to me, detail step by step i have got problem back up & restore .
What problem did you face? Got any error message?
Note: Don't use this option in your routine backup. Use this only for special purpose. Because, it cannot be used as base for differential backup.
December 23, 2008 at 3:47 am
Hi,
i saw ur message, i have a got problem, my problem is i have create one database, i take backup but restore database got problem.
Ex:
iam creating one database like sample_database.
i take back up like:
BACKUP DATABASE SAMPLE_DATABASE TO DISK='C:\PROGRAMFILES\MICROSOFT SQL SERVER\...\BACK UP\SAMPLE_DATABASE.BAK'
after that back up sucefully message showing no problems.
now restore database.
RESTORE DATABASE SAMPLE_DATABASE FROM DISK='C:\PROGRAMFILES\MICROSOFT SQL SERVER\...\BACK UP\SAMPLE_DATABASE.BAK'
but proparly should not restore database have got problem it showing some error , could plz tell me how resolve these problem.
fisrt i said i have creating one database that is sample_database,
i taken back up , now iam restoring backup. Restoring time shall u removed sample_database or no need removed, but how to resolve that problem give me some tips.
Regards,
Pradeep.
December 23, 2008 at 4:38 am
can u post the error that you're getting during restore.
December 23, 2008 at 9:55 am
use data_sample
BACKUP DATABASE DATA_SAMPLE TO DISK='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DATA_SAMPLE.BAK'
RESTORE DATABASE DATA_SAMPLE FROM DISK='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DATA_SAMPLE.BAK' WITH NORECOVERY
ERROR MESSAGE:
Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'DATA_SAMPLE' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
December 23, 2008 at 10:18 am
beerampradeep (12/23/2008)
Msg 3102, Level 16, State 1, Line 1RESTORE cannot process database 'DATA_SAMPLE' because it is in use by this session. It is recommended that the master database be used when performing this operation.
There's a connection open to that database. Make sure that no one (including you) is connected to that database.
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
December 23, 2008 at 9:30 pm
Hi,
Could you please provide solution, plz provide syntax.
Regards,
Pradeep.
December 23, 2008 at 10:08 pm
Just USE master database instead of the one you want to restore.
use master
go
BACKUP DATABASE DATA_SAMPLE TO DISK='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DATA_SAMPLE.BAK'
RESTORE DATABASE DATA_SAMPLE FROM DISK='E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DATA_SAMPLE.BAK' WITH NORECOVERY
You cannot restore a database when some one is using it. Make sure that no user is accessing it.
December 26, 2008 at 12:46 pm
Hi,
It seems you wrote backup statement in one window and executed that, and again you opened another window and wrote the restore statement and executed that.
Before you execute restore statement close the backup statement window. This will solve your problem. The basic thing about restoration is that no other session will used apart from the restoration window.
The syntaxes you used are correct.
Regards
kb
July 14, 2009 at 3:55 pm
Hi,
See syntax below:
Use master
Backup database [database_Name]
to disk = 'path_of_backup_file'
with
init,
copy_only,
stats = 10
Hope this helps!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply