When I tried to restore backup of publication database, time taken for restoration is too long [ 4 hours]. The actual database restore takes only 20 minutes but the stored procedures used for cleanup replication takes the remaining time. Is the issue related to my environment or the cleanup replication stored procedure?
View 7 RepliesWe take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedHello friends,
Kindly guide, if you are aware of any mechanism for restoring the differential backup only.
This can save a lot of time other than restoring complete database.
Thanks in advance.
Shrirang
Hello there,
I am facing problem in restoring a single table from backup file.
I have taken backup before one week, and now that i lost my data of one table named 'activity' i want to restore its data from backup.
How do i do it? i am not able to restore it.
Regards,
Dev.
Hi All:Being a bit of a nube to MSSQL I could use a little advice. This is thesituation.A client's HDD became full so I backed up, deleted then recreated a blankdatabase (they said they didn't need the stored data).They have now deceided that they want the data again.My plan is to restore the database to a second volume so it can grow as muchas is needed, however, there is now data in the second instance that wouldneed importing to the restored DB.I can restore the backed up db with a different name and reconfigure thesoftware that accesses it but what would be the best method to append therestored DB with the data in the current instance of said DB? Both DBs areidentical.TIAMP
View 1 Replies View Relatedi am restoring newdatabase(test) with the backup from other server.
The problem is the restoring process is hanging and its not restoring the
database .when i stopped and refreshed the databases the database is not showing up
could some body Give patch for this problem
Thanks
Prasad
pls tell me how to solve the database in use problem while restoring database using query.
View 2 Replies View RelatedHi,
I have a backup (.bak) of one 'AdventureWorks' database with me and need to restore it to the AdventureWorks database I have created in my SQL Server 2005 database. I go by the normal process --> restore from device and then select the path of the .bak file in my hard drive but it is giving the error "The backup set holds a backup of a database other than the existing database AdventureWorks".
Obviously I have got the backup file from a different source and would like to replicate the same database in my own machine.
Can anyone please help me find out why is this not working?
Regards.
Hi,its newbie question.I have one production server(A) ,one backup server(B),additionalserver(C).my database backup are moved from A to C in regular basis.My problem is that when ever I have to restore my database in BackupServer i.e B,I have to first copy that particular Backup File on to my Backup Serverfrom Cthen i have to perform my restoration process.Even if I mapped the drive in which my bakup file is lying ,I amnot able to see that drive in SQL Entriprise Manager and when i amrestoring through Query Analyzerit shows the following error -Server: Msg 3201, Level 16, State 2, Line 1Cannot open backup device 'G:pharmacypharmacy_db_200505210330.BAK'.Device error or device off-line. See the SQL Server error log for moredetails.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.What i think is it a waste a time ,first copy the bak file in localdrive and perform the restoration process.SO please can any one guide me how should i do the network restorationif possible or put me to right path for doing it so.I am using SQL SERVER 2000,OS WINDOWS 2000 Adv SERVER.cheersT.V
View 3 Replies View RelatedHi,Does a database restoration perform anyupdate-statistics/defragmentation by default. We observed a markedimprovement in performance when we restored the a database from an highend machine to a low machine.Could someone shed some light on this.Regards,Thyagarajan Delli.
View 1 Replies View Related Hi Experts,
I am setting up new production box. I have 2 separate boxes with SQL server 2000 Standard Edition with sp4.
As SQL server standard edition don't have logshipping in it, I created manual jobs to ship logs every 10 mins and Full Backup once in a day from Server A to Server B.
Database on server A have multiple filegroups. When I try restoring database backup on Server B which already has the database on standby, I get an error.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_TESTDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_ITEMDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_OTHERDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'I:sqldatapsi_dbPSI_DB_ANALYSISDATA.mdf' may be incorrect.
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 5105, Level 16, State 9, Line 1
.
.
.
.
.
.
.
This error continues for all the files I created on server A
But the path I:sqldatapsi_db exists.
Restoration works fine when I don€™t have any database on Server B.
I am using the "replace" keyword while doing the restoration
Please help, this is little urgent.
Thanks in advance.
Regards
Sachin
I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.
Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.
Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
Hello,
I want to create one more publication on a database (SQL Server 2005) that is already a publisher and has subscribers. The subscribers of the second publication will not be inserting new records so I will mark the articles (tables) as download-only.
Is there anything extra that I should take into consideration? Are there any side effects of this process?
Thanks in advance.
SilentOceaner.
Hi ...i am new to sql server replication, im using the sql server 2005.
I was configure the replication n publish a database , i wanna add a new subscription, but i am facing the follow error
You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
How do i add another subscription database,since in my sql server has only 1 subscriber?
Hi,I am trying to use Enterprise Manager Wizard to create a newPublication database but I got the following message.Error 2812: Could not find stored procedureMyDB.dbo.sp_MSadd_logreader_agentThere seems that some replication stored procedures.Any help?Thanks in Advance
View 1 Replies View Related
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
View 2 Replies View RelatedI have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View RelatedBackup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies View RelatedI should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
Hi All,I am facing this issue very frist time. I add a logical device asfollowing:USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksData','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksData.bak'-- Create a logical backup device, AdvWorksLog.USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksLog','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksLog.bak'-- Back up the full AdventureWorks database.BACKUP DATABASE AdventureWorks TO AdvWorksData-- Back up the AdventureWorks log.BACKUP LOG AdventureWorksTO AdvWorksLogThe database backup is completed successfully but log's backup failswith the following messages:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current databasebackup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.We are moving from SQL 2000 to SQL 2005. I have ensured that databasehas full recovery mode on. We are using SQL 2005 on Windows 2003 ServerSP1.Further more, I also created a plan to test it, and plan also failswhen backing up the logs.Can anyone shed some light on this issue.Thanks in advance.Najm
View 1 Replies View RelatedHi,
I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.
The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:ackups.
The job executes the following steps:
Step 1: BackupFull:
Command:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:Backups' /* location of backup files */
Step 2: MarkAndBackupLog
Command:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:Backups' /* location of backup files */
My challenges are:
1. The job doesn't back up my databases(D:Backups is empty after executing the job)
2. I get every time the following error in error protocol of the job:
BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).
I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.
Any help would be appreciated.
Thanks in advance,
Greg
I am using sql server 2000 and windows server 2003 standard edition:
My database backup job is failing due to lack of disk space. I am taking the backup onto E drive and the
available free space on E drive is 6.85 GB and there are no other drives I can use for the database backups.
The size of mdf file is 21 GB and that of ldf file is 4.2 GB.
The transaction log back up job of that db ran fine.
This database recovery model is Full and
Auto shrink is not checked.
There is one primary filegroup for the database.
In this situation, I am thinking of the following option:
1. Backup the db and log files onto another network shared drive.
If I want to still use the same server E drive to backup the db and log files instead of using another network shared drives.
how can I do that.
Please let me know the best way of handling this issue.
Any help is greatly appreciated. Thanks!
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
Please excuse my lack of understanding as I am not a SQL DBA.
I have just received a database backup: "myDB_20005034400.bak" and have been
asked to put a copy on my server. I am told that this was made from the Enterprise Manager's DB Maintenance Utility. The backup Administrator has left the company with the server in a crashed condition.
How can I find out what "myDB_20005034400.bak" pertains to? (No msdb, etc. available). I am RESTORING TO A NEW SERVER.
MAIN: HOW DO I RESTORE THIS FILE TO A NEW DATABASE; ie TestDB (TestDB.mdf;
TestDB.ldf)?
Thanks a trillion.
Shelly
Hi,
Please give the retoration procedure:
I have a production server and I want test it on the other machine to check whether my backups are working or not?(Please give me step by step so that I can do easily)
Your answer will be highly appreciated.
Thanks,
dindu
Hi !
when i am trying to restore MSDB database from a disk backup it is giving an error saying
"Database in use.System administrator must have exclusive use of the databse to run the restore operation".
But i am not using msdb anywhere.when i see the processes under current activities i see an application named sqlagent-alert engine using the database.even after killing this process it reppaears and stops the restore operation.
so i stopped sql server agent and did the restore operation.It worked.
so is it necessary to stop sql server agent while restoring msdb?may i know the logical reason for it?
Hi Gurus,
While one of my DB through job i got following error message.
Failed to uncompressing data block. RESTORE DATABASE is terminating abnormally. Read on "VDI_2A04E518-0B70-41F8-BA22-48A63F9AC65C_0" failed: 1223(The operation was canceled by the user.) [SQLSTATE HY000] (Error 52008). The step failed.
Can someone help in this.
Thanks,
ServerTeam
Hi Gurus,
Every time when i want to restore database Iam going with SqlLight restoration scripts.
Here iam unable to get estimation time while restoration time.
How can i get time to complete restoration when database is in middle of restoration.
Please advice...
Thanks,
ServerTeam
VijayaKumar writes "We have SQL Server 7.0 & that machine was crahesd out. We took the .ndf & .ldf files and restore the DB in onther machine.
In that machine I am able to Login but during the time of retrival (select query or stored procedure) it gives the following error....
Server: Msg 823, Level 24, State 1, Line 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1174f100, page ptr = 0x217f8000, pageid = (0x1:0x55b1), dbid = 12, status = 0x801, file = E:MYSTS_CWS_DB_PRIMARY.ndf.
Connection Broken"