We run a script to check the blocking issues every morning. We find a LogBack up job for master db running everyday. But the Backup team doesn't see it on their systems. The backup team says, they are not running any backup job . But this backup job shows up in the process Info. So, we manually kill it everyday. It takes a lot of wait time. We can't find where it comes from ?
Below is the info from the Process Info Page
Process ID : 86
User: ABCAdmistrator
Database: Master
Status: Runnable
Open Transactions: 0
Command: Backup Log
Application: Symantec Backup Exec for Windows
Wait Time: 42600844
WaitType: NetworkIO
CPU : 0
Physical IO: 12
memory Used: 1
Login Time: 5/19/2008 10:04:53 PM
Host: XYZ
Network Library : LPC
Network Address: 0015C58947C3
Blocked by : 0
Blocking : 0
This is the info from the Process details
BACKUP LOG [abc_xyc] TO virtual_device = 'abc_xyz_00__bfbdb0fa_9b94_4392_8ce0_0719ad2a00c5_ ' WITH NO_TRUNCATE
Master DB Properties
Size: 71MB
Space Available : 17MB
No of users: 49
Data File location: c:ProgramFilesMicrosoftSQLserverMSSQLdatamast er.mdf
Automatically grow file (enabled) 10%
Transaction Log File: c:ProgramFilesMicrosoftSQLserverMSSQLdatamast log.ldf
Automatically grow file (enabled) 10%
Reocovery Model (Master): Simple
Auto update stats, and Auto create stats enabled.
Please let me know if any one knows what the problem was??
im performing a backup on the master and i receive this error "backup database permission denied in database master" anyone input will be appreaciated.
Environment: 2 different SQL 2000 servers both with service Pack 1 installed. OS is NT5/Windows 2000 server. Dell 4300 dual processor Servers.
Problem: When I create a maintenance plan to backup the system databases only the model will be backed up. (The model & the msdb are skipped). This occurs whether I select the 'System Databases Only' option, or whether I explicitly select each individual database (from the first page of the Create New Maint Plan Wizard). I can back up the master and msdb manually. But, I cannot get it done through creating a maintenance plan. I find nothing in the SQL Errorlog to explain why this is failing.
Do you have a solution? Even any suggestions? Most appreciated!
Mark Blackburn Consultant to California Department of Parks & Rec Sacramento, CA mblackburn@parks.ca.gov -or- mark_blackburn@yahoo.com
Currently, I have set up maintenance plan to backup all the databases and I am only the most recent copies. This works very good for me on most of the databases. However, I am having a potential problem with backing up master database.
Based on what I know, I cannot perform a restore on master database. So I am wondering if anyone is able to advise me on a good method to backup the master database and restore it when it needs to be.
I am trying to replicate our production SQL 6.5 environment on an isolated network for Y2K testing. I have installed SQL 6.5 on the test server, then ran sp_help_revdatabase on the production server to generate scripts for recreating the databases for testing. I used a zip drive to move dumps of the user databases from production to test. I now need to move over data from the master db, to recreate security and logins. But I don't think I can simply do a dump and restore, as I am not creating copies of all the production databases from our production server, just a few of them. Does anyone have a suggestion for a procedure to replicate the info I need from our production master database? Am I correct that restoring a dump of our production master database to the Y2K environment would be inappropriate?
Please advise.
Thanks,
Fred Smith fsmith@physio-control.com Physio-Control Corp.
Okay. I changed the times that the transaction logs are backed up, via the built in maintenance schedule. It was then that I started to get failures only on the Transaction log backup for the master. The error that I get from the history log is Backup can not be performed on this database. This sub task is ignored. If I look in the file that is saved to disk :
Starting maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:00 Backup can not be performed on database 'master'. This sub task is ignored.
End of maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:31 SQLMAINT.EXE Process Exit Code: 1 (Failed)
I changed the backup back to its original time as this was the only change made. This has not resolved the problem. As I am new to SQL and still finding my feet All the other SQL maintenance plans that I changed are working fine.
Version: 10.00.1600 (I believe the first 2008 release)
I made a backup from the master, model and msdb system tables. Now I travelled to a different country, and I only have the backups and would like to access them. I was able to restore the master db to a new installation of mssql but, it wont start. I do know why: as I read after I quickly realised that master backups take the accounts, serverID, groups, directory structure, and lots of other things.Basically I have no chance of doing a master restore. The question here: Is there any way of getting my data out from the master backup file (thats where my stuff is)? I'm thinking of "restoring" it to a separate non-master db, table, anything. I know now that I did the most stupidest thing ever, but believe me I don't want the last half years work to just disappear.
I wish to restore master database which was located on "e:" on my server. the restored copy is residing on "f:" when i restore the backup and restart, the server does not start.
is there any way to avoid this and perform the restoration succesfully. or the process followed by me is wrong/incomplete.
I have my master database with 10 MB datafile and 4 MB log file,But my backup file for the master DB is 11 GB in size.How could it possible. Can i delete that backup and create a newone... Thanks.
Hello, My master database (MS SQL 2005) has simple recovery model, however, when performing diff backup of "all databases", I am getting the following error:
Executing the query "BACKUP DATABASE [master] TO DISK = N'X:\Database Backups\diff backups\master\master_backup_200803251235.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'master_backup_20080325123514', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I choose to back up only "All user databases", then it performs just fine. Why I cannot run diff backup of master?
Newbie to backups here. I'm trying to setup a Maintenence Plan for backing up the system tables. For some reason the Differential backup on the Master table is failing.
Can you NOT run differential backups on the Master table?
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table. Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
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?
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files. Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database? I can then attach the .mdf files if I need to restore the database to another server. Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
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?
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin] GO IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL DROP PROC usp_DeleteBackup GO
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
I 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...
This is probably a simple question but I have to ask it anyway. When backing up I can backup to a file on my local drive but I also can create a device to the same location on my local drive. Is this doing the same thing. If I so desire to backup to the local drive(bear with me) what is the difference between creating a device and a file called mybackup or just choosing to backup to a file called mybackup? Should I always create a device? I know these are dumb question but....
How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run : RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one?? Thanks. Shalini.
