Backing Up The SQL Server 2000 Database Using Tape Drives
Jun 9, 2006
Dear sir/madam,
I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.
My client is changing hosting providers. We are trying to download a full backup of our MS SQL 2000 database from the old host. We can get all of the data using the Data transfer wizard in Enterprise manager. We can get all data and structure using the Data Transfer wizard with other options. However, when we do this, all of our tables get renamed with a prefix that is our username from the old host.
So, if we have a table named customers and our MSSQL login on the old host is userA, then our table gets renamed to tableA.customers.
This won't work with our applications and stored procedures. Changing tens of thousands of lines of code is not acceptable.
Does anyone know a way we can get a full copy of the database (rules, data, structure, keys, identies, etc) without having the username prefix?
I am getting an error while backing up a particular database named "asset" . The error says ........
Server: Msg 3132, Level 16, State 1, Line 4 The media set for database 'Asset' has 2 family members but only 1 are provided. All members must be provided. Server: Msg 3013, Level 16, State 1, Line 4 BACKUP DATABASE is terminating abnormally.
I have created two backup devices for this database ("asset") through enterprise manager. AssetDMP AssetLogDMP
I have total 9 databases out of which only this database throws error. All other database backups going fine.
Here is the query that I am using to backup the database and Log.
USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Asset') BACKUP DATABASE Asset TO AssetDMP WITH NOUNLOAD, STATS=10, INIT, SKIP GO IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Asset') BACKUP LOG Asset TO AssetLogDMP WITH NOUNLOAD, STATS=10, INIT, SKIP
I am setting up a new SQL Server that has an external HP 920 SAS Ultrium tape drive attached to it via a p400 sas card.
I am running 2003 R2 x64 with 2005 SQL Server x64, and I am trying to set up my DB backup plan but for some reason I can't get SQL to recognize my tape drive, alhough it is listed in device manager.
Can someone please help in in understanding what I have done wrong. My company is using Backup Exec 7.3 as it's back up application. I have built a server, installed SQL, Built an database and added logins. I am backing up my database and Transact Log Files to specific folders on my partioned drive. Why when I run the backup ups, It backs up everything EXCEPT for my SQL folders. It seems to Skipp all SQL Folders. Please Help, I need to have this funtion working properly. What have I done wrong or what do I need to do to make this stop. Does this perhaps have something to dwil Here is an example of the backup Message in Backup Exec that I am recieving:
Media Name: "DIFF" Backup of "FEPTESTE$ " Backup set #41 on storage media #1 Backup set description: "Daily 6" Backup Type: DIFFERENTIAL - Changed Files Backup started on 6/28/02 at 12:36:21 AM. The item Microsoft SQL ServerDataMSSQLDataTESTDB_Data.MDF in use - skipped. The item Microsoft SQL ServerDataMSSQLDatamaster.mdf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatamastlog.ldf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatamodel.mdf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatamodellog.ldf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatamsdbdata.mdf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatamsdblog.ldf in use - skipped. The item Microsoft SQL ServerDataMSSQLDataorthwnd.ldf in use - skipped. The item Microsoft SQL ServerDataMSSQLDataorthwnd.mdf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatapubs.mdf in use - skipped. The item Microsoft SQL ServerDataMSSQLDatapubs_log.ldf in use - skipped. The item Microsoft SQL ServerDataMSSQLDataempdb.mdf in use - skipped. The item Microsoft SQL ServerDataMSSQLDataemplog.ldf in use - skipped. The item Microsoft SQL ServerDataMSSQLLOGTEST_Log.LDF in use - skipped.
Backup completed on 6/28/02 at 12:37:11 AM. Backed up 48 files in 24 directories. 14 items were skipped. Processed 23,876,604 bytes in 50 seconds. Throughput rate: 27.3 MB/min
We are currently trying to restore a copy of our production database to a different server. Unfortunately SQL does not see the tape drives and we are not able to add them. The tape drives are installed and visible in device manager. I have updated all drivers and firmware but still no luck. Any ideas would be appreciated. Thanks
I have SQL Server 2000 Standard Version backups going to Disk then we have Legato Networker Software to make backups of these backup directories to TAPE.
When we retrieve the file from the TAPE and use to recover the database we get the following errors: (Notice some work and some do not. This is from command 'restore verifyonly from disk='
Server: Msg 3242, Level 16, State 2, Line 1 The file on device '\backupfilersqlbackupsqlserver ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709210130.TRN' is not a valid Microsoft Tape Format backup set. Server: Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally. The backup set is valid. The backup set is valid. Server: Msg 3242, Level 16, State 1, Line 4 The file on device '\backupfilersqlbackupsqlserver ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709180130.TRN' is not a valid Microsoft Tape Format backup set. Server: Msg 3013, Level 16, State 1, Line 4 VERIFY DATABASE is terminating abnormally. The backup set is valid. The backup set is valid.
If I take the original backup file that are on disk they are all valid.
Thanks for any help that can lead me to the solution
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.
HiWe 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
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
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database? I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors. But I have heard a lot of discussion that you can't just simply backup SQL Server data files? I'm wondering how sound the backup I've created is... Any suggestions?
I got a quick question. My database size has reached the limit of the drive, one option is to add a new database device on a different drive, then expand in across 2 drives. Anything I have to concern for a database on 2 database device on 2 logical drives (recoverability or ...?) compared to maybe another option:
reformat the whole disk and allocated more space to the original drive and recover (since in my case, all disk space have been formated) from backup....
Every time a new database is created on my server,my log files and database files automatically default to the following to the C drive in the following path: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData. Is there a way to get them to default to different drives?
For example, right now I have my data files on our F Drive, which is a RAID 5 and my log files on our G Drive which is a RAID 1. Every time an out of the box application installs a database, it does so on the C Drive. I want data files to default to my F Drive and log files to default to my G drive? Is this possible?
Does anyone know if it is at all possible to restore a single database from tape if no backup jobs have been ran or setup for this database. However, the server itself has been backuped to tape. So I would like to restore this database from the server tape backup. If so, how do I accomplish this?
I am trying to backup a database to tape. I'm using Microsoft SQL Server 2000 Enterprise Edition. When I try to do the backup using Enterprise Manager I see the following message:
Microsoft SQL-DMO (ODBC SQLState: 42000) Cannot open backup device '\.Tape0'. Device error or device off-line. See the SQL Server error log for more details. BACKUP DATABASE is terminating abnormally.
In the Event Viewer is logged the following message:
Event Type:Error Event Source:MSSQLSERVER Event Category:(2) Event ID:17055 Date:14/1/2004 Time:10:29:20 User:N/A Computer:MySQLName Description: 18204 : BackupTapeFile::OpenMedia: Backup device '\.Tape0' failed to open. Operating system error = 5(Access is denied.).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I can do the backup to disk normally, but I need to do to tape.
Hi, I back up SQL Server 2000 and SQL server 2005 databases to hard disk using the SQL Server Backup Wizard and maintenance plans. Then, I copy the resulting backups to tape using third party tape backup software and compression by the backup software and hardware. I do not use the SQL Server Agent available for the third party backup software. Is this acceptable, or does the compression performed by the third party backup system introduce opportunities for database corruption or other negative effects?
When backing up a database in MS SQL 7.0, there is a window that appears in the destination section of the backup window. In this window is where the filename appears that the backup will proceed to and to the right of this window are "add", "remove" and "contents" buttons.
If there are multiple names in this window, will the entire backup go to both files or does it split the backup between the two filenames.
Here is the situation, I have a web application (asp.net/vb.net) with ms sql 2000 as the database. I have two database server. I would like to backup database 1 with database 2. I would like to do it at intervals and through code. If anyone can give me some ideas on how to do this or any research material, please do.
Sorry this is so trivial, but I don't work with SQL Server too much. I just want to back up a table in one of my databases. I'm using SQL Server 2005, the management studio. Can someone walk me through how to back up a table? Thanks!
I backup the database into tape0 using management studio and can see the contents in the backup media.
However when I tried to restore, the error "timeout expired" appears and sometimes together with the error "restore headeronly is terminated abnormally".
Please advice and thank you in advance!
Below is the error message:
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query) at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd) at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
===================================
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..? ive used tables and stored procedures and a diagram btw. thanks for any advice
Hi, I am in a production database server. Data has been added and deleted via the web. So there is alot of traffice in and out of the database. How can I make a backup plan to make sure I am not lossing any input or deleted data . I am familiar with backing up a database in regular intervals, but I amnot sure if this is the best way in this senario.
How can I take advantage of the transaction log in restoring the database.
I have been using the backup feature of SQL Express for some time and I thought it would be good to test a restore in case the worst happend. I moved my bak files to a usb memory stick and copied them to a machine and installed SQL Express so its empty no database's etc. So I right click the databae folder and choose restore from device and point to the db's but I get errors restoring them. What am I doing wrong? Is their any guides that give steps to backup and restore database's ? Any help would be great - I can of course provide more information need be.
Is there a way by which we can execute the backing up of database from the command prompt......
Suppose i need to do the below task. I can write a stored procedure and schedule it. But the situation demands to run from the command prompt. Is there a way out.
-- Create the backup device for the full MyNwind backup.
I developed an application in Vb.net and SQLDMO that backs up a database and performs certain operations on it once this is done. I have set the backup to backup to file which the user selects. However when i execute the backup i get an error message that " Backup device not found or error finding device" .THis error baffles me because i have not specified the backup to be to device can anyone help?
I found some code below that works fine when I run it in my vb.net code in Design mode. When I publish the application, run the app and then execute the sub MyDbBackup() I get the following error: Cannot open backup device 'C:dbBackup'. Operating system error 5(Access is denied). BACKUP DATABASE is terminating abnormally.
Here is the code that runs:
Public Sub MyDbBackup() Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb) sqlconn.Open() Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE [" & sqlconn.Database.ToString & "] TO DISK = 'c:myDataBaseBackup.bak'") Backupcommand.CommandType = CommandType.Text Backupcommand.Connection = sqlconn Backupcommand.ExecuteNonQuery() sqlconn.Close() End Sub
2 Questions: Why does it backup fine in design mode, but not after it is published? Also, in design mode, my database is called TAP_Master.mdf but when I look under C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData, I do not see that database name... all I see is master.mdf, model, msdbdata etc... Is that database stored anywhere else?
I have set the MyConnectionStringdb variable to = "Data Source =.SQLEXPRESS;AttachDbFilename=|DataDirectory|TAP_Master.mdf;Integrated Security=True;User Instance=True"
I mapped a drive on to my SQL Server box. It points to another server from the same domain. When I try to backup or restore a database, I can't see this mapped drive through my SQL Server. Even if I type the entire path, SQL Server wouldn't take it. I don't have a clue about why it is not working. Can anyone throw some light on this. Your help is grately appreciated.
Hello! I hope that I have posted in the right spot. We are looking for advise. I know that there is no right or wrong answer. "It depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical drives are being used. These 3 physical drives are 1 container with Raid 5. This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is SAN.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples of what they are doing or provide suggestions?
I'm aware that it's best practice to separate mdf and ldf files onto separate drives.
However, I see a lot of servers where the underlying disk array is the same for drives on the server.
Is there still any performance benefit to separating mdf and ldf files in this situation?
For example, a single virtual server running SQL Server, with multiple drives attached.All of the drives are connected to a shared storage via iSCSI.There drives C:, D:, E: etc are all actually sharing the same underlying disks.
Obviously, there are some benefits from an administration perspective whereby individual drives can be reconfigured without affecting the others.
