Hi,
I prepared a backup server for my productive server. I have SQL database
backup every night. I tried to restore the backup to the backup server, but
it failed. Besides IP address and computer name, backup server has the same
configuration as productive server. The database name is the same. How can I
restore a SQL backup to another computer?
Thanks in advance!
LisaIf you show us the RESTORE command and the error message, we can probably help you with the restore
operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
> Hi,
> I prepared a backup server for my productive server. I have SQL database
> backup every night. I tried to restore the backup to the backup server, but
> it failed. Besides IP address and computer name, backup server has the same
> configuration as productive server. The database name is the same. How can I
> restore a SQL backup to another computer?
> Thanks in advance!
> Lisa|||Hi
You don't say what error message you had when it failed. You can backup and
restore from a UNC path so long as permissions allow you to do so. If this is
a scheduled task then the service account for SQL Agent will need those
permissions which means you can't need to use a domain account.
The restore will fail if there are connections using the database you are
trying to restore to. You may have to use the move options if your disc
layouts on the servers are different. If you are using SQL Logins then you
may need to change the corresponding users with sp_change_users_login if they
are orphaned.
John
"Lisa" wrote:
> Hi,
> I prepared a backup server for my productive server. I have SQL database
> backup every night. I tried to restore the backup to the backup server, but
> it failed. Besides IP address and computer name, backup server has the same
> configuration as productive server. The database name is the same. How can I
> restore a SQL backup to another computer?
> Thanks in advance!
> Lisa|||The error message is:
MS SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
See the SQL Server error log for more details.
Restore database is terminating abnormally.
one quick question: can I restore a SQL backup to other SQL server with the
same batabase name?
Thanks,
Lisa
"John Bell" wrote:
> Hi
> You don't say what error message you had when it failed. You can backup and
> restore from a UNC path so long as permissions allow you to do so. If this is
> a scheduled task then the service account for SQL Agent will need those
> permissions which means you can't need to use a domain account.
> The restore will fail if there are connections using the database you are
> trying to restore to. You may have to use the move options if your disc
> layouts on the servers are different. If you are using SQL Logins then you
> may need to change the corresponding users with sp_change_users_login if they
> are orphaned.
> John
> "Lisa" wrote:
> > Hi,
> >
> > I prepared a backup server for my productive server. I have SQL database
> > backup every night. I tried to restore the backup to the backup server, but
> > it failed. Besides IP address and computer name, backup server has the same
> > configuration as productive server. The database name is the same. How can I
> > restore a SQL backup to another computer?
> >
> > Thanks in advance!
> >
> > Lisa|||Hi Tibor,
Thank you for your response. I run it from GUI.
The error message is:
MS SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
See the SQL Server error log for more details.
Restore database is terminating abnormally.
one quick question: can I restore a SQL backup to other SQL server with the
same batabase name?
Thanks,
Lisa
"Tibor Karaszi" wrote:
> If you show us the RESTORE command and the error message, we can probably help you with the restore
> operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
> > Hi,
> >
> > I prepared a backup server for my productive server. I have SQL database
> > backup every night. I tried to restore the backup to the backup server, but
> > it failed. Besides IP address and computer name, backup server has the same
> > configuration as productive server. The database name is the same. How can I
> > restore a SQL backup to another computer?
> >
> > Thanks in advance!
> >
> > Lisa
>|||Hi Lisa
Assuming that the backup was made to the source machine (called LiveServer)
in directory C:\Backup\Backupfile and C:\Backup has a share called
LiveBackups, then the if the backup command was something like:
BACKUP DATABASE MyDatabase
TO DISK = 'c:\Backup\MyDatabase.bak'
If you want to move the data and log files into a C:\Databases directory then
the restore command can be something like:
RESTORE DATABASE MyDatabase
FROM DISK = '\\SourceServer\LiveBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'C:\Databases\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'C:\Databases\MyDatabase.ldf'
The backup command will need to complete before the restore can take place.
You will need permissions to see the \\LiveServer\LiveBackups shared
directory. There are security issues with having a share on the live server
so you may want to backup to a share on the test server instead.
The database can have the same name as it is on a different server
HTH
John
"Lisa" wrote:
> The error message is:
> MS SQL-DMO (ODBC SQLState: 42000)
> Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
> See the SQL Server error log for more details.
> Restore database is terminating abnormally.
> one quick question: can I restore a SQL backup to other SQL server with the
> same batabase name?
> Thanks,
> Lisa
> "John Bell" wrote:
> > Hi
> >
> > You don't say what error message you had when it failed. You can backup and
> > restore from a UNC path so long as permissions allow you to do so. If this is
> > a scheduled task then the service account for SQL Agent will need those
> > permissions which means you can't need to use a domain account.
> >
> > The restore will fail if there are connections using the database you are
> > trying to restore to. You may have to use the move options if your disc
> > layouts on the servers are different. If you are using SQL Logins then you
> > may need to change the corresponding users with sp_change_users_login if they
> > are orphaned.
> >
> > John
> >
> > "Lisa" wrote:
> >
> > > Hi,
> > >
> > > I prepared a backup server for my productive server. I have SQL database
> > > backup every night. I tried to restore the backup to the backup server, but
> > > it failed. Besides IP address and computer name, backup server has the same
> > > configuration as productive server. The database name is the same. How can I
> > > restore a SQL backup to another computer?
> > >
> > > Thanks in advance!
> > >
> > > Lisa|||Remember that the file path is relative to the SQL Server. So the directory and file name specified
need to exist in the server machine where you do the restore.
> one quick question: can I restore a SQL backup to other SQL server with the
> same batabase name?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:AE803E98-A9D8-4C0A-A550-DC1E8B4E8D19@.microsoft.com...
> Hi Tibor,
> Thank you for your response. I run it from GUI.
> The error message is:
> MS SQL-DMO (ODBC SQLState: 42000)
> Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
> See the SQL Server error log for more details.
> Restore database is terminating abnormally.
> one quick question: can I restore a SQL backup to other SQL server with the
> same batabase name?
> Thanks,
> Lisa
>
> "Tibor Karaszi" wrote:
>> If you show us the RESTORE command and the error message, we can probably help you with the
>> restore
>> operation.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
>> > Hi,
>> >
>> > I prepared a backup server for my productive server. I have SQL database
>> > backup every night. I tried to restore the backup to the backup server, but
>> > it failed. Besides IP address and computer name, backup server has the same
>> > configuration as productive server. The database name is the same. How can I
>> > restore a SQL backup to another computer?
>> >
>> > Thanks in advance!
>> >
>> > Lisa
>>
Showing posts with label prepared. Show all posts
Showing posts with label prepared. Show all posts
Tuesday, March 20, 2012
Restore question
Hi,
I prepared a backup server for my productive server. I have SQL database
backup every night. I tried to restore the backup to the backup server, but
it failed. Besides IP address and computer name, backup server has the same
configuration as productive server. The database name is the same. How can I
restore a SQL backup to another computer?
Thanks in advance!
LisaIf you show us the RESTORE command and the error message, we can probably he
lp you with the restore
operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
> Hi,
> I prepared a backup server for my productive server. I have SQL database
> backup every night. I tried to restore the backup to the backup server, bu
t
> it failed. Besides IP address and computer name, backup server has the sam
e
> configuration as productive server. The database name is the same. How can
I
> restore a SQL backup to another computer?
> Thanks in advance!
> Lisa|||Hi
You don't say what error message you had when it failed. You can backup and
restore from a UNC path so long as permissions allow you to do so. If this i
s
a scheduled task then the service account for SQL Agent will need those
permissions which means you can't need to use a domain account.
The restore will fail if there are connections using the database you are
trying to restore to. You may have to use the move options if your disc
layouts on the servers are different. If you are using SQL Logins then you
may need to change the corresponding users with sp_change_users_login if the
y
are orphaned.
John
"Lisa" wrote:
> Hi,
> I prepared a backup server for my productive server. I have SQL database
> backup every night. I tried to restore the backup to the backup server, bu
t
> it failed. Besides IP address and computer name, backup server has the sam
e
> configuration as productive server. The database name is the same. How can
I
> restore a SQL backup to another computer?
> Thanks in advance!
> Lisa|||The error message is:
MS SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
See the SQL Server error log for more details.
Restore database is terminating abnormally.
one quick question: can I restore a SQL backup to other SQL server with the
same batabase name?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't say what error message you had when it failed. You can backup an
d
> restore from a UNC path so long as permissions allow you to do so. If this
is
> a scheduled task then the service account for SQL Agent will need those
> permissions which means you can't need to use a domain account.
> The restore will fail if there are connections using the database you are
> trying to restore to. You may have to use the move options if your disc
> layouts on the servers are different. If you are using SQL Logins then you
> may need to change the corresponding users with sp_change_users_login if t
hey
> are orphaned.
> John
> "Lisa" wrote:
>|||Hi Tibor,
Thank you for your response. I run it from GUI.
The error message is:
MS SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
See the SQL Server error log for more details.
Restore database is terminating abnormally.
one quick question: can I restore a SQL backup to other SQL server with the
same batabase name?
Thanks,
Lisa
"Tibor Karaszi" wrote:
> If you show us the RESTORE command and the error message, we can probably
help you with the restore
> operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
>|||Hi Lisa
Assuming that the backup was made to the source machine (called LiveServer)
in directory C:\Backup\Backupfile and C:\Backup has a share called
LiveBackups, then the if the backup command was something like:
BACKUP DATABASE MyDatabase
TO DISK = 'c:\Backup\MyDatabase.bak'
If you want to move the data and log files into a C:\Databases directory the
n
the restore command can be something like:
RESTORE DATABASE MyDatabase
FROM DISK = '\\SourceServer\LiveBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'C:\Databases\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'C:\Databases\MyDatabase.ldf'
The backup command will need to complete before the restore can take place.
You will need permissions to see the \\LiveServer\LiveBackups shared
directory. There are security issues with having a share on the live server
so you may want to backup to a share on the test server instead.
The database can have the same name as it is on a different server
HTH
John
"Lisa" wrote:
[vbcol=seagreen]
> The error message is:
> MS SQL-DMO (ODBC SQLState: 42000)
> Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
> See the SQL Server error log for more details.
> Restore database is terminating abnormally.
> one quick question: can I restore a SQL backup to other SQL server with th
e
> same batabase name?
> Thanks,
> Lisa
> "John Bell" wrote:
>|||Remember that the file path is relative to the SQL Server. So the directory
and file name specified
need to exist in the server machine where you do the restore.
> one quick question: can I restore a SQL backup to other SQL server with th
e
> same batabase name?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:AE803E98-A9D8-4C0A-A550-DC1E8B4E8D19@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Thank you for your response. I run it from GUI.
> The error message is:
> MS SQL-DMO (ODBC SQLState: 42000)
> Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
> See the SQL Server error log for more details.
> Restore database is terminating abnormally.
> one quick question: can I restore a SQL backup to other SQL server with th
e
> same batabase name?
> Thanks,
> Lisa
>
> "Tibor Karaszi" wrote:
>
I prepared a backup server for my productive server. I have SQL database
backup every night. I tried to restore the backup to the backup server, but
it failed. Besides IP address and computer name, backup server has the same
configuration as productive server. The database name is the same. How can I
restore a SQL backup to another computer?
Thanks in advance!
LisaIf you show us the RESTORE command and the error message, we can probably he
lp you with the restore
operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
> Hi,
> I prepared a backup server for my productive server. I have SQL database
> backup every night. I tried to restore the backup to the backup server, bu
t
> it failed. Besides IP address and computer name, backup server has the sam
e
> configuration as productive server. The database name is the same. How can
I
> restore a SQL backup to another computer?
> Thanks in advance!
> Lisa|||Hi
You don't say what error message you had when it failed. You can backup and
restore from a UNC path so long as permissions allow you to do so. If this i
s
a scheduled task then the service account for SQL Agent will need those
permissions which means you can't need to use a domain account.
The restore will fail if there are connections using the database you are
trying to restore to. You may have to use the move options if your disc
layouts on the servers are different. If you are using SQL Logins then you
may need to change the corresponding users with sp_change_users_login if the
y
are orphaned.
John
"Lisa" wrote:
> Hi,
> I prepared a backup server for my productive server. I have SQL database
> backup every night. I tried to restore the backup to the backup server, bu
t
> it failed. Besides IP address and computer name, backup server has the sam
e
> configuration as productive server. The database name is the same. How can
I
> restore a SQL backup to another computer?
> Thanks in advance!
> Lisa|||The error message is:
MS SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
See the SQL Server error log for more details.
Restore database is terminating abnormally.
one quick question: can I restore a SQL backup to other SQL server with the
same batabase name?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't say what error message you had when it failed. You can backup an
d
> restore from a UNC path so long as permissions allow you to do so. If this
is
> a scheduled task then the service account for SQL Agent will need those
> permissions which means you can't need to use a domain account.
> The restore will fail if there are connections using the database you are
> trying to restore to. You may have to use the move options if your disc
> layouts on the servers are different. If you are using SQL Logins then you
> may need to change the corresponding users with sp_change_users_login if t
hey
> are orphaned.
> John
> "Lisa" wrote:
>|||Hi Tibor,
Thank you for your response. I run it from GUI.
The error message is:
MS SQL-DMO (ODBC SQLState: 42000)
Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
See the SQL Server error log for more details.
Restore database is terminating abnormally.
one quick question: can I restore a SQL backup to other SQL server with the
same batabase name?
Thanks,
Lisa
"Tibor Karaszi" wrote:
> If you show us the RESTORE command and the error message, we can probably
help you with the restore
> operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:3B6B6872-EAA8-4921-AC4C-C1A0FE933580@.microsoft.com...
>|||Hi Lisa
Assuming that the backup was made to the source machine (called LiveServer)
in directory C:\Backup\Backupfile and C:\Backup has a share called
LiveBackups, then the if the backup command was something like:
BACKUP DATABASE MyDatabase
TO DISK = 'c:\Backup\MyDatabase.bak'
If you want to move the data and log files into a C:\Databases directory the
n
the restore command can be something like:
RESTORE DATABASE MyDatabase
FROM DISK = '\\SourceServer\LiveBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase_Data' TO 'C:\Databases\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'C:\Databases\MyDatabase.ldf'
The backup command will need to complete before the restore can take place.
You will need permissions to see the \\LiveServer\LiveBackups shared
directory. There are security issues with having a share on the live server
so you may want to backup to a share on the test server instead.
The database can have the same name as it is on a different server
HTH
John
"Lisa" wrote:
[vbcol=seagreen]
> The error message is:
> MS SQL-DMO (ODBC SQLState: 42000)
> Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
> See the SQL Server error log for more details.
> Restore database is terminating abnormally.
> one quick question: can I restore a SQL backup to other SQL server with th
e
> same batabase name?
> Thanks,
> Lisa
> "John Bell" wrote:
>|||Remember that the file path is relative to the SQL Server. So the directory
and file name specified
need to exist in the server machine where you do the restore.
> one quick question: can I restore a SQL backup to other SQL server with th
e
> same batabase name?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:AE803E98-A9D8-4C0A-A550-DC1E8B4E8D19@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Thank you for your response. I run it from GUI.
> The error message is:
> MS SQL-DMO (ODBC SQLState: 42000)
> Cannot open backup device 'C:\Backup\XXX". Device error or device offline.
> See the SQL Server error log for more details.
> Restore database is terminating abnormally.
> one quick question: can I restore a SQL backup to other SQL server with th
e
> same batabase name?
> Thanks,
> Lisa
>
> "Tibor Karaszi" wrote:
>
Subscribe to:
Posts (Atom)