A month ago I set up a small program to backup my databases – it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run – so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in
a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups – I appear to have lost a mon
ths
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) – so the backups were happening. What am I do
ing
wrong here? Note that the recovery model was set to FULL.
Please help.Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also – I can’t use Enterprise Manager because it doesn’t have the opti
on to
specify a password – so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE
.
> If you do the restore though enterprise manager, it will the backups too a
nd
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>
>|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
[vbcol=seagreen]
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also – I can’t use Enterprise Manager because it doesn’t have the op
tion to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
>
Showing posts with label databasebackup. Show all posts
Showing posts with label databasebackup. Show all posts
Tuesday, March 20, 2012
Restore script restoring to old version of the database
A month ago I set up a small program to backup my databases – it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run – so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups – I appear to have lost a months
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) – so the backups were happening. What am I doing
wrong here? Note that the recovery model was set to FULL.
Please help.
Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.
|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also – I can’t use Enterprise Manager because it doesn’t have the option to
specify a password – so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE.
> If you do the restore though enterprise manager, it will the backups too and
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>
>
|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
[vbcol=seagreen]
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also – I can’t use Enterprise Manager because it doesn’t have the option to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run – so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups – I appear to have lost a months
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) – so the backups were happening. What am I doing
wrong here? Note that the recovery model was set to FULL.
Please help.
Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.
|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also – I can’t use Enterprise Manager because it doesn’t have the option to
specify a password – so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE.
> If you do the restore though enterprise manager, it will the backups too and
> then you can select which one you want to restore.
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>
>
|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
[vbcol=seagreen]
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also – I can’t use Enterprise Manager because it doesn’t have the option to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
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:
>
Saturday, February 25, 2012
Restore msdb file
I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
Subscribe to:
Posts (Atom)