Unable To Take Backup
Jun 19, 2007
Hi
I have Database in SQL-Server2000 size is 1.37 GB i am tryn to take backup followin are the problems:-
1. Maintainence Plan for Backup Fails for DB
2. Jobs fails for Backup
--> I goto SQL Server Logs Error message is Operating system error 23(Data error (cyclic redundancy check).)
--> Now i am tryin Backup using command :-
Backup Database 'DB1' To Disk ='Path' followin error occurs:-
"Nonrecoverable I/O error occurred on file"
--> Now i am doing back-up directly from Enterprise Manager following error message occurs same error comes
Whats surprising Jobs, maintainence plan, Backup for other database is working perfectly, i checked HDD doesnt have any bad blocks etc. i have even changed path for Backups but still only for these database it gives problem
With Regards
View 2 Replies
ADVERTISEMENT
Apr 24, 2008
i am using sql 2005,i want to backup all the databases when i try to schedule a maintenance plan , i dont get all the databases listed event though i authenticate as SA.
View 6 Replies
View Related
Jan 2, 2001
Hi all,
On my server running SQL7.0 with SP2, I try to backup my database to a file.
All works well if I run the backup manually, but when I use the schedule option, when I close the Backup properties window and open the window again, the schedule is gone...
Does anyone of you know how to get this working?
Thanks in advance,
Camiel
View 2 Replies
View Related
May 31, 2007
Frns
Currently i am working with Sql Server 2005 and i am taking daily backups which is going
append same backup file <DB_NAME>.BAK.
Now i am trying to restore this bkp with a new database name and with 2 days before date
image.When i am trying to do that, sql server is not showing the versions of the backups.
Why? is there any reason for that?
Regards,
franky
View 1 Replies
View Related
Apr 22, 2006
Hi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same
name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
View 3 Replies
View Related
Apr 23, 2006
Hi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
View 1 Replies
View Related
Dec 17, 2007
i have taken full database backup on tape drive one week ago,by automated scripts ,now when i check the maintenance plan history , it says the task failed and gives a message that unable to open the \. ape0 ,with error
([Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '\. ape0'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
)
View 4 Replies
View Related
Sep 21, 2007
I am trying to restore a SQL Server 2005 database backup file.The size of the database backup is 5.51 GB. I specify the source as a device and then provide the path of the .bak file and press OK i get the error as follows
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The media family on device 'G:ITWeb DB BkupDB BkupNew BkupWSS_Content_ITWEB.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Please let me know how we can solve this issue.
Satish
View 5 Replies
View Related
Mar 16, 2007
I've been struggling with this for a few days now, so I guess it's time to ask for help. I'm working on a VB Express 2005 Single form windows application that uses a SQL Express 2005 two table data base. I can read and write to the database with no problems. I just can't back it up. I haven't tried restores yet. I created the database in SQL Server Management Studio Express and then connected to it from the VB Express application.
My.Settings.UsersConnectionString is
Data Source=.sqlexpress;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataUsers.mdf";Integrated Security=True;User Instance=True
I used the following code to establish a connection to the server that is the userinstance
Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UsersConnectionString)
Dim ServerConnection As ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(SqlConnection)
Dim srv As Server = New Server(ServerConnection)
I am able to confirm the connection to the UserInstance via a message box that displays the instance name
MsgBox(srv.InstanceName.ToString)
Then this code follows:
Dim db As Database
db = srv.Databases("USERS")
Dim bk As New Backup
'Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database
bk.BackupSetDescription = "Full backup of Users"
bk.BackupSetName = "Users Backup"
bk.Database = "USERS"
'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
Dim bdi As BackupDeviceItem
bdi = New BackupDeviceItem(AppDir & "Test_Full_Backup1.bak", DeviceType.File)
'Add the device to the Backup object.
bk.Devices.Add(bdi)
bk.Initialize = True
'Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = False
'Set the expiration date.
Dim backupdate As New Date
backupdate = New Date(2008, 10, 5)
bk.ExpirationDate = backupdate
'Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate
'Run SqlBackup to perform the full database backup on the instance of SQL Server.
bk.SqlBackup(srv)
'Inform the user that the backup has been completed.
MsgBox("Full Backup complete.")
Unfortunately, the line bk.SLQBackup(srv) errors off advising that - Microsoft.SqlServer.Management.Common.ExecutionFailureException {"An exception occurred while executing a Transact-SQL statement or batch."} Microsoft.SqlServer.Management.Common.ExecutionFailureException
and that
+ InnerException {"Could not locate entry in sysdatabases for database 'USERS'. No entry found with that name. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally."} System.Exception
So I went to SQL Server Management Studio Express and ran the query
use master
select * from sysdatabases
Among other lines the query returned
USERS 5 0x010500000000000515000000358A021AFA4F0C2F828BA628AD8A0200 0 1073807369 1627389952 2007-03-16 10:04:42.320 1900-01-01 00:00:00.000 0 90 C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataUsers.mdf NULL
so it appears to me that the entry for database USERS is really there.
Any ideas as to what I need to do so that I can add a program feature to backup the data are greatly appreciated.
Thank you,
MF
View 25 Replies
View Related
May 13, 2008
Hello,
My name is Elizabeth and I'm a SQL newbie.
So we do SQL 2005 Database backup daily. At one point, we have to restore the backup. However, we cannot restore it whatsoever; it keeps saying that the database is in use.
It is actually a SQL Database for a CRM Server. I stop the internet service, so CRM or any other instances cannot pull up information from the SQL Database. I try running sp_who and killing the processes that are still using the database, but those processes keep coming back again.
Here is the error that I keep getting.
Could anyone please help me on this one? Thanks in advanced!
-Elizabeth K.
View 4 Replies
View Related
May 28, 2014
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
View 4 Replies
View Related
Oct 5, 2007
I am using sql server 2005 and unable to restore backup file(.bak) file when i ry to restore it to other database it shows following error
restore failed for server (microsoft.sqlserver.smo)
additional information:
system.data.sqlclient.sqlerror : the media set has 2 media families but only 1 are provided.All members nust be provided.(microsoft.sqlserver.smo)
View 1 Replies
View Related
Mar 12, 2007
I get the following error in a log file created in the osql command:
Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
Cannot open backup device 'x:Servername_master_sqlbu_200703101930.bkf'.
Device error or device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
BACKUP DATABASE is terminating abnormally.
The device is established in a CMD file right before the osql command is started that starts my stored procedure. The CMD in this file is:
for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i
:loop
if exist x: net use x: /del
net use x: \192.168.%SUBNET%.1ackup
if not ERRORLEVEL 1 (
goto continue
) else (
echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%LogFiles\%Computername%.log"
sleep 60
goto loop
)
:continue
echo IP ADDRESS OBTAINED
Echo delete old log file if it exists
if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log
Echo Rename log file to old.log
rename %3\%computername%_kaman_full_sqldb_backup.log %computername%_kaman_full_sqldb_backup_old.log
echo backup SQL Databases on server will start now
osql -E -n -d %1 -i %2kaman_full_sqldb_backup.sql -h-1 -o %3\%Computername%_kaman_full_sqldb_backup.log
In the stored procedure I try to use the x: drive and that does not work. I have tried obtaining the \192.168.x.x address and that only works on some of my servers. All are running SQL 2000, some are using Win2K and Win2003. It does not seem to matter. One of them that is using Win2003 only fails occationally.
I notice when I do a
exec master..xp_cmdshell 'x:'
The system cannot find the drive specified.
Yet, when I go to the server there is an x drive.
I am using the sqlserv user to run the job and that use is an administrator on the local machine. This is a virtual machine. We add the X before the osql and drop it after the command finishes.
Any help would be appreciated. Thanks, dbmsql
View 6 Replies
View Related
Oct 6, 2007
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
View 3 Replies
View Related
Dec 5, 2014
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
View 9 Replies
View Related
Jan 31, 2008
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
View 3 Replies
View Related
Aug 23, 2013
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?
View 9 Replies
View Related
Feb 18, 2015
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.
View 3 Replies
View Related
Feb 9, 2004
Hi,
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?
Thanks in advance
regards,
M.Y. Yap
View 2 Replies
View Related
Feb 19, 2015
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?
View 2 Replies
View Related
Aug 3, 2015
Need to restore database,here's the scenario:
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.
View 8 Replies
View Related
Dec 28, 2007
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.
View 4 Replies
View Related
Jul 15, 2015
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 Related
Mar 13, 2015
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
[Code] .....
View 2 Replies
View Related
Jul 11, 2007
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?
View 11 Replies
View Related
Dec 29, 2014
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?
View 4 Replies
View Related
Dec 5, 2006
What is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
View 3 Replies
View Related
Nov 16, 2015
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...
View 13 Replies
View Related
May 10, 2001
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....
Jason
View 1 Replies
View Related
Dec 7, 2001
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.
View 2 Replies
View Related
Nov 29, 2007
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
Marc
View 2 Replies
View Related
Jul 19, 2007
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.
View 3 Replies
View Related
Apr 15, 2008
I have only one tape drive.
My server is using the SQL server 2005 backup tool and arcserve backup tool to backup different files .
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use SQL server 2005 backup tool.
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use arcserve backup tool.
It seems that the tape device resource is held by softwares even the tape drive is not in use.
What should I do to make the tape drive shared by different softwares?
Regards,
Manuel
View 2 Replies
View Related
