The database is SQL Server 2000. Recover Plan = 'Simple'. Maintenance Plan always fails when DBCC tries to put database in single-user mode so it can do the backup, if there is a user has a connection to it from EssBase. No queries running - just a connection. How can I get SQL Server to automatically kill the connections so it can do the backup?
View 2 RepliesHi everyone,
I am running SQL 2000 SP3. I have one maintenence plan that is backing up one database and that is working fine. I am trying to create another maintenence plan for another database but the job does not execute and it gives me errors. Both SQL server and the agent uses 'local system account' to run the service. I have tried changing this to a domain account to see if it fixes anything. Also on the jobs, the job owner is a domain account. Below are two erros I am getting when running the jobs.
Error1:
BackupDiskFile::CreateMedia: Backup device 'D:SQL Backups....mybackupfile.bak' failed to create. Operating system error = 3(The system cannot find the path specified.).
Error2:
BACKUP failed to complete the command BACKUP DATABASE [Database_Name] TO DISK = N'D:SQL Backups....mybackupfile.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Any help would be appreciated.
Thanks.
I just setup two new SQL 2005 Stnd x64bit servers for a customer. The two I setup a few weeks ago work great! One the two new servers, I get an error message when I try to Create a Maintenance Plan or use the Maint Plan Wizard.
The error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server component that is not installed on the remote computer. To proceed, install SQL Server 2005 Management Tools on the remote computer, and then try again. For more information, see "How to: Install SQL Server
2005 (Setup)" in SQL Server 2005 Books Online, or find the article on MSDN at http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)
For help, click: http://go.microsoft.com/fwlink/?LinkID=57083
Everything else with the server seems to be working without problems.
Any ideas?
Thanks
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
Created a maintenance plan to backup my sharepoint databases.
When I execute it the following error occurs:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI)
- Execute maintenance plan. SharePointBackUP (Error)
Messages
* Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
------------------------------
ADDITIONAL INFORMATION:
Job 'SharePointBackUP.Backup_SharePoint' failed. (SqlManagerUI)
When checking the Maintenance PlansharePointBackUP log it is empty!
Under Job History I thinks this:
Date 4/18/2008 12:55:35 PM
Log Job History (SharePointBackUP.Backup_SharePoint)
Step ID 1
Server DESD7
Job Name SharePointBackUP.Backup_SharePoint
Step Name Backup_SharePoint
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error.
Under SQL Agent there are no entries!
So what bloody log am I suppose to be checking?! This is very frustrating.
I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
Hi Guys!
I am trying to create a 'scheduled back up database task'maintenance plan which has an option to 'overwrite backups' but with also the "Create a backup file for every database" option checked. Does anyone know how to do this?
Thanks Guys.
Heya all,
Sorry if this has been posted elsewhere, etc., please point me in the right direction if it has 'cos I couldn't find it!
Right, we have a mirrored database with full safety and a witness for automatic fail over, all works fine, very impressed with it. Now I need to backup the database involved and this is where I could do with some help and answers and/or tips.
As the mirror database is off-line/recovering it seems you can't back that one up, but I'd like to have something that tries to back it up for if/when it fails over and becomes the primary. The solution I've used for now is to write a small .Net application that uses the client side fail over connection string (Data Source=Server1;Failover Partner=Server2) so that it connects to whatever system is currently the primary, and then issues the relevant 'BACKUP xxx' statements to backup the database.
This applications is launched from a windows scheduled task job on the hour (or near to it), every hour. At 06:00 it does a full backup, at 12:00, 18:00 and 00:00 it does a differential backup, and all other times it does a transaction log backup.
This all seems to work fine so far, and generates all the relevant backup files to a share on another server.
So, my main question is; does this look like a good plan? Am I missing some really simple wizard or button that would backup the relevant database from whatever server is up?
Secondly, are the backups from each server interchangeable as they're in a mirrored configuration? That is, for example, the backup application (on the hour) connects to Server1 does a full back up, followed later by a log backup. Server1 then dies, so the next time the backup application runs it connects to Server2 and because of the current time does a log backup from Server2. If we had to restore from backups, could we use the full backup and log from Server1, followed by the log from Server2? Otherwise I'll guess I'll need to modify the logic for the backup application to detect it's failed over, and maybe do a full backup on Server2, or something like that.
Anyway, thanks for any help/advice/tips,
Gareth/OhGod
If I try to backup of database in sql 2012 with t-Sql as follows:
Backup database db1
to disk='c:myfullbkup.bak' with init
go
This code works absolutely fine.
But if I try to take backup through SSMS -Task -Backup in object explorer and gave the same path of c: drive or any other folder outside sql default "Backup" folder.It gives error as access denied.This was not the scene in Sql server 2008.
Hello,
I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.
I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..
select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc
select @message = @message + char(13) + Char(13) + 'Backup Status' + Char(13)
DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name
OPEN GetBackup
FETCH NEXT FROM GetBackup INTO @dbname, @Status
WHILE @@FETCH_STATUS = 0
BEGIN
select @message = @message + @dbname + ' backup up on ' + @Status + Char(13)
FETCH NEXT FROM GetBackup INTO @dbname, @Status
END
Close GetBackup
Deallocate GetBackup
drop table #backup
print @message
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver!',
@type = N'text/html',
@message = @message
Thanks
Hi,
I am administering several SQL Servers running SQL Server 2005 SP2 Build 3042.
I have a common maintenance plan that runs on each of the servers. The maintenance plan runs
fine on all the servers except for one. On the one server the Database Integrity check fails with the following error:
Check Database integrity on Local server connection
Databases: <list of databases>
Include indexes
Task start: 2008-02-21T00:05:42.
Task end: 2008-02-21T00:05:46.
Failed0) Alter failed for Server XYZ
I created a test maintenance plan to just do the integrity check and selected one database only and this also failed with the same error message. I ran this test maintenance plan and configured it for each of the databases in question and it failed each time.
If I run the DBCC manually against the databases they all report fine.
I read some of the post that talked about the Allow Updates? being set incorrectly but that does not apply to my problem since my configured and run values are set to 0.
Does anyone know what the problem could be?
I have a maintenance plan step to perform transaction log backups. There are a few databases that are offline. The backup seems to fail because it tries to backup the offline databases. The backup step is by itself as one of three subplans, although it also fails when it is the only step in a plan with only one subplan.
I have another server that is only on SP1 and it does not have problems handling the offline databases.
Is this an issue with SP2(9.0.3054)?
Hello, everyone:
I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.
Is it true? Can anyone offer official documents?
In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.
Thanks
ZYT
I created a maintenance plan to back up a database. It fails and I get the follow error in the log file.
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
What user is it referring to? What user does a Maintenance plan use to login as? SA on the server has full access to all databases. SAs default database is master and I can read and write to it with the enterprise manager and query analyzer.
All replies appreciated
Hello,
On SQL 2005 with mixed authentication, I am simply trying to create a maintenance plan to backup one single database. When I get to the end of the wizard, the creation process fails with this message:
Create maintenance plan failed().
I installed SQL 2005 using my domain account and the services are running under my account credentials. I am a domain and local admin.
Any ideas?
Regards,
Chris
On our SQL2000 installation on a Windows2000 machine, our sql maintenance jobs run for about a week and then begin to fail with the following error:
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
The logs only indicate failure and the report file is not generated, nor is a maintenance plan history record generated.
When the call to xp_sqlmaint is failing I try it in Qeury Analyzer (QA) and it still fails. I can however still run the SQLMAINT.exe directly from a command prompt without error. It is just the calls to xp_sqlmaint that seem to be failing. If I simply restart SQL Server, the maintenance jobs run fine for a while again. Same success with the calls in QA to xp_sqlmaint. Any ideas on why this begins to fail? It is happening on 3 of 6 database servers running the same build.
Thanks, John.
I created a user db integrity maintenance task. The created job runs once a week. It fails. How do you tell at what point is fails? The history log is not specific about where things go off the rails.
The job errors as follows -
Execution of job "User Integrity check subplan_1 failed. See history log for details
If I run the sql in a query window it goes right through - error free.
USE [AcAspNetDB]
GO
DBCC CHECKDB(N'AcAspNetDB', NOINDEX)
GO
USE [LCCIntranet_Config]
GO
DBCC CHECKDB(N'LCCIntranet_Config', NOINDEX)
GO
USE [library_SSP_Search_DB]
GO
DBCC CHECKDB(N'library_SSP_Search_DB', NOINDEX)
GO
USE [Librarysspdb]
GO
DBCC CHECKDB(N'Librarysspdb', NOINDEX)
GO
USE [seeunity]
GO
DBCC CHECKDB(N'seeunity', NOINDEX)
GO
USE [SharedServicesv2_DB]
GO
DBCC CHECKDB(N'SharedServicesv2_DB', NOINDEX)
GO
USE [SharedServicesv2_Search_DB]
GO
DBCC CHECKDB(N'SharedServicesv2_Search_DB', NOINDEX)
GO
USE [SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d]
GO
DBCC CHECKDB(N'SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d', NOINDEX)
GO
USE [WSS_Content]
GO
DBCC CHECKDB(N'WSS_Content', NOINDEX)
GO
USE [WSS_Content_lccintranet80]
GO
DBCC CHECKDB(N'WSS_Content_lccintranet80', NOINDEX)
GO
USE [WSS_Content_LCCSSPv2]
GO
DBCC CHECKDB(N'WSS_Content_LCCSSPv2', NOINDEX)
GO
USE [WSS_Content_library]
GO
DBCC CHECKDB(N'WSS_Content_library', NOINDEX)
GO
USE [WSS_Content_librarymysite]
GO
DBCC CHECKDB(N'WSS_Content_librarymysite', NOINDEX)
GO
USE [WSS_Content_libraryssp]
GO
DBCC CHECKDB(N'WSS_Content_libraryssp', NOINDEX)
GO
USE [WSS_Content_mysitev2]
GO
DBCC CHECKDB(N'WSS_Content_mysitev2', NOINDEX)
GO
USE [WSS_Search_ISSMOSS]
GO
DBCC CHECKDB(N'WSS_Search_ISSMOSS', NOINDEX)
Anyone had an issue like this?
Regards
Rob
W2k3SP2 running SQL2k5SP2 in mixed mode. Trying to run a daily maintenance plan with a domain admin service account [domainsqlsvc]. This account is having login failure when I try to run the maintenance job. Would like to figure out why this account cannot authenticate with SQL. I believe I have local login denied, but login as a svc and login as a batch job are both enabled in Domain Policy.
What am I missing?
TIA for your help!
-Nick
EDIT: Here is the error data.
Date 11/14/2007 9:36:16 AM
Log SQL Server (Current - 11/14/2007 9:36:00 AM)
Source Logon
Message
Login failed for user 'domainsqlsvc'. [CLIENT: <local machine>]
Error:18456, Severity: 14, State: 16
Hi
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
Any insight is greatly appreciated.
Ever since upgrading SQL Server 2005 to SP2 build 3050 all my maintenance plans which begin with a Check Database Integrity Task are failing! If I remove the task from the plan they all execute properly. If I run the T-SQL equivalent manually it executes properly.
I upgraded the server to build 3054 and rebooted several times and Im still having no luck. What am I doing wrong?
Thanks for your help with this.
-Kenny
Our backups by default go to a network location, but I'd like to modify our maintenance plans to backup to an alternative location if the primary location isn't available. I've setup two Backup Database Tasks where the second one runs only if the first one Fails, and if the second one runs (on first one's failure) it then sends a notification to me so I know this occurred.
The Plan is running as expected, when I simulate a bad path in the first Backup Task the second one runs and the notification is sent, but the Job shows failure. I'd like to show the job as Successful when this occurs since I'm handling the issue and notification within the Plan, but I'm unable to find out how. I've set FailParentOnFailure to False on the Plan and I've changed the MaximumErrorCount to 2 with the assumption that this would work, but neither didn't.
Also I'm running into this in both SQL 2008 and SQL 2012.
In Sql 7.0 you cannot perform a log backup with NO_TRUNCATE when the primary db file is missing (ref. Q218739). I'm not updated with respect to Sql2000 and was wondering if this remains an issue in that version too? Anybody know?
(the solution being to have a separate DEFAULT file group and store the primary file on the same disk as the log).
Hello,
In the process of migrating a logshipped database (in non recovery mode) from SQL2000 SP4 to SQL2005 SP2, I got the following error (sorry for the french translation!):
---
Conversion de la base de données 'AdStatistiques' de la version 539 à la version actuelle 611.
Base de données 'AdStatistiques' exécutant l'étape de mise à jour de la version 539 à la version 551.
Msg 601, Niveau 12, Ătat 3, Ligne 1
Impossible de poursuivre l'analyse avec NOLOCK car les données ont été déplacées.
Msg 3167, Niveau 16, Ătat 1, Ligne 1
RESTORE n'a pas pu démarrer la base de données 'adstatistiques'.
Msg 3013, Niveau 16, Ătat 1, Ligne 1
RESTORE DATABASE s'est terminé anormalement.
---
Of course, the restore operation performs well when the destination is SQL2000. I suspect the integrity checker of SQL2005 to be less permissive, hence I run a checkdb on the original DB, which raise several errors, which are quite difficult for me to analyse:
[Sorry, it's in french again]
----
Serveur : Msg 8929, Niveau 16, Ătat 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620702208 possédé par l'enregistrement de données identifié par RID = (1:1385:13) id = 1902629821 and indid = 1.
Serveur : Msg 8929, Niveau 16, Ătat 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620767744 possédé par l'enregistrement de données identifié par RID = (1:1385:14) id = 1902629821 and indid = 2.
Serveur : Msg 8929, Niveau 16, Ătat 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620833280 possédé par l'enregistrement de données identifié par RID = (1:1385:15) id = 1902629821 and indid = 3.
Serveur : Msg 8929, Niveau 16, Ătat 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620898816 possédé par l'enregistrement de données identifié par RID = (1:1385:16) id = 1902629821 and indid = 4.
Serveur : Msg 8929, Niveau 16, Ătat 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 26594574336 possédé par l'enregistrement de données identifié par RID = (1:7153632:27) id = 1031010754 and indid = 5.
Serveur : Msg 8961, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 2. Le nĆud text, ntext ou image Ă la page (1:1974), slot 0, texte ID = 26594574336 ne correspond pas Ă sa rĂ©fĂ©rence dans la page (1:191177), slot 10.
Serveur : Msg 8974, Niveau 16, Ătat 1, Ligne 1
NĆud texte rĂ©fĂ©rencĂ© par plusieurs nĆuds. L'objet ID = 2, page de nĆud text, ntext ou image (1:1974), slot 0, texte ID = 26594574336 est pointĂ© par la page (1:191177), slot 10 et par la page (1:38504), slot 0.
Serveur : Msg 8965, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 2. Le nĆud text, ntext ou image Ă la page (1:1111184), slot 0, texte ID = 15620767744 est rĂ©fĂ©rencĂ© par la page (1:191177), slot 11, mais n'a pas Ă©tĂ© vu Ă l'analyse.
Serveur : Msg 8965, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 2. Le nĆud text, ntext ou image Ă la page (1:1111185), slot 0, texte ID = 15620833280 est rĂ©fĂ©rencĂ© par la page (1:191177), slot 12, mais n'a pas Ă©tĂ© vu Ă l'analyse.
RĂ©sultats DBCC pour 'AdStatistiques'.
RĂ©sultats DBCC pour 'sysobjects'.
Il y a 388 lignes dans 9 pages pour l'objet 'sysobjects'.
RĂ©sultats DBCC pour 'sysindexes'.
Serveur : Msg 8965, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 2. Le nĆud text, ntext ou image Ă la page (1:1111186), slot 1, texte ID = 15620898816 est rĂ©fĂ©rencĂ© par la page (1:1385), slot 16, mais n'a pas Ă©tĂ© vu Ă l'analyse.
Il y a 437 lignes dans 26 pages pour l'objet 'sysindexes'.
CHECKDB a trouvé 0 erreurs d'allocation et 10 erreurs de cohérence dans la table 'sysindexes' (objet ID = 2).
[SNIP]
RĂ©sultats DBCC pour 'AdStatPageHeure_OLD'.
Serveur : Msg 8977, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 1902629821, index ID = 1. Le nĆud parent pour la page (1:1043307) n'a pas Ă©tĂ© rencontrĂ©.
Serveur : Msg 8977, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 1902629821, index ID = 1. Le nĆud parent pour la page (1:1043308) n'a pas Ă©tĂ© rencontrĂ©.
Serveur : Msg 8977, Niveau 16, Ătat 1, Ligne 1
Erreur de table : Objet ID = 1902629821, index ID = 1. Le nĆud parent pour la page (1:1043309) n'a pas Ă©tĂ© rencontrĂ©.
Serveur : Msg 8977, Niveau 16, Ătat 1, Ligne 1
[SNIP, several errors pretty similar here]
-Trop d'erreurs trouvées (201) pour l'objet ID = 1902629821. Pour voir tous les messages d'erreurs, relancez l'instruction avec 'WITH ALL_ERRORMSGS'.
Il y a 21062117 lignes dans 72062 pages pour l'objet 'AdStatPageHeure_OLD'.
CHECKDB a trouvé 0 erreurs d'allocation et 72063 erreurs de cohérence dans la table 'AdStatPageHeure_OLD' (objet ID = 1902629821).
---
Of course, I'd like to delete the AdStatPageHeure_OLD table but I get a cryptic error message when doing that, and I can't remove the indexes that, according to some other posts I browsed, could explain the behavior:
---
La page (1:1111186), slot 1 pour le noeud text, ntext ou image n'existe pas.
Page (1:1111186), slot 1 for text, ntext, or image node does not exist. (Microsoft SQL Server, Erreur : 7105)
Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=7105&LinkId=20476
---
Is there any easy option you can see to operate the transfer, since the backups I have from the DB already contain the data corruption?
Thanks a lot for any hint you could provide,
--
Julien
Hi all
i have taken backup of a database from SQL Server2005
can i Restore it on SQL Server2000 server?
Please help me
Thanks
hi,
i have six files ( 3 MDF and 3 LDF) from a backup of a MS SQL 2000 server.
I installed MS SQL 2005 and wanted to Restore these Dataset. but they cant be accessed. doesnt SQL 2005 support these files ?
How can i use these files to access them with SQL 2005 ?
Thx,
I am restoring a backup from SQL2000 Database to 2005 and I get this error :
An exception occured while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
Additional Information: Too many backup devices specified fro backup or restore; only 64 are allowed.
RESTORE HEADEEONLY is terminationg abnormally. (Microsoft SQL Server, Error 3205)
How do fix this? Please assist.
Thanx
I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.
My new plan is looking like this:
1) Run a complete database backup once a day
2) Run Transaction Log backups every hour
My questions are as follows:
1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?
2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.
3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.
Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!
Thanks in advance~
Hi all,
I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.
1. Can I find out if there is any activity on a database so I can delete them off and how?
2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?
Any comments or suggestions are greatly appreciated.
I would like to implement the following backup strategy in SQL Server 2000:
full backup is done once a week
diff backup daily
tran backup every hour
How should I approach this. Should I use a backup device linked to a hard disk file? Should I create a backup device for each type of backup (full, diff, tran) and schedule it. If I use a backup device how do I distinguish each backup for old backups.
thanks
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
I made a backup maintenace plan in sql7.0, but it doesnŽt work. The message included on the event logger is:
SQLServerAgent
SQL Server Scheduled Job
'DB Backup Job for DB Maintenance Plan 'Teste''
(0x219C5CB03C11D4119D8F0020188D7311) -
Status: Failed - Invoked on: 4/17/00 3:25:00 PM - Message:
The job failed. The Job was invoked by Schedule 12 (Schedule 1).
The last step to run was step 1 (Step 1).
And before this message there is another one that can be making something goes wrong:
SQLServerProfiler
Executable: C:WINNTsystem32mmc.exe
Description: Unable_To_Set_RegKey_Val
Source: ........srceventlog.c
Line: 315
Someone can help me telling me the reason that is generating this kind of error and doesnŽt work. I have do the same think in others two server and there it works ok.
Thanks in regards
Sandra Lino
I am doing full backups on 25 databases nightly using the SQL maintenance plan. Recently I have started getting the backup process hung on one database, so the whole plan does not finish. The job shows as executing, but nothing is being done, and nothing has been written to disk for this database. The SQL logs show a DBCC TRACEON 208, but the process it refers to is using another database. The NT logs do not show anything going on either. Additionally the backup process will not die when it is killed. They only way to make it go away is to stop and start the SQL Server. The system is a quad Pentium 450, 2Gig of ram, and plenty of disk space. It is NT SP5, SQL7 SP1. The DB is about 8.2 Gig. There are no other jobs scheduled to run at the time the backups run. Does anyone have any ideas what would be causeing the process to hang? Also what is a trace flag 208, it is not listed in BOL. Thanks for your help.
View 1 Replies View RelatedI have a maintenance plan that I use to backup some of my databases and transaction logs. I have "Check Database Integrity" checked. If the database is being used by anyone when the backups are being performed, the check issues an error because the DB cannot be put into single user mode and then skips the backup. In the Plan, I have unchecked the box for "Perform these checks before backing up the database or transaction file" and apply the change. When I go back into the plan, the option is checked again. How can I get around this problem?
Thanks,
Ken