Hai friends,
I am using sql server 2000 as a Backend. I have stored nearly 50 user tables.Now i need backup that(export) , and i need to put that in another pc where sql server 2000 installed(import).
i have a "master" database.
do u have any simple steps , pl reply to me....
THANK U in ADVANCE
Ambrose
5 March 2000
Hi,
I had SQL Server 2000 installed like below:
Installation Directory = C:Program FilesMicrosoft SQL Server
Default data directory = D:
Default log directory = D:
I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup
However, Microsoft says we should never put the ..ackup and ..data folders in the same partition.
My question is: How can I move the path of the backup directory?
I checked the registy but this is what I found:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer
..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)
..DefaultData=D:
Can somebody help please?
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.
I want to know how people are backup up their win2k system and SQL 2000 server. If you want a run backup once a night and first backup win2k file system and then run backup using sql agent will that work?
Thanks.
Hi!
Is it possible to restore a backup made with sql server 7.0
on a SQL Server 2000 without any problems?
I`d need that because I tried to upgrade a 7.0 and got an errormessage
that says, that sp_vupgrade_replication culd't run successfully...
Any Hints?
Thanx in advance
I am trying to backup a database thro' enterprise manager and the msg I am getting is 'SQL Server Agent on target server (local) is stopped. Make sure it is running during the scheduled execution of this job.'
I tried logging into Query Analyzer and I am able to - so the server is up and running. I am not sure what I am missing.
Please help.
Thank you,
I am trying to set a backup of my database on every hour. I know I need to create a job first but I don't know how to create one! Can someone help me out, please ?
View 6 Replies View Related
Dear sir,
SQL SERVER 2000 €“ BACKUP PLAN
I have used scheduled backup plan in my SQL Server 2000.In case, suppose users are entering data during the backup time. What my doubt is, at the time of entering data by the users, all the opened data tables are to be took for backup or not.
Regards,
v.annadurai
Hi!!!
I don't know much about SQL Server....
In our office, about 30 clients are connected with SQL server. There are 4 to 5 database each of more than 2 gb.
There are two files of extension LDF and MDF of each database.
* Is it necessary to take back up of both files? LDF files are important???
* Which method / software is best for taking backup?
* Is it possible to take backup while files are used by clients????
* Is it possible to split database in smaller size. because each time I have backup to entire more than 2 gb file....
* Are newer version of SQL 2000 are better than 2000? Should I use it???
please help...
thanks...
Hi,Good morning to All,
I have SQL Server 2005 installed on my system. Recently I have formatted by computer. But I couldn't take backup of my data. Big loss.
For this, I've googled also. but I couldn't get clear information.
I want like the following:I could able to backup entire the Database either into my pen-drive, or some other media.And, latter I could able to put the Data back into my system.
Can anyone please tell how to take backup of my Data, and Restore also.
Thanks in advance,Ashok kumar.
Hai friends.....
I am doing project in ASP.NET(vb) my backend is sql server 2000...in my PC..
I have another pc....where sql server 2000 is installed.....
i have nearly 50 tables...in sql server 2000. in my PC..
I want to backup these tables from my pc and another pc...
can u tell me the steps...
I will try immediately....
Ambrose......
Hi Gurus,
I am new to SQL server and I need your advise on the following .
1. We have two SQL 7.0 Databases at a distant location and we want to move those databases to our location.
2. we plan to take the SQL backup of the databases in tape ( 4Gb and 2 Gb in size) , bring them here, restore them to SQL 2000 server.
- Is this possible , DO SQL 7.0 backups are restored on SQL 2000 without any problems ?.
Also, we plan to take log backups of the DBs at the source after the initial full backups and apply them at the target SQL 2K databases till we cut over to the Target Databases.
Ehat would be the best method of doing this? All your suggestions , pointers to real life scenarios like this, solutions are welcome.
Please let me know if you need any more informations , before suggesting any solution.
Thanks in advance
-Jay
Hi I'm an experienced programmer, but just don't have much experience with ASP or MSSQL. I inherited a website and now need to set up an automated backup system for a fairly large (40MB) MSSQL database hosted by a 3rd party provider of reasonable quality with ASP and SQL Server 2000. Let's avoid ASP.NET solutions since the website I inherited is written in "straight up" ASP (old school).
I have access to the database, login etc. I'm thinking I should combine an ASP script with the database connectivity and write daily/weekly backups to a dir on the hosting server.
Where do I begin? Tools? Examples?
Thanks for any assistance.
Stoob
I am experiencing the following problem when running a backup. This process was working really fine for 3 months.
BACKUP DATABASE [DBLIVE] TO DISK = N'C:BACKUPSdblive' WITH INIT , NOUNLOAD , NAME = N'DBLIVE backup C', NOSKIP , STATS = 10, NOFORMAT
Database size 10G
Recovery Mode : Simple
Daily Backup
Error message:
Executed as user: ACC33Administrator. 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) Nonrecoverable I/O error occurred on file 'D:sqldbDBLIVE_Data.MDF'. [SQLSTATE 42000] (Error 3271) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Thank You.
Hello!
I have a bug in SQL Server 2000 that I would like to discuss.
The bug is described briefly on http://support.microsoft.com/kb/821334.
"The potential for inconsistency in the backup history tables backupset and backupmediafamily is resolved. This issue may cause the restore process to point to the wrong backup files."
According to my experiences the bug is extremely serious. SQL Server backup and restore operations are not fully reliable. If you have a disaster situation there is a possibility that you can not perform a restore.
Technically a media_family_id is generated in a backup operation and inserted into msdb.dbo.backupmediafamily. What could happen is that a media_family_id, that already exists in backupmediafamily, is generated.
The bug is fixed from version 8.00.0859, but only if you have an special undocumented trace flag (3003) enabled.
Here is the information that I have got from Microsoft about this trace flag.
"Trace flag 3003 changes how SQL generates media_set_id for new backup files. It will guarantee that new media_set_id values in backupset table are unique when you create a new file with your backup. This is the only place in code where TF 3003 is used so it will not impact your SQL Server operations."
I have a SQL Server with 80 databases. I have a maintenance plan with full backup every 24 hours and log backup every 5 minutes.
The error happened on my server a few times every 24 hours. I believe it's a general problem.
Best regards
Ola Hallengren
Hi All
I am using Sql Server 2000.
The database is being used since 5 years and the transaction file (mdf) has increased upto 12Gb.
We are thinking of reducing the size of the database and keeping only the data of the past 1 year. So I would like to know if there is some way of backup or export so that I can get latest 1 year data.
Thanking You All:S :S :S
Hi people!!
I was wondering how u can copy a sql server database (to make a backup) on to either a CD or floppy(dunno if theres enough disk space for that)???
Thanks
Hi all,How can I schedule backup in SQL Server 2000 for ALL databases?
With SQL Server 2005 I can create maintenance plan to do this,
but in the 2000 version I don´t know.thanks!!!!
Hi all,
I am using SQL Server 2000 for one of our web application system called eRoom. eRoom uses SQL Server 2000 as a database. Now I have used SQL Server 2000 Database backup features to run the 4 database backup every night at 10pm. The backup runs fine. But recently I have found that for some reason when Our server machine which is hosting eRoom and SQL Server (Windows Server 2003 Standard Edition) is restarted, the database backup does not run anymore.
Now I have checked SQL Server settings and the settings allow SQL Server and SQL Server agent to restart both the services when the operating system starts. But still the database backup does not run when the server machine is restarted. It happened twice last week. Can anyone please help me?
Here are some information to help you understand the problem.
Backup Process Followed:
I went to SQL Server 2000 Enterprise Manager, then went to the appropriate server and then right click the Database>All Tasks> Backup Database> and then selected the backup file which is a (.BAK) file and then scheduled the database to be backed up at 10:00pm every night. I followed the same procedure for all 4 databases.
I am running SQL Server 2000 Standard Edition on Windows Server 2003 Standard Edition R2
Thanks
Hi Friends,I have installed SQL server 2005 Express Edition and SQL Server managementI have a SQL server 2000 db backup file. I try create a new database in my SQL server 2005 express Edition and try restore that backup file from device, it only searching for file with *.bak and *.tm extension! I tried generate backup file with .bak extension and tried restore into SQl server 2005 express edition but still it is not allowing to do so! I also tried copy my database's data file and log file and paste it under SQL server 2005 express edition Data folder and still not able to read the tables.Is that any ways to do restoring for this SQL server 2000 backup file into SQL server 2005 express edition! Anybody can help me on this please...:eek:
View 2 Replies View RelatedHi Dear,
May Any one guide me?
I have a backup file of database which is in SQL Server 2000. it has no Extension.I want to restore this backupfile or this database in my SQL Server 2005.
I have tried to Attach Database or attach this backup file in Sql Server2005 but it also fails .
First I have created New database name as is on the backupfile and then I have also tried to rename this file with .bak extension and then try to restore again it fails.
Plese Guide me urgently........
:eek: :eek: :eek: :eek: :eek:
Hi All
This is a very urgent requirement of my client having 200 stores and one Head office. the HO has just installed SQL-Server 2005, The HO sends small backups of the data related to stock transfer which needs to be restored at stores. It was working find when both the HO and stores were SQL-Server 2000. but the process has come to a dead halt as the HO has SQL-Server 2005. It is not possible to convert the stores immediately. please help me out.
Thanks
Raoshan
howto backup ODBC configure and data on windows 2000 server help me!
thanks.
I want to restore a backup of a SQL Server 2000 database to SQL Server 2005. Would I need to do anything after running the restore to upgrade the database?
View 1 Replies View RelatedHi everyone I am fairly new to SQL server and need some help with creating backups. I am a little confused whether to append or overwrite my backup data when saving full, differential and transaction log backups to disk. Below is my current backup schedule.
1) A full backup every night at 2:00a.m. that overwrites my existing backup. Should this be an appended backup? Wouldn't my file keep growing? How do I manage the backup file size?
2) Differential backups every two hours that is set to append. How do I stop this file from growing? Should I be controlling the size of my backups through the backup set expiry option?
How do I incorporate transaction logs into my backup schedule and manage the size of the log? Should the log backups also be appended or overwriten?
I appreciate everyones help in advance.
Hey all-
I am working to establish a backup plan for SQL Server 2000. We are currently doing log shipping between servers.
A full backup once a week
transaction log backup every hour
We run a bulk deletion (delete * from table where date < ninetydays) which deletes 22,000 rows approximately. We are running in Full Recovery mode.
I wonder if there was anyway to improve my backup plan, preferably minimizing logging when the deletion takes place but still backup the data with log shipping.
Let me know if any of you have ideas. Thanks
Nate
Of course I'll try this before I go live, but I figured I'd askhere first. I've read the docs, and the answer is unclear.My current backup strategy is:Take a full database backup occasionally (like, every two weeks).Back up logs every five minutes.This works, but restoring the database all the way through cantake a while, as I've sometimes spun through 800+ log restores.I'd like to change toTake a full database backup occasionally (like, every two weeks).Take a differential backup every day.Back up logs every five minutes.So, I'd have this set of backups after day twoFULLLOGS 1-100DIFFERENTIALLOGS 101-200When I go to restore, do I always have to doFULLDIFFERENTIALLOGS 101-200or can I doFULLLOGS 1-100LOGS 101-200?I guess what I'm really asking is: is there any reason to keepthe "old" logs around (in this case 1-100), if I assume I alwayshave the latest differential?I suppose I *might* need them to do a "point in time" restore,i.e.FULLLOGS 1-53.but assuming I never want to do that, can I discard the logsthat are earlier than my latest differential?
View 1 Replies View RelatedHello,Recently I've tried to restore backup made on SQL Server 6.5.Unfortunately, the only version of SQL Server I currently possess is SQLServer 2000. I have access only to that backup, because I've made it afew years ago, and the main db file doesn't exist any more ;( I would begrateful if you could help me a little ;)RegardsR.Kruk
View 2 Replies View Related
I want to set a full and differential backup to one database in sql server 2000.
Is there a way to set both full and differential to just one database.
i want the full backup weekly once and differential every day to set up.
Please let me know
hi Experts,
any one tell me that how to write code to take back up of sql server database?
I have schedule backup job from SQL Server 2000 to Tape. Each Jobs consist of 4 steps which backup different database.
Sometimes I encounter problem. The Message is
Executed as user:dbo. The file on device '\.Tape0' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
There is no problem at the command, as it will run normally when i manually trigger the schedule job. Sometimes the error occur only after few days the job run.
When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:
The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)
The backup is made from an SQL Server 2000 database called 'UpperBridge'
I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.
Any help very much appreciated.
I was running Small Business Server 2003 with MS SQL Server 2000. I put backups of all my SQL Server 2000 databases on hard disk d: and the operating system and applications on c:/. I then reformatted c:/ (but left d:/ intact) and installed Windows XP Home. Then I installed MS SQL Server 2005 Developer's Edition.
I would like to now restore the backups of the SQL Server 2000 databases (still intact on drive d:/) to SQL Server 2005, but I get a variety of error messages.
I suspect that I am going about this the wrong way. I have done several hours of research in the documentation without success. I cannot attach the SQL Server 2000 database to SQL Server 2005 because it is a backup, not a database. I cannot migrate the databases as part of the installation of SQL Server 2005 because (a) they are back-ups, and (b) I don't have SQL Server 2000 installed when I install SQL Server 2005.
Is there a solution?
TIA
Mark Schreiner