Hi all , hope my post finds u all well.
Currently I was reading about how to create a backup and i have some questions and i hope to find helpful answers..
what is back up media ,device and tape ?
how to initialize the media and what is the need for something like that ?
what about media headers that must be written ?
thanks in advance
Davids Maguire
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
------------------------------
Server Name: NTSRV1
Error Number: -2
Severity: 11
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
Is there a way to check if a tape drive contains a tape? I would like
to check this prior to doing an incremental transaction log dump...
ben
Hi Folks,
I have a SQL Server 7.0 with and exteranl SCSI tape device.
When I attempt to backup a database in EM the tape device is being greyed out.
I have add the backup device using sp_dumpdevice and made sure that it is in sysdevices table. It will not allow me to add this backup device from the drop down.
1. Why does EM not see this device ?
2. If I try to back this up using T-SQL will I be able to use the registared tape device.
Thanks Folks
I want to create a backup device for the tape drive.
So I go in "Server Objects"=>"Backup Devices" and create a new device. Problem is: In "Destination" I can't select "Tape:".
The ntbackup.exe program recognizes the tape drive, but SQL2005 not.
I have this:
-SQL2005 Enterprise edition 64bit.
-Tape drive HP StorageWorks Ultrium 960.
Someone knows how I can create a backupdevice for the tape?
Thanks
IF Debugging = removing bugs from program THEN programming := putting bugs in program;
I've got a new HP ML370 G4 Server 2003 SP1 with a fresh install of SQL 2005 SP1. I'm trying to create a backup device for the internal HP DAT 40 and tape is greyed out as an option. I have the newest HP software drivers and firmware. The tape drive is seen by Server 2003 device manager and by NTBACKUP. I can backup using that program. Why doesn't SQL 2005 see it? I tried a new HP Ultrium tape drive with the same results. Anyone else see this and can recommend a fix? TIA
View 11 Replies View RelatedHello,
I am trying to configure the backup device on SQL Server 2005.
I cannot get the Tape device list.
The Tape Device is attached physicaly to the server ! (IBM Ultrium-TD3 scsi). I can 'see' the device using Windows Backup; but I cannot even get the list of tapes on SQL Server.
Can you please give me some help on this one ?!
Thank you all.
Youss
Hello,
TSQL command works fine,
C:SQLCMD
1> BACKUP DATABASE TestDB TO TAPE = '\.Tape0'
2> GO
but how to append more than one databases( SQL 2005) to the same tape.
Having said this, what is the solution for " I've got a new HP ML370 G4 Server 2003 SP1 with a fresh install of SQL 2005 SP1. I'm trying to create a backup device for the internal HP DAT 40 and tape is greyed out as an option .........." Pls advice me.
Does anyone know if it is possible to have a tape device another server as a tape dump device on SQL. Instead on removing the tape drive and installing it locally ?!?
Regards
Jasper
emergency help, our server1 is down, we need to restore everything from tape using server2, but when i put the tape in the server2, it said "unreadble media", what should i do to restore everything? How can the tape to be readble? Thanks, thanks.
View 14 Replies View RelatedIs there any SYSTABLE or table from where I can get the backup device logical name.
Thanks,
-Rajesh
I want to back up my database to a logical drive and restore it from this logical device. But I am unable to see my logical device through sql server management studio interface. I am able to back up to a logical device and restore it from logical device through T-SQL code. I am wondering how come I am unable to see my logical device from sql server management studio interface. Is there any way, I can solve this problem? Thanks in advance.
View 3 Replies View RelatedDear All,
I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
currently I am using Append to Media option, and every day backup, I see my backup files growing in size.
can someone give me nice example about these two options,
Thanks,
This is my first attempt to use SQL Server Compact with Visual Studio 2005.
I wrote a program for Inventory Scaning which requires the database to be stored on an SD Card. As long as the device is left powered on, the programs function normally. However, if the device powers off and is then turned back on the 0xC0000005 error is displayed when the program attempts to do anything with the SQL Connection.
I have also written a small sample program to test and the results are repeatable each time.
The sample program consists of a single form with six buttons (Create Database, Open Database, Insert Records, Delete Records, Close Database, Dispose Database).
Both programs are written in VB.
Here is the code for the sample program.
Public Class frmSQLTest
Private cnDB As System.Data.SqlServerCe.SqlCeConnection
Private connString As String
Private Sub frmSQLTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.txtDB.Text = "Storage CardTest.sdf"
End Sub
Private Sub txtDB_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDB.LostFocus
connString = "Data Source=" & Me.txtDB.Text
Me.txtStatus.Text = "Connection String Set"
End Sub
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
If Me.txtDB.Text = Nothing Then
MessageBox.Show("Unable to Create Database")
Exit Sub
End If
Dim eng As New System.Data.SqlServerCe.SqlCeEngine(connString)
eng.CreateDatabase()
eng = Nothing
Me.txtStatus.Text = "Database Created"
End Sub
Private Sub btnOpenDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenDB.Click
Dim sqlDB As New System.Data.SqlServerCe.SqlCeCommand
If cnDB Is Nothing Then
cnDB = New System.Data.SqlServerCe.SqlCeConnection(connString)
End If
cnDB.Open()
Me.txtStatus.Text = "Database Opened"
sqlDB.Connection = cnDB
sqlDB.CommandText = "CREATE TABLE Master (Item nvarchar(12), Description nvarchar(19))"
sqlDB.ExecuteNonQuery()
sqlDB.CommandText = "CREATE INDEX idx_Master on Master (Item)"
sqlDB.ExecuteNonQuery()
Me.txtStatus.Text = "Master Table Created"
sqlDB = Nothing
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim RecCount As Long
Dim sqlDB As New System.Data.SqlServerCe.SqlCeCommand
sqlDB.Connection = cnDB
For RecCount = 1 To Me.nudRecs.Value
sqlDB.CommandText = "INSERT INTO Master ([Item], [Description]) " _
& "VALUES ('" & RecCount.ToString & "','Test Item " & RecCount.ToString & "')"
sqlDB.ExecuteNonQuery()
If RecCount Mod 100 = 0 Then
Me.txtStatus.Text = "Updating Record..." & RecCount.ToString
End If
Next
Me.txtStatus.Text = "Records Inserted"
sqlDB = Nothing
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim sqlDB As New System.Data.SqlServerCe.SqlCeCommand
sqlDB.Connection = cnDB
sqlDB.CommandText = "Delete FROM Master"
Me.txtStatus.Text = "Master Table Cleared"
sqlDB = Nothing
End Sub
Private Sub btnCloseDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseDB.Click
cnDB.Close()
Me.txtStatus.Text = "Database Closed"
End Sub
Private Sub btnDispose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDispose.Click
cnDB = Nothing
System.IO.File.Delete(Me.txtDB.Text)
Me.txtStatus.Text = "Database Diposed"
End Sub
End Class
In my tests, I start the program (loaded on the Storage Card in the same directory as the database). SQL Server Compact is also installed on the SD Card to save space in Main Memory.
Steps to Re-Create the Error:
1. Click the Create Database button. Database is created properly.
2. Click the Open Database button. Database connection opens properly.
3. Click the Insert Records button. Records are inserted into the table properly.
4. Turn off the Symbol MC3000 unit.
5. Turn the unit back on.
6. When I click on any of the following buttons, the error is displayed. Close Database, Insert Records, Delete Records.
I have also tried closing the database (as well as closing and disposing the database) prior to step 4 above, but when I try to re-open the database (or create the database) again, the error displays.
Is there anyway to correct this issue?
With the real program, the database must be kept open as long as each form is open to prevent speed issues and the database must be stored on the SD Card since there is not enough storage space available on the device.
We are getting error and we are aware that there should be 2 files but we are able to find the second file,how to search all files(2) so that we may restore DB.
View 4 Replies View RelatedHi, I'm from Argentina. I made a backup of my database from ServerA (SQL 2000) and trying to restore it to ServerB (SQL 2005) I'm getting the following error: "The media set has 2 media families but only 1 are provided".
Any ideas? Thanks.
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies View RelatedBasically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
Hi to All
when i m trying to execute following code
backup database web
to disk = 'c:inetpubwwwrootackupmybakup.bak'
with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. 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 DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
but not for any subfolders of wwwroot.
Can Any One Help Me ??
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedNew to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View Relatedcurrently if I want to restore 1 db to another i do
RESTORE DATABASE userr
FROM DISK = 'd:sqlbackupsuser.bak'
WITH MOVE 'user' TO 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAuserr.mdf',
MOVE 'user_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAuserr_log.ldf',
REPLACE
now I am backing up my db to a media - How can I do the same restore above but from that media (I don't remember what I made the media point to -- how can I find this out)
Hi Folks..I have a question i want's to make a website where people can upload mediafiles like movies short movies of a minut or 3 max. Can i store those mediafiles like mp3, mpg etc.. for instance in a sql-server database, or is thisnot wise to do?Thanx in advance..
View 1 Replies View RelatedHelp, I'm trying to burn a CD using Windows Media Player 10. I keep getting the message-interface not registered. I tried to download WMP, and it says it cannot because there is already a more recent version already installed. Should I uninstall WMP, and then reinstall it. If so, how do i do that, I cannot find it on the control panel!
Thank You for your help
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View RelatedWe have a software assurance contract, and we getour media from a Microsoft Certified Partner.Our MCP hasn't been able to provide us with media forSQL Server 2005.It's been 7 weeks since the launch; I expected the product tobe in the channel by now.Anybody else (especially software assurance customers) havingtrouble getting the goods from MCPs and/or resellers?
View 1 Replies View RelatedI'm thinking of storing a lot of images, Word documetns, PDF, and emails as media in SQL Server 2005. Is it equiped to handle a lot of media? Or is it still preferred to store media separately on a file server?
View 6 Replies View RelatedHi i have a video file path stored in my sql database and i have an embedded media player in my web page, how can i load up a different video file into the embedded media player based on movie id or movie name, this is the code i have written so far but im unsure if im in the right direction, thanks
View 2 Replies View RelatedDoes anyone out there work with databases created using sp_create_removable?
I am encountering a situation that is a difference between 6.5 and 7.0 handling of these types of databases. In 6.5, our product creates a removable database on a fixed hard drive. When the user is ready to move the database to a jazz or zip drive, we set the database readonly and remove the log file. No problems. We can use sp_dbinstall witout a log file and everything works just fine.
In 7.0 we do the same thing, however, when we attach the database via sp_attach_db (which replaced sp_dbinstall), SQL Server 7 creates a new log file.
Is there any way to make SQL Server NOT re-create this log file?
We are doing a full backup at night. During the day we are doing a backup of the log every hour. How do I delete the log backups from the media without deleting the device itself. I would like to do this once we have a full backup for the day. The media we are backing up the logs to is disk.
View 3 Replies View RelatedHey All,
I am currently backing up a SQL 2000 Database by doing full backups everynight. I would like to cut this back to once a week and do differential backups during the week. BUT... How can I do differential backups without appending or overwriting the night befores backup? I'm sure this can be done through command line but im not exactly sure of the process. If anyone can help please post...
Thanks,
Jared
We have jumped straight to 64 bit SQL 2005 with no 32 bit 2005 inbetween.
However this means the tools we have cannot be installed on windows XP/2000 32 bit.
Is there some way to get the 32 bit tools as it appears strange that 32 bit tools were not provided for remote access.
Cheers
Julian