SQL Server 2000
Backup database command hangs. The command I am using is:
BACKUP DATABASE @DBName TO DISK = @filename WITH INIT
Size of the database is 140GB and we have 210 GB free disk space.
sp_who status shows runnable.
Any help would be appreciated.
Thanks.
I have a scheduled task that runs backups on our prodcution database at midnight - it usually takes five minutes to run - but the last two nights it has been running for hours and never completing. It ran for 3 hours last night - until i was paged by the database that the transaction log backups were failing (with error 3023) to run because the backup (Backup Database db to db1_nightly with init) job was still running.
The night before our European users could not reach the database - the machine was locked up when I got in in the morning - needed a hard boot to get it back up.
I ran the backup job last night at 8:30 pm and it succeeded - but when it ran at 12:00 AM for the last two days - it runs and never completes. When I tried to kill the process in Enterprise Manager at 3:00 AM, it wouldn't stop. I tried stopping the SQLAgent Service and MSSQLSERVER service to stop the process, with no luck. Finally an early morning drive and a hard boot killed the process.
Any thoughts? There were no new jobs scheduled in the last couple of days. There is no error in the logs (the step never completes - so it's not recorded). The backup works fine if run earlier in the day.
I am running SQL 7.0 with SP2.
Thanks you for any ideas - I'm hoping not to have to drive in at 3 AM this morning - so any thoughts would be great.
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
We have SQL 2000 running on Server 2003. The server has 4 GB RAM, RAID 1 and has the /3GB switch in Boot.ini. We have 6 databases, the largest is about 14 GB. Until recently I could use Enterprise Manager to perform database backups to a folder on the same server but now the operation fails. After selecting the location for the backup and clicking OK, the hard drive light comes on but goes out after 15 - 30 seconds. The progress bar does not move. I've waited and waited but nothing further happens and the server doesn't respond. The only solution is to push the power button and let the server re-boot. Very occasionally the backup will run for a minute or two and the progress bar might get to about one third of the way across, (in which case the destination file will show up with more than 0 bytes), but it never completes. I can find nothing in the event log relating to this problem. As far as I am aware, nothing has changed since the last successful backup. Backups to tape using Retrospect appear to work fine but I haven't done a test restore. Does anyone have any ideas as to what I can try to get the backups working again?
View 14 Replies View RelatedI'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.
The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.
When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.
I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.
The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.
My Vista system hangs immediately after I insert the second blank DVD. I'm doing a simple backup of my user files. I'm using Vista Premium, fully up to date with patches, minimal software installed because this system is 100% used as a Media Center. Hardware is off the shelf HP Media Center m8067n.
I checked Microsoft knowledgebase and no other similar cases popped up. Is anyone else experiencing this?
I'm trying to restore a database using SSMS GUI. In "Select backup devices" window if I click the Add button there the window and the whole process hangs like it will never work.
I tried to clear backup history, but that didn't solve it.
I tried with a couple of other databases, but same thing. It was working fine!
When I select All Actions>Backup Database and then click the ... button to choose a location on the hard drive, SQL Enterprise Manager Hangs and I have to kill it from Task Manager.
All other areas of EM browse the disk drive without a problem. (DTS, File Groups, etc.)
Anyone ever run into a similair problem?
Thanks
HELP!
For the past several weeks, I have been trying to install SQL Server 2005 on a Win XP SP2 PC. Regardless of the options I choose (SQL Engine + Client Tools, Client Tools only, etc.) when the installation gets to the end of the Client Tools setup and the setup status displays "Removing Backup Files", my PC appears to hang.
For a period of time, there is a fair amount of HD activity which gives me the impression that files are in fact being deleted, but then there is no HD activity and the installation appears to hang. Finally I give up and kill the setup process and reboot.
After reboot, it appears that the client tools have been installed (the icons appear and I can open the Management Studio, but when I try to connect to an existing instance of SQL, that hangs. This occurs for either Windows or SQL authentication.
Has anyone experienced this behavior and determined the root cause? What EXACTLY is happening during the "Removing Backup Files" phase of the installation?
Thanks for any help in advance,
Marc Mueller
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
Hi,
View 6 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
I have a Microsoft Cluster running on Server 2003 Entrprise. SQL 2000 8.00.2039 (SP4). 5gb physical memory installed.
With the databases online we run a test failover from the Cluster administrator. It takes about 30-40 seconds and completes without generating any Server Event log errors nor SQL log errors. Everything looks good from an administrative stand point.
However, when we test with running a series of queries to the databases, then failover, we notice that it can take up to 3 or 4 minutes before some of the databases will respond. Connections are not refused, they just sit there.
How can we troubleshoot this or does anyone have a similiar experience with this scenario?
We 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 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.
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
I have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:
1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment)
2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect.
3. I can create a new virgin user on the PC and that login experiences the same problem.
4. A complete rip and re-install of SSMS 2012 does not resolve the issue.
Hi, I am relative newbie to SQLServer. When I try to take a user database offline, the query "hangs," with the query processing circle spinning. The Sharepoint 7 application is running on top of the Enterprise SQL Server 2005 db with several logins sleeping and awaiting commands. No errors are generated until I kill the offline command. Anyone have any ideas? Do I need to kill all the connections?
View 2 Replies View RelatedI did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
I am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download.
When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end.
Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time?
I have a backup of sqlserver 2000 data base . Now presently i am working on sqlserver 2005. Is there any facility to IMPORT entire 2000 database intosqlserver 2005.Please help me if there is any chance. Thanks in advance.
View 1 Replies View RelatedI have a Subscriber database updated using transactional
replication and want to create a copy for development & testing. The Distribution database is running on the
same 2005 instance as the Subscriber and the Publication database on another
server running SQL Server 2005.
If I use a TSQL script to run the restore, the database is
restored to a usable state but I get the following messages:
Msg 15247, Level 16, State 1, Procedure
sp_MSrepl_check_job_access, Line 112
User does not have permission to perform this action.
The replication agent job [job name] was not removed because
it has a non-standard name; manually remove the job when it is no longer in use.
(I€™ve removed the job name from the message)
RESTORE DATABASE successfully processed 46219 pages in
935.413 seconds (0.404 MB/sec).
It looks like an attempt is being made to restore
replication information too. I note that
the RESTORE statement has a KEEP_REPLICATION options which implies (I would have
thought) that by default the replication information is not be restored.
Incidentally, before implementing replication, backing up
the live database and restoring the backup to a new database took < 7
minutes but running the same process with a Subscriber database backup takes
about 15 minutes.
I€™d be interested to know if anyone has any idea what I€™m
overlooking or doing wrong.
ThanksTim
Hi,
I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.
I am using VB.Net to do the backup and restore.
Is there any way of doing it?
Thanks!
i have a stored procedure that backups database on server A which i execute this SP from server B in a scheduled job. when i run this job it gives me an error:
Executed as user: domainuser_name. Could not relay results of procedure 'usp_BackupDBOnServerA' from remote server 'ServerB'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
i searched MS KB, everywhere but could not find the solution or even explanation why this happenning.
both sql servers are 2000 and they are linked.
Due to a network time skew some time ago, all of the databases on an SQL system now have the following information stamped on them..
Within Enterprise Manager (SQL 2000), The "Last Database Backup" timestamp for each database is showing the year 2025.
Our backup system runs incrementally daily and a weekly full. Unfortunately, the timestamp does not seem to be updating as the year is as I guess in the future.
Can anyone advise of a procedure so as I can wipe this time skew date from each of the databases?
Many thanks folks!