I've recently set up Backup Exec on our SQL Server to backup our databases to DLT tape everynight. However when backing up the mdf and log files these are skipped because its says they are in use. However if you look in the Current Activity they are not in use. Plse could someone help/advise.
View 1 RepliesI'm reviewing our Backup Stategy. Can anyone advise me what advantage there is to using Backup Exec instead if just backing up the database and transaction logs straight to a DLT tape. Thanking you in advance.
View 1 Replies View RelatedWe've been using BackupExec for a while to back up our SQL 2000 databases, but we've suddenly encountered a problem while trying to back up some of them. It seems that it won't back up databases that have a recovery model of "simple". Our Network Admin can't find anything in BackupExec that references Recovery Model, and we haven't changed the recovery model on the databases since they were created .....
Here's the error from the event log ... any ideas ?
An error occurred while executing the following query: "BACKUP DATABASE [TI] FILEGROUP='PRIMARY' TO virtual_device = 'BackupExecSqlAgent_PRIMARY_00'".
On server: "SQLDB-01".
SQL error number: "1070".
SQL error message: "The statement BACKUP DATABASE...FILE=<name> is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
".
Hi
I currently have Backup Exec 11 w/ SQL Agent backing up my databases on a daily full, and 4 hour incremental basis. I want to configure log shipping as an additional level fault tolerance for my data. I have a few test databases setup with the log shipping and they are working correctly. But my question is related to Backup Exec.
Now that I have logs going to a second SQL server how will Backup Exec play with the primary server logs? Do I need to configure Backup Exec in a certain way, say do not truncate logs, etc? Or can I just keep the backups going as I have them and log shipping will just work at the same time.
TIA
Michael HamSeattle, WA
We currently use CA ArcServe (ArcServe 6.5 Enterprise and Single Server Editions) to backup our Windows NT files and MS-SQL Server databases. We have experienced significant reliability issues with ArcServe. Many times we have found ourselves rebuilding a corrupt ArcServe Job (ArcServe’s backup schedule) database. One of our NT server occasionally NT bug checks when ArcServe is performing backups. Occasionally ArcServe Jobs incorrectly reschedule themselves. Sometimes the Jobs do not complete but stay executing, not performing any work, and to cancel them may require a lot of effort. The ArcServe job DB repair utility generally does not work. The user interface is lacking. For example, the job scheduling options are very limited. CA tech support for this product has been poor. Because we have issues with ArcServe stability we are now evaluating Veritas (formally Seagate) Backup Exec for NT. What are other people’s experiences with these 2 products?
View 1 Replies View RelatedI'm going back and forth on an issue and was looking for some outside observations. I have over a dozen SQL NT servers, both 6.5 and 7.0. On some servers I'm dumping the databases and backing up the dumps with Veritas BackupExec, on 4 (two SQL 6.5, two v7) I'm backing up with the Veritas SQL Backup Agent.
Obviously, if you don't have room to dump a database, you must use the backup agent. That is the case on one of my servers and becoming so on a second. But my personal preference is to do the dump/backup.
(as a side note, one server is not backing up correctly with the backup agent but ultimately that box will require it due to DB growth, so it is something that I have to resolve)
I like the dump system for a few reasons. I find it easier to load from a dump, particularly a single table. Likewise, I find it easier to copy a database by loading from a dump rather than going from a backup, but that's mainly because of BackupExec being a little bit strange on redirecting restores.
Here's my clincher. I find restores via the backup agent to be ridiculously slow. Let's say I have a 5gig DB that has 1.5gig in it. The dump size will probably be somewhere below 2gig. The restore via backup agent carefully writes the entire 5gig even though 3.5g of that is empty. This takes a lot of time. Add to that the "post-restore DBCC". I had such a restore take something on the order of 13 HOURS which, needless to say, conflicted with my nightly DBCC's and backups.
OK. End of rant. Any suggestions or thoughts on the subject?
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?
View 3 Replies View Related
Hi,
I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?
Any suggestions?
Thanks,
Dale
What backups options do you have if you do not have a tape drive connected to your SQL 7.0 Server ? Can you run a SQL Maintenance Plan and backup the databases to a remote server. If not, can ARCServe 2000 backup the SQL backups to another server with a tape drive ?
View 1 Replies View RelatedI'm having trouble backing up two databases to tape. Should I be using a backup_device?
have a job defined with the following in it:
backup database test1
TO TAPE = '.Tape0'
with ...
go
backup database test2
TO TAPE = '.Tape0'
with ...
go
where should the init go?
I've set up several backups in the past using the backup screen. I've also set up a Maintenance Plan to back up my database using MyDbName* where the * is the familiar suffix automatically generated by the system when the backup runs every night.
The trouble is that, even though I've deleted old maintenance plans and scheduled backups, they're not going away. Every morning, I see new nightly backups of old backups etc., in the backup folder. Furthermore, the maintenance plan I want to backup my database isn't creating a backup file.
How can I make the old backups stop running, and make sure the new backup runs? I've gone through everything and can't seem to find any residue of these old scheduled backups--yet they still run! Can anyone help?
We are having problems with our tape drives so we are attempting to back up our databases to disk. I have a whole server on the network I could use for this. When I use the backup that comes with SQL 7.0 it won't allow me to choose a network drive. It looks like it must be a local drive. Is there a way around this so I could back it up to this other server. I jave 32 gig available on the server which would be plenty. We don't have enough disk space on the local drives to back it up. We have been having problems with our current tape drive company Exabyte. Does anyone have any recommendations. We are thinking of switching to another company.
View 3 Replies View RelatedI currenntly backup several databases to thier own unique device. Each backup is executed as a full backup (databases are small)and they are set to expire after 7 days. I checked my devices after 7 days and they continue to grow in size - is this right? I expected the device size to hold constant once I had a rolling seven days of backup (The databases don't change in size from day to day) Any ideas?
View 2 Replies View RelatedI'm using the database maintenance plans to backup my databases. We are backing them up to tape. Is there a way within the database maintenance plan to tell it to format the tape instead of appending it to the tape.
View 1 Replies View RelatedI couldn't restore a SQL 6.5 Backup in my SQL 7.0, because it is generating an error in SQl 7.0 that shows an Incompatible types.(media)
How can I restore a SQl 6.5 Backup into my new SQL 7.0 ??? Please Help me, because I'm a Beginner in SQL 7.0.
Thanks in Advance.
I am trying to make sure I have my backups scheduled correctly and in the correct spot. We have a tape drive on the server which is backing up the databases each night. The logs I have them currently being backp up once an hour to disk. My question is would it better to have the logs back up to the tape drive or to disk. The server where I'm backing up the logs to has 2 drive C and D. Where D contains the data and C contains the application/program files. I'm assuming I wouldn't want it on D since if there was a crash with that drive and we lost the data we wouldn't be able to get the logs for that day so it would be better to put it on C. What are most people doing to backup there logs. At the end of the day after a full backup I clear the device that contains the log backups. It this good practice?
Stacy
How do you backup to a remote drive?
The book says that inorder to backup to a remote drive, you need to log on with a domain NT account with administrative privileges.
I am unable to log on with a domain account and backup to a mapped drive.
For example, S1 and S2 are two servers in the domain D1. The NT domain administrative account is A1. I need to backup a database D1 on S1 to a location on S2. For this I do the following.
On S1,
sp_addumpdevice 'disk','dump_dev','s2sharenamefilename.bak'
On executing, I get,
(1 row(s) affected)
'Disk' device added.
Then when run the command,
backup database D1to dump_dev
I get the following error,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'dump_dev'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
The event log shows the following error message:
BackupDiskFile::CreateMedia: Backup device 's2sharenamefilename.bak' failed to create. Operating system error = 1326(Logon failure: unknown user name or bad password.).
Please let me know how to back up to a mapped drive using TSQL and Enterprise Manager.
Thank you.
Praveena
I have a dump device configured to append the backup file each time a backup is run. This file has grown extremely large. Im looking for a way to go into the file and delete all but the last 2 or 3 backups. Is there a way to do this?
Thanks
Hello,
is there any backup software (archserve,ultraback) that has a sql agent for 7.0 ? I have not found one yet.
Charlie Principato
Dialamerica Marketing,Inc.
I have a SQL2K/SP3 database with an MDF of 4GB and an LDF of .5GB. If I do a backup using Enterprise Manager, whether manually or via a DB Maintenance Plan, I get a .BAK of approx. 2GB. If I do a backup using:
USE master
EXEC sp_addumpdevice 'disk', 'RAMScopyBAK', 'F:MSSQLBACKUPRAMScopyRAMScopy.BAK'
GO
BACKUP DATABASE RAMScopy TO RAMScopyBAK
GO
I get a .BAK of 24GB !!!
In the end I want a T-SQL step to be part of a SQA job that will back up the database, thus the use of the dumpdevice command. I could create a maintenance plan, disable the jobs it creates, and then start them via sp_start_job, but this is just an example of a systemic problem; i.e. I'd have to do individual maintenance plans for more than one database.
Help?
Thanks,
Al
For a lot of smaller/medium sized systems that I administer, I tend to use SQL Server to backup to a backup device and then let the normal system backups backup the dump files to tape.
However, I am taking on a new system with approx 900Gb of data. We dont want to buy another 900Gb of disk to secure the databases ! I have found the SQL Agents in Arcserve 2000 to be unreliable to say the least. (we dont want to stop the SQL Services either)
Would anyone recommend alternative software to backup large databases.
Thanks
Tom
Hello,
Can anyone guide me which method is best to dump databases to a tape.
Using Sql server to dump database to tape or dumping of the file to tape.
Thanks
Sejal
Is there a way in SQL 2k running on win2k adv. server to map a scsi tape device from one sql server to another so database backups can run to the remote tape device??
View 3 Replies View Relateddatabase backups and differential backups
i took database back up on 01-jan-2005 and from 2nd jan onwards im taking differential backups. if i want to restore the database to a new system, is it sufficient that i restore the back up which i took on 1st jan , and the most recent differential backup ? will the entire data till the last back up date be restored?
Hi Guys,
I'm pretty new to SQL Server. I have a ? regarding backups.
I have a database which I'm backing up everyday using BACKUP DATABASE db TO db_bak WITH INIT. This job runs everyday successfully, but I'm having an evergrowing transaction log file. I presumed a full database backup will apply transaction logs and will truncate the log files. Isnt it so, or am i missing some concepts? This database can have a data loss of 1 day which is covered by my full database backup job. I dont need a transaction log backup. But to reduce log file size should I do a log backup?
-Sunil.
I have some backups scheduled on my sqlserver.most of them are differentail backups.But among them some have options 'Append to media' and some of them are 'Overwrite existing media '. But i dont know which of them falls into which category.How can i find that and can i cahnge 'Append to media' to 'Overwrite existing media '?If yes how can i do that?Does it effect my db if i change it?
INfact which one is the best option ('Append to media','Overwrite existing media ') among these two?
Thanks.
Just Read this :-
A complete database backup is very simple to execute and use in recovery. If your data doesn't change often, you might simply schedule a nightly full backup of your database. Even if you need more frequent backups, regularly scheduling a full database backup each hour might be sufficient (if your database is small enough).
Now what does a small database mean , can someone please let me the size in (GB) that a DB is considered to be small (even a range would do )
& How much time does the backup take , or should taken on a dual processor server. I mean need to get a rough idea
Thanks...
I am trying to restore a database to a point in time preferably a minute early before a crash. Initially i have a table called orders where i inserted new rows but it has later been dropped. If i later want to recovered that drop table along with the inserted rows before the drop
would this be the correct procedure using the stopat?
RESTORE DATABASE DB1
FROM 'c:db1.bck'
WITH RECOVERY, STOPAT = 'mmm dd, yyyy hh:mm AP'
the following is what i have worked out so far. Would it make sense?
1. I backed up the database.
backup database db1 to disk = 'c:db1.bck' with init
2. I Added 2 rows to two tables Orders and OrderDetails table.
insert into Orders(salesproductID,OrderQty,status)
values(' 1','5','6')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('9','6','8')
3. I backed up the log
backup log db1 to disk = 'c:db1.bck' with init
4. here I recorded time using the GETDATE() function.
select GetDate()
5. I added Add 3 more rows to the two tables like this
insert into Orders(salesproductID,OrderQty,status)
values(' 2','3','5')
insert into Orders(salesproductID,OrderQty,status)
values(' 4','2','9')
insert into Orders(salesproductID,OrderQty,status)
values(' 3','2','1')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('8','4','2')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('6','3','2')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('3','7','9')
6. I Performed a differential database backup.
backup database db1 to disk = 'c:db1.bck' with differential, init
7. I then recorded the second time: Time 2.
select getdate()
8. I Dropped the OrderDetails table.
drop table orderdetails
9. I then record the third time: Time 3.
select getdate()
10. I then Added 3 rows to the Orders table.
insert into Orders(salesproductID,OrderQty,status)
values('7','21','10')
insert into Orders(salesproductID,OrderQty,status)
values('14','12','19')
insert into Orders(salesproductID,OrderQty,status)
values(' 13','12','11')
11. I Backuped the log and record the fourth Time 4.
backup log db1 to disk = 'c:db1.bck' with init
select getdate()
Now when i tried to add rows to the OrderDetails table it does not exist but I like to get back the dropped table OrderDetails and not lose the three rows added to the table Orders. the only thing i would know is that the table orderdetails existed at time 2 and not at time 3
I am in search of a simple .bat file or .vbs script that I can use tostop my SQL database server. I am looking to schedule a script of sortsto initiate at a certain time every day. This script needs to be able tostop my server and/or initiate a backup. The script should be able to beran remotely from the lan. can anyone help me?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
hi ,
I have small doubt on backup and restore .if we taking daily two transactional backups(1 pm and 5 pm) , one differential backups(7 pm).and weekly once full backup monday 9 am.
if some one deleted data on wednesday 11 am . how can we get back that data.i tryed in my system but i failed to get back that data.
pls help some one ...
thanks
manoj.
SQL 2005
I have a scheduled back up running at 1am everynight which after a few problems is running ok.
Can I get it to overwrite the existing nights back up? no data has yet to be added to the database as we are still testing yet the back up is doubling in size each night.
Not really a SQL specialist this has been dropped on me so go easy on me please
Thanks
I've just taken over the administration of a small server with about 10 databases, admittedly I'm a bit of a novice so I apologize in advance. SQL Server 2005. I've been charged with creating an offsite backup, so I am trying out Mozy. They say that they use VSS, and all you have to do is point it to the data files and log files. To be honest, it worked pretty well. I have also left the regular backups running, to disk, which is then backed up to tape. The databases are Full backup, with regular transaction backups -- nothing too complicated.
Today I noticed the backup set jumped from 8GB to 10GB. I had a look and saw that the log files had gotten really large, roughly 1/3 of the 3GB database. I noticed a job that runs every Sunday morning which rebuilds the indexes and runs statistics so that explains why the log file would have jumped.
I would have expected that when the full backup ran at 5AM, and then the subsequent transaction log backups occurred, that this log file would have shrunk significantly. I looked at the logs and noticed that when Mozy runs, it causes a full backup to occur on each of the databases --- part of the shadow copy? I'm wondering if anyone can shed some light on what is happening. I spent some time today backing up the transaction logs manually and then doing a shrink on the log files, and got things back to a reasonable size.
Anyone have any idea if this shadow copy is causing these issues, or is this just a silly idea in the first place.
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.