Started work at a new company and relatively new to a SQL server 7.0 DBA role. Need to double check something. Looking at a Database where the database, transaction logs and overnight backups are saved to the same hard disk and 2 backups a week (MONam & THURSam) are made to a DLT tape. Surely this cannot be correct, as if there was a hard disk failure you could only restore from the DLT tape & this maybe a few days old. Is the way forward to save the overnight backups and transaction logs to another server. Any help/advice would be very much appreciated.
Fairly new to DBA work. In error log receiving message Error 1105 Can't allocate space for object 'Syslogs' in database 'msdb' because the logsegment is full.
I've looked in books and is this a serious problem, as I've tried to increase the size of the MSBD device but it will not allow me it says the Max size allowed is -1197MB...Is this right
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?
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 ?
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.
I 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?
I'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.
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?
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.
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?
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.
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.
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??
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?
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?
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.
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
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!
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.
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
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.
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??
I setup a maintenance plan to create full backups every night and to backup the transaction logs every hour. In the backup log, it always fails on backing up the transaction log for Master and MSDB. I believe this is because the recovery model for these two databases is set to "SIMPLE". Is it ok to change the recovery model on these two system databases to "FULL"?
I know that the differential backup will only back up information that was changed since the last full database backup, but I have process that I need to implement and need to know if differentials will work.
1. Perform full database backup on Sunday. 2. Perform differential backups Monday thru Friday (twice a day). 3. Perform a database restore on the following Sunday from the backup taken on the previous Sunday. 4. Perform differential backups Monday thru Friday.
At this point I would repeat steps 3 and 4 for the following weeks.
My question is this. Since I didn't actually perform a backup in step 3, but instead performed a database restore, will the differential backup in step 4 backup all of the information changed since step 1??..or will it backup all of the information changed since the restore in step 3? I'm hoping it will only back up the information changed since step 3.
I perform a full database backup at night with a differential backup at noon each day. I created 2 devices to do this. Northwind (for full backups) and northwind_diff (for diff backups).
When the differential is run, it appends each day's noon backup to the northwind_diff file so the file doesn't really reflect only changes - it just keeps growing and growing. I'm using the following syntax to perform the diff backup.
backup database northwind to northwind_diff with differential
Is there a way to initilize the backup device(file) prior to the differential backup being done?
