Backup device?

Hi all,
I want to store bakup file on the computer on netword, so make a backup device on the other computer, it ok. after that i make an full backup, there is an error : "Can not open the bakup device " .
please show me,
best regard.share that network PC drive and if u still have any problem login into both pc with same login id...

that will surely resolve ur problem..

Cheers!!|||Originally posted by thanhtung2003
Hi all,

I want to store bakup file on the computer on netword, so make a backup device on the other computer, it ok. after that i make an full backup, there is an error : "Can not open the bakup device " .
please show me,
best regard.
Make sure you have write permission in that folder.
Or create log on credential at that newwork PC.

Backup db to another computer?

Is this the correct NG for a backup Q?
Right now I'm wondering if the only way to copy a backup file from one serve
r to
another is by DTS(?)
1. I'd like to schedule a back from one server to another recieving server t
o be
used in an emergency.
2. I'd like to be able to automate the restore of that database on the recie
ving
emergency server to reduce recovery time in the event of the primary server
becoming unavailable.
TIA
JeffP...> Right now I'm wondering if the only way to copy a backup file from one
> server to
> another is by DTS(?)
No. You can use batch files or ActiveX scripts (FileSystem object) - both
of which can be scheduled as SQL jobs.
You might look into Log Shipping - not automated for the restore but for the
backup and copy including transaction log backups.
HTH
Jerry
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ewfxk$dfGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Is this the correct NG for a backup Q?
> Right now I'm wondering if the only way to copy a backup file from one
> server to
> another is by DTS(?)
> 1. I'd like to schedule a back from one server to another recieving server
> to be
> used in an emergency.
> 2. I'd like to be able to automate the restore of that database on the
> recieving
> emergency server to reduce recovery time in the event of the primary
> server
> becoming unavailable.
> TIA
> JeffP...
>|||You can take backup directly on remote server. You need to share drive
on remote server with proper permission.
Also you can use net use and map a drive at time of backup.
For high availability use log shipping , replication or if you are
using SQL Server 2005 you can use mirroring.
Regards
Amish Shah.

Backup database over network not working.

The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.

On P5 I created the Backup Device:

USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\\P3\temp\test.bak'

Then I executed the following on P5 Query Analyzer:

Backup Database myDatabase TO myDevice

But I get this error:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. 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.

Any idea? Thanks and regards.Originally posted by juniper
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.

On P5 I created the Backup Device:

USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\\P3\temp\test.bak'

Then I executed the following on P5 Query Analyzer:

Backup Database myDatabase TO myDevice

But I get this error:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. 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.

Any idea? Thanks and regards.

yeah, go into Enterprise Manager, right click on the db you are trying to backup and all tasks - bring online|||Better yet...don't dump across a network...

It'll be much slower and be at risk for blips in the network...

Dump local then copy...|||Hi guys, thanks for your advice. Brett, I am doing it the way you suggested. However, it would mean that the computer running the database server would have to have a folder permanently shared for my program to copy the file across the network.

Any workaround so that I do not need to share folders? The user might inadvertently unshare the folder and would create problems.

Thanks and regards.|||Ensure the account used to execute this task does have required privileges to access the share specified.

If you're scheduling this task then ensure SQLAgent account used does have admin privileges or a domain account to access so.

Backup cant find database name

I was trying to backup my database file using My.Computer.FileSystem.CopyFile but because SQL keeps the file locked this doesnt work (I am using VB.NET 2005 Express and SQL 2005 Express). It seems that the Backup command is what I want to do but it doesnt recognise my database name, which otherwise appears in the Database Explorer tab of the IDE

I wrote a function to use the Backup command as follows
Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
Debug.Print(sqlconn.Database)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE MyDatabase TO DISK = 'c:\Mydatabase.bak'")

Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()

End Sub

But this gives the error
Could not locate entry in sysdatabases for database 'Mydatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

I have also tried with the .mdf extension, with the full path name, and with the name as given by sqlconn.database which is supposed to be the full database name, with and without single quotes surrounding it.

How can I find out which database name this function requires and why it is not finding the database? Other queries on it are working fine.

Thanks

Martin

hi,

the Transact SQL syntax is correct... but this works against "traditional" SQL Server (and SQLExpress) instances... are you perhaps working with a user instance (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp)?

if this is the case, the database name you see in the IDE (VS ide or SQL Server Management Studio IDE?) is not the same as the registered database name in the running SQLExpress user instance.. the name is composed with the file + path of the .Mdf file the database is composed of..

regards

|||

Andrea Montanari wrote:

hi,

the Transact SQL syntax is correct... but this works against "traditional" SQL Server (and SQLExpress) instances...

should be read as "but this works directly against "traditional" SQL Server (and SQLExpress) instances... user istances modify the database name as reported..

I apologise..

regards

|||Thanks Andrea for the link to the article on User Instances, I was using that method as it was the default method used in the connection string generated by the database wizard, so I did need the full path. But that was not the full story, because I still had problems getting SQL to recognise this database name.
In the end, the correct database name was given in the connection object sqlconn.database but because it had had its first few characters replaced with some sort of unique identifier which started with numbers and an E, the Backup command seemed to think it was a floating point number even though it was expecting a database name at this position in the command. Putting it in single quotes did not work, but after lots of guessing different syntax variations, I tried square brackets and it worked.

For anyone else with this problem, here is the form of my solution:

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:\Mydatabase.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub|||

Hi, I'm trying to do exactly the same thing as you did. You solution is a great help to me. However, when I modify the sqlCommand for Restore operation, I won't allow me since the database is currently used by my application. Would you tell me how you did that? Thanks.

Public Sub RestoreOperation(ByVal restorePath As String)

Dim sqlconn As New SqlClient.SqlConnection(GlobalConnectionString)

Try

' the command for restoring the DB

Dim cmdBackup As New SqlClient.SqlCommand("RESTORE DATABASE [" & "Test.mdf" & _

"] FROM DISK = '" & restorePath & "'", sqlconn)

sqlconn.Open()

cmdBackup.ExecuteNonQuery()

Finally

sqlconn.Close()

End Try

End Sub

|||

hi,

modify the context of your connection via con.ChangeDatabase or close it and open another one referencing the master system database...

regards

|||

Hi Andrea, thanks for your suggestion. It seems very good to me. However, I have a problem. Since I'm new in ADO.net, I use the most basic solution (drag-and-drop from Data source Explorer) to setup my database. So my program have a bunch of forms and each have their own TableAdapters. So other than closing all the connections from each adapter, is there other way to do it? Can it be done by SQL? Appreciated.

This is my connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True;Connect Timeout=60;User Instance=True

|||

hi,

actually Inever use your approach, thus drag&drop ...but, anyway, all database connections against the database to be restored must be closed before restoring...

regards

|||Yeah, I guess I don't have much choice. Thanks for your reply.

Backup cant find database name

I was trying to backup my database file using My.Computer.FileSystem.CopyFile but because SQL keeps the file locked this doesnt work (I am using VB.NET 2005 Express and SQL 2005 Express). It seems that the Backup command is what I want to do but it doesnt recognise my database name, which otherwise appears in the Database Explorer tab of the IDE

I wrote a function to use the Backup command as follows
Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
Debug.Print(sqlconn.Database)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE MyDatabase TO DISK = 'c:\Mydatabase.bak'")

Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()

End Sub

But this gives the error
Could not locate entry in sysdatabases for database 'Mydatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

I have also tried with the .mdf extension, with the full path name, and with the name as given by sqlconn.database which is supposed to be the full database name, with and without single quotes surrounding it.

How can I find out which database name this function requires and why it is not finding the database? Other queries on it are working fine.

Thanks

Martin

hi,

the Transact SQL syntax is correct... but this works against "traditional" SQL Server (and SQLExpress) instances... are you perhaps working with a user instance (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp)?

if this is the case, the database name you see in the IDE (VS ide or SQL Server Management Studio IDE?) is not the same as the registered database name in the running SQLExpress user instance.. the name is composed with the file + path of the .Mdf file the database is composed of..

regards

|||

Andrea Montanari wrote:

hi,

the Transact SQL syntax is correct... but this works against "traditional" SQL Server (and SQLExpress) instances...

should be read as "but this works directly against "traditional" SQL Server (and SQLExpress) instances... user istances modify the database name as reported..

I apologise..

regards

|||Thanks Andrea for the link to the article on User Instances, I was using that method as it was the default method used in the connection string generated by the database wizard, so I did need the full path. But that was not the full story, because I still had problems getting SQL to recognise this database name.
In the end, the correct database name was given in the connection object sqlconn.database but because it had had its first few characters replaced with some sort of unique identifier which started with numbers and an E, the Backup command seemed to think it was a floating point number even though it was expecting a database name at this position in the command. Putting it in single quotes did not work, but after lots of guessing different syntax variations, I tried square brackets and it worked.

For anyone else with this problem, here is the form of my solution:

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:\Mydatabase.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub|||

Hi, I'm trying to do exactly the same thing as you did. You solution is a great help to me. However, when I modify the sqlCommand for Restore operation, I won't allow me since the database is currently used by my application. Would you tell me how you did that? Thanks.

Public Sub RestoreOperation(ByVal restorePath As String)

Dim sqlconn As New SqlClient.SqlConnection(GlobalConnectionString)

Try

' the command for restoring the DB

Dim cmdBackup As New SqlClient.SqlCommand("RESTORE DATABASE [" & "Test.mdf" & _

"] FROM DISK = '" & restorePath & "'", sqlconn)

sqlconn.Open()

cmdBackup.ExecuteNonQuery()

Finally

sqlconn.Close()

End Try

End Sub

|||

hi,

modify the context of your connection via con.ChangeDatabase or close it and open another one referencing the master system database...

regards

|||

Hi Andrea, thanks for your suggestion. It seems very good to me. However, I have a problem. Since I'm new in ADO.net, I use the most basic solution (drag-and-drop from Data source Explorer) to setup my database. So my program have a bunch of forms and each have their own TableAdapters. So other than closing all the connections from each adapter, is there other way to do it? Can it be done by SQL? Appreciated.

This is my connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True;Connect Timeout=60;User Instance=True

|||

hi,

actually Inever use your approach, thus drag&drop ...but, anyway, all database connections against the database to be restored must be closed before restoring...

regards

|||Yeah, I guess I don't have much choice. Thanks for your reply.

Backup and Restore on different computer?

Hi,
I use SQL Server 2005. In computer "A", I backup a database "AAA" to a
file "AAA_Back.bak". When I try to restore on computer "B", error message
shows up, it try to find the original backup file path in computer "A".

How can I fix this problem?

Thanks

KaiFirst, the path must be specified using a UNC name, not a mapped drive
letter: \\ServerA\Sharename\foldername\filename.ext

Second, the account under which the SQL Server service is running must
have rights to access that location. Test this:

EXEC master..xp_cmdshell 'dir \\ServerA\Sharename\foldername\*.*'

If the service is running under the local system account it will NOT
have any network rights, and will not be able to access the file.

Roy Harvey
Beacon Falls, CT

On Tue, 11 Jul 2006 20:51:07 GMT, "kai" <kailiang@.earthlink.net>
wrote:

Quote:

Originally Posted by

>Hi,
I use SQL Server 2005. In computer "A", I backup a database "AAA" to a
>file "AAA_Back.bak". When I try to restore on computer "B", error message
>shows up, it try to find the original backup file path in computer "A".
>
>How can I fix this problem?
>
>
>Thanks
>
>Kai

|||Roy,
Thanks for your help.
Kai
"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:6148b29emu0679q9d41k6q27g5a05g8hgg@.4ax.com...

Quote:

Originally Posted by

First, the path must be specified using a UNC name, not a mapped drive
letter: \\ServerA\Sharename\foldername\filename.ext
>
Second, the account under which the SQL Server service is running must
have rights to access that location. Test this:
>
EXEC master..xp_cmdshell 'dir \\ServerA\Sharename\foldername\*.*'
>
If the service is running under the local system account it will NOT
have any network rights, and will not be able to access the file.
>
Roy Harvey
Beacon Falls, CT
>
On Tue, 11 Jul 2006 20:51:07 GMT, "kai" <kailiang@.earthlink.net>
wrote:
>

Quote:

Originally Posted by

>>Hi,
> I use SQL Server 2005. In computer "A", I backup a database "AAA" to a
>>file "AAA_Back.bak". When I try to restore on computer "B", error message
>>shows up, it try to find the original backup file path in computer "A".
>>
>>How can I fix this problem?
>>
>>
>>Thanks
>>
>>Kai

Backup a remote SQL Server to local computer?

Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?I was thinking of that too. A straight up backup won't work, SQL needsa local device for that. You should be able to replicate the data to alocal database and back that up though. I tried and it didn't work.It's been a while and I haven't persued it so I'm sorry that I can'tprovide details. Take a look in Enterprise Manager, I think I tried theExport function. Of course any backup is only as good as its recoverycapabilities. Whatever you come up with, try it out *before* you need arecovery done :)
|||I think only a database backup tool will do what you want to do. If it is a company network this is what I have done remote into the server do the backup use Winzip to zip the file and email it to whoever needs it yourself included. When restoring use restore from device option in the backup wizard. Hope this helps.