Master DB Backups
Jul 10, 2002
I've got a new (my first) SQL2000/SP2 installation in which I've set up a vanilla database maintenance plan, including backup of master & msdb with my user databases. My user databases are backing up, but master & msdb fail every time with "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode." Presumably this is caused by Integrity checks being set to perform before every backup.
First, this should work as I'd think we want to do the integrity checks; however, in the interest of getting a backup I've unchecked that box on the Integrity form. Unfortunately, my "unchecking" doesn't stay; I've 'applied' and 'okayed', but the next time I open the maintenance plan form that box is checked again!
Any suggestions appreciated.
TIA, Al
View 3 Replies
ADVERTISEMENT
Sep 19, 2015
Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances, Is there a way I can automate a file deletion process from within SQL Server?
View 2 Replies
View Related
Nov 15, 2015
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
Jun 19, 2007
I got a File with sales orders and their details.
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.
Any Idea?
View 4 Replies
View Related
Mar 25, 2008
Hello,
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.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Nov 2, 2015
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?
View 4 Replies
View Related
Jan 14, 2007
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
View 9 Replies
View Related
Mar 15, 2002
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 Related
Jun 27, 2001
I'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?
View 1 Replies
View Related
Aug 28, 2001
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?
View 2 Replies
View Related
Nov 13, 2000
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 Related
Dec 13, 2000
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?
View 2 Replies
View Related
Oct 18, 2000
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.
View 1 Replies
View Related
Jan 4, 2000
I 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.
View 2 Replies
View Related
Nov 30, 2000
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
View 1 Replies
View Related
Aug 10, 1999
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
View 1 Replies
View Related
Dec 4, 2000
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
View 1 Replies
View Related
Apr 5, 1999
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.
View 1 Replies
View Related
Sep 11, 2003
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
View 2 Replies
View Related
Aug 22, 2001
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
View 1 Replies
View Related
Aug 21, 2001
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
View 2 Replies
View Related
Oct 9, 2001
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 Related
Feb 1, 2005
database 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?
View 4 Replies
View Related
Dec 30, 2003
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.
View 4 Replies
View Related
May 20, 2004
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.
View 2 Replies
View Related
Feb 8, 2006
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...
View 6 Replies
View Related
Nov 16, 2007
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
View 14 Replies
View Related
Jul 20, 2005
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
Dec 26, 2007
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.
View 13 Replies
View Related
Aug 10, 2006
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
View 5 Replies
View Related
Mar 18, 2008
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.
View 1 Replies
View Related
Dec 19, 2007
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.
View 6 Replies
View Related
Feb 8, 2006
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??
View 3 Replies
View Related
