I have a job scheduled to backup a production database every night at 10pm. It will run fine for a number of days and then fail with the error:
Msg 3201, Sev 16: Cannot open backup device 'PCSLogisticsDB'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007] The backup set is valid. [SQLSTATE 01000]
When I go back and run it manually it works fine and then will run on it's own for a few more days. I've checked and File System backups aren't running at the time the backup kicks off. Has anyone experienced this?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
I developed a intranet web application that stores about 1000+ new entries into a SQL Server database over the course of about a half hour. This runs 24/7 so its a very busy application. SQL Server is running on a server with a RAID 5 array which runs very good but I want to better guard against data loss. If I run a backup at 8:00 AM and the roof falls on the server crushing and destroying it at 5:00 PM, we just lost thousands of records that were made between 8am and 5pm that cannot be replaced. I was thinking of adding a second server so when an entry is made it would be stored on both servers. The second server would be located at a different location so if 1 gets crushed we could just pick up where we left off and I wouldn't have to update my resume..... Does this sound like a good idea or could anyone recommend a better solution? Thanks in advance.
Is it possible to backup a remote SQL Server database to my local system? It is not working for me as I'm getting error as given below Cannot open backup device 'path'. Device error or device offline Any idea??
Does anyone know how to get SQL Server to see a network drive for backups? Even when trying to create a backup device on a network drive using UNC naming, SQL Server seems to have a problem with accepting it. If you browse for a place to put the device, only the local drive is accessible.
I am working on a new SQL Server 7.0 system and have gotten to the issue of backing up and restoring the database. I am fairly new to SQL Server 7.0 and I am not sure how good the built in backup/resore is in SQL Server 7.0. The other optioin I have is Seagate Backup Exec which is currently being used for network backups.
Our e-business group will be running SQL Server as a backend and C#, ASP.NET, VB.NET application on the front end. Besides my enternal backups of user data on the database. What would you suggest for the Network team to backup and how offen. This is for productions. I usally get a full drive dump on the Dev network daily appended,and weekly archived. Any suggestion for production.
I'm looking for an opinion on an architecture I'm thinking of rolling out.I have a HA cluster with 3 nodes:
Primary -- Main SQL Server Failover -- Passive node, only used in DR scenario Reporting -- Read Intent only queries directed here, SSRS.
I am pondering with the idea of balancing the backups out between 2 nodes as follow:
Primary: Full backup weekly, daily incremental. Reporting: Log backups every 5 minutes.
I have tested that I can indeed restore the logs from the secondary node using the Full and Diff from the other server, no worries there.My concern is what will happen to the logs on the primary and fail-over server. Will they just grow, grow and grow?
I'm trying to create a backup set which maintains only a fixed number ofdays. As such, I've got the following script:BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD,NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT,RETAINDAYS=5DECLARE @i INTselect @i=position from msdb.backupset where database name='mydb'and type!='F' amd backup set id=(select max(backupset set id) frommsdb.backupset where database name='mydb')RESTORE VERIFYONLY FROM [mydb Backup Set] WITH FILE=@iThis script was created automatically, but I added the RETAINDAYS bit. Ionly want the past five days of backups available, but when I check thelist, I've got backups dating back to December 2003! The older backupsare correctly being marked as expired, but they are still on the list!How can I purge the list to only have the last five days of backups?Thanks in advanceJohnny
Can anyone please confirm if SQL Server 7 allows differential or indeed transaction logs backups? I noticed in Enterprise Manager that while trying to configure a backup for the Master database, only complete was highlighted and the other types including file and filegroup was greyed out....
we are looking into a new tape drive and they say it is NT compatable and that its built around NT. My question the backup that is build in to sql 7.0 is it built around NT. I would think so since its a microsoft product
I am looking for the best method to backup SQL Server databases. Currently we are running a dump database statement to disk and backing up the files to tape through Arcserve.
One problem that I am having is the statement to dump the database. I would like to retain the dump for at least three days and be able to restore the database from any one of those three days. My current statement is: "DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP"
but, every other day I receive the message from SQL executive: "Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)"
What am I doing wrong? Any suggestions?
P.S.
Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but the backup files still remain on the disk and I have to delete them every week.
How can i make sure that i dont have any data loss in the event of DB crash. I take daily full database backup dump to the disk and every 4 hrs transaction log dump.
What shoud be the backup strategy to get 100 % database from the backup/ to get in point in time receovery.
For some reason my backups (Transaction log and full backups) have started failing. When looking at the job histoy for the step that failed it reports:
*****(0.011 MB/sec). [SQLSTATE 01000] (Message 3014) Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.*****
I have tried running the SQL for the step manually and I get:
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. Server: Msg 3013, Level 16, State 1, Line 36 Backup or restore operation terminating abnormally.
The step is only determining what day of the week it is and putting it in to a '@bkupday' variable then: "BACKUP LOG [model] TO @bkupday WITH NOINIT , NOUNLOAD , NAME = N'Model Log Backup', NOSKIP , STATS = 10, DESCRIPTION = N'Model log backup', NOFORMAT" for each database and log.
This had been working fine, but for some reason has now started playing up. (trying to workout wether this ties in with when I started merge replication of one of the DBs)
Any one got any ideas as to what might be causing this as it's happened for a couple of nights now?
Has anyone had experience using ‘VERITAS Backup Exec For Windows Servers Agent for MS SQL Server’ to run backup jobs? If so, what are the pros and cons?
I have always preferred using SQL Server Agent (not the maintenance plans) to run backup jobs and don’t know why anyone would want to use 3rd party software to do something SQL Server Agent does perfectly. Any advice?
If I understand correctly I cannot do table & databse backups and restores or exports & imports as with Enterprise Manager in SQL 2000 because I have the lowest level version of the Studio Express Manager. Is that right?
Can anyone tell me what I need to do to regain that funtionality? It will be used for managing local (on my pc) and remote databases.
If it involves downloading and installing a different Studio version:
1. which one would that be? 2. is there a cost? 3. do I need to UNINSTALL my current version? 4. if so, how do I preserve my existing local databases (since there's no backup capability)
Not sure if there is anyway to do hots on sql server .From all I haveheard an EMC can do clones and fractures in order to implement a hotbackup or some kind of it in sql server.Seemingly the DBA has to do something toenable the fracture of the clone . Is anyone familiar with this ?Your input is highly appreciatedMB
I am looking to be able to backup our database automatically on a daily basis. It is for a SQL Server 2005 Express Database. Does the Express edition have automated backups or does it have to be carried out manually?
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. 2. Restore the server with Oct 1 full backup with NORECOVERY option. 3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.
I have this
DECLARE @BackupLoc nvarchar (100) DECLARE @DayOfWeek nvarchar (100) set @BackupLoc = N'D:Backup' set @DayOfWeek = (SELECT DATENAME(dw,GETDATE())) set @BackupLoc = @backuploc + @DayOfWeek
[Code] ....
But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.
My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups.
I am thinking about moving some backups from the local machine to a remote server. Right now, I am using Ola Hallengren script for backups. The script performs checksum on the backup while it's being written on the disk. I am going to move the files using Robocopy utility. How do I check the data integrity once the backups are transferred to the remote server?
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
