Hi
I'm not really a MS SQL Admin. I have only one
system in our house holding an MS SQL DB.
I need to made a backup of the DB.
Can someone tell me what is the cheapest way?
To buy Backup Exec is not an option.
And I can't shut down the service while the backup is
running...
Regards and thanks in advance
Dominik
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.
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
Hi All,
Is there a way to back only specific tables from a database? I need to filter a few tables as they are not needed for backup.
Is there any way to do this?
Thanks in advance.
Hi,I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?The database only contains tables and views...ThanksKoen
View 1 Replies View RelatedHi!
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,
If it can, is there some special way to set it up? I can only be successful setting up a tape drive.
Thanks,
Brian
Wondering if SQL 2000 offers any compression of logical backups vs. no software compression in SQL 7.5. I've been asked to evaluate a 3rd party tool that compresses SQL 7.5 backups nicely, but this tool does not allow concatenating all databases into a single logical device file. I'm thinking I won't recommend the tool if SQL 2000 does compression on the fly as we will be upgrading sometime over the next handful of months.
TIA
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 RelatedHiWe are running SQL 2000 Enterprice version with SP3a on a DellPoweedge 5600 server running Advance Server. It has Tape drivePowerVault 110T (DLT VS80 Tape Drive) & uses DLT tapeIV tapes. A DBmaintenance job had been specified to backup on to tape daily. I havefollowing issues;1.The backup job sometimes fails with the following error in Eventlog(Event ID:17055)3041: Backup failed to complete the command BACKUP DATABASE [UNIQRS]TO TAPE = N"\.Tape0 WITH NOINIT, NOUNLOAD, NOSKIP, STATS = 10,NOFORMAT. The job is specified with the Verification option.2. If I try to restore a succeeded backup it asks for a initial tapewith a past date which does not exists3. Is there a way to view the content of the tape & free space etc.4. It is recommended to put a disk backup & transfer it to tape usingWindows backup utility. Is there a way to automate this by initiatingthe Backup utility when the SQL finishes the disk backup.ThanksChirath
View 2 Replies View RelatedI used Enterpise manager to make a backup of a SQL Database. When Irestored it on another machine, I did not have any of my indexes. Ichecked the backup wizard and I could not find any reference toincluding or not indexes.Are my indexes there, but not built? Maybe a stored procedure I needto run to rebuild them?TIARob
View 2 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
I am running a Microsoft SQL 2000 Server. Our original Systems Admins had implemented a strange backup system where they were backing up the databases to the same SQL DB server. I am in the process of moving our backups of off the box we are backing up to a new backup server. I have configured my backup jobs and have been running the backups tandem for the last couple of days. Since doing this I have noticed an issue with failing backups. The backups don't fail on some backup jobs but on others they do. I know SQL is supposed to allow for backups while the database is active. I believe my backups are failing because the database is active and there are two backup jobs accessing the database at the same time.
Has anyone else run into this issue? Can you run backup jobs tandem on an active database?
Hello all,
I would like to be able to backup our databases to a network share mapped drive. Is this possible? When every I go in to setup a maintenance plan it will only allow me to select local drive as the backup location. If my server crashes its not going to do a whole lot of good if my databases are stored locally (I do also backup up to tape every niight). I backup the databases every hour to local disk and would like to send them to one of my MS Storage Servers on the network.
Any help would be greatly appreciated.
thanks
Jason
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 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
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
I have set up two Maintenance Plans to do daily backups overnight for our two DBs that are on our SQL server.
As a separate strategy we want to have a second backup done nightly that would involve
#1 detach the db(s)
#2 copy the mdf(s) and ldf(s) to L:xxx
#3 attach the db(s) again
#4 zip up the db(s)
#5 copy the files to a different server for storage.
I created #1, and #3 in query analyzer and saved the script.
I want the process to run at say 2AM and don't know how to schedule them to run - that is #1 ... #2 ... #3. Anyone have sample scripts for this kind of a backup strategy?
This seems like a very simple process especially for a restore and especially since there would not be any trasaction logs involved. This way if we had a KRASH we could take the backup from the separate server and install it on a warm SQL backup server.
Apart from the new servers name then what other steps would I need to cover to get the apps up and running in the quickest time. The app software runs as a client install - Access 97DB] I'm especially curious if I need to have more than a fresh MS2000 SQL server install.
I'm know I'm asking a lot for a first timer.
It seems we want to handle disaster recovery BEFORE we even have our first system crash.
many thanks
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 Everybody,
I am kind of new to MS SQL server databases. I like to take a full export at database level. When I use DTS wizard, it did allow me to take one table at a given time. I have 1000's of table in my database. Manually doing so is not possible. Should i call the 'bcp' command line utility 1000 times to collect the table data to 1000 different flatfiles or is there any provision to take export of all the 1000 tables in one single command/tool.
Many thanks.
Friends -
Is it possible to restore user databases in SQL Server 2005 with user database backup copy of SQL Server 2000.
Please advise on this.
Cheers:)
VSH
Hi All
I have a problem in transferring a 2005 backed up database to SQL Server 2000 SP3.
i got an error message, crux of it is
"too many backup devices specified only 64 allowed"
the i browsed MICROSOFT help, downloaded SQL 2000 SP4 - mentioned as a fix for the above error.
Now get a message,crux of it is
"current version of Database in 611 and destination version is 536"
Is there a solution????
should i download any other patches or
Should i tweak the SQL 2005 backup process???
ASAP Plz
Hi,
I have an existing sql server 2000 installation on one server. I now need to take one 2000 DB and restore it to a new sql server 2005 installation (on a different server).
What is the best way to do this. I can not upgrade the sql server 2000 installation to 2005. Basically i hoping all i need to do is backup the 2000 db and then restore it on the new sql 2005 server.
Is this possible...thanks for all help.
Environment: Win Server 2003 Standard Version, SQL Server 2000, HPDDS3 Tape(local).Backup procedure:1. open SQL Server Enterprise Manager2. find the database i want to backup3. right click and select "All Tasks"-"Backup Databse..."4. Select "destination"-"backup to"-->>"Tape"5. Click "Add", Select "Tape" device name(or may address)"\.Tape0"6. Click "OK" begin to backupError:system prompt:The file on device '\.Tape0' is not a valid Microsoft Tape Formatbackup set. BACKUP DATABSE is terminating abormally.On this SAME server, using the same TAPE, but USE Tapeware(a HP backupsoftware come with HP tape machine) to back file, everything is OK.I don't have any clue about this. am new to Windwos, new to SQLserver. Any body could show me light? Thanks in advance.A.Y. From shanghai/China
View 6 Replies View RelatedDoes anyone know if this works to where the data is viewable/usable?
I have an application that takes datasets out of the SQL database. We decommisioned our old SQL 2000 SP4 machine and brought in a SQL 2005 Server. What we did was backed up the old database and then restored it on the new Server.
Now the application does not see the old datasets but will collect new ones that are viewable. We can import older datasets but we have nearly 27 GB of data, which our DBA can see the old tables in Enterprise Manager but our applciation can't see it.
I'm thinking it might be a schema issue or the way that 2005 translate the odler 2000 SP4 database. Does anyone know, or have they run a back up on a older 2000 Server and then restored it to a 2005?
Thank you.
David