If I make a database backup with the following command
backup database SkatingResults to SkatingResultsBackUp with skip, format,
DESCRIPTION='Skating Results Backup', MEDIADESCRIPTION='Disk File'
GO
I am wanting to restore that database backup onto another computer.
Since the computer that I will be going will not be restoring to the same
locatin, I must use the "move" option of the restore.
Here is where I am confuse, what does my restore statement look like. The
example I saw in BOL says I should "Determine the number and names of the
files in the backup" by executing "RESTORE FILELISTONLY". The example of
the command is as follows:
RESTORE FILELISTONLY
FROM MyNwind_1
What is "MyNwind_1" - is this the "backup devise". The Backup Devise
points the computer to the actual backup file to work with?
Would I have use a statement like the following to establish the "Backup
Devise"?
exec sp_addumpdevice 'disk', 'SkatingResultsBackUp', 'H:\Temp
\SkatingResultsBackup.dat'
Then I would place my backup file in this location and perform the "RESTORE
FILELISTONLY" and "RESTORE DATABASE". There is no need for me to restor
the transaction log if my database is empty?
Thanks inadvance for your assistance!!!!!!!!!!Jim,
I am not sure what your back statement creates, since I have never used that
syntax. I usually use this:
BACKUP DATABASE DB1TO Disk='C:\MSSQL\Backup\DB1.bck'
Assuming it creates a disk file called SkatingResultsBackUp.bck or SkatingRe
sultsBackUp.bak you can issue this command:
RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\BACKUP\SkatingResultsBackUp.bak'
replacing the path with yours
The output of this command basically list what is in the sysfiles table.
Assuming that you database consist of two files, 1 data and 1 log,
Restore the database with this command:
RESTORE DATABASE SkatingResults FROM DISK = 'D:\MSSQL7\BACKUP\SkatingResults
BackUp.bck'
WITH MOVE 'SkatingResults_dat' TO 'D:\MSSQL\Data\SkatingResults_Data.mdf',
MOVE 'SkatingResults_log' TO 'D:\MSSQL\Data\SkatingResults_log.ldf'
again replacing the paths with yours
HTH,
Norman
Showing posts with label skip. Show all posts
Showing posts with label skip. Show all posts
Wednesday, March 7, 2012
Database Restore
If I make a database backup with the following command
backup database SkatingResults to SkatingResultsBackUp with skip, format,
DESCRIPTION='Skating Results Backup', MEDIADESCRIPTION='Disk File'
GO
I am wanting to restore that database backup onto another computer.
Since the computer that I will be going will not be restoring to the same
locatin, I must use the "move" option of the restore.
Here is where I am confuse, what does my restore statement look like. The
example I saw in BOL says I should "Determine the number and names of the
files in the backup" by executing "RESTORE FILELISTONLY". The example of
the command is as follows:
RESTORE FILELISTONLY
FROM MyNwind_1
What is "MyNwind_1" - is this the "backup devise". The Backup Devise
points the computer to the actual backup file to work with?
Would I have use a statement like the following to establish the "Backup
Devise"?
exec sp_addumpdevice 'disk', 'SkatingResultsBackUp', 'H:\Temp
\SkatingResultsBackup.dat'
Then I would place my backup file in this location and perform the "RESTORE
FILELISTONLY" and "RESTORE DATABASE". There is no need for me to restor
the transaction log if my database is empty?
Thanks inadvance for your assistance!!!!!!!!!!Jim
I am not sure what your back statement creates, since I have never used that syntax. I usually use this
BACKUP DATABASE DB1TO Disk='C:\MSSQL\Backup\DB1.bck
Assuming it creates a disk file called SkatingResultsBackUp.bck or SkatingResultsBackUp.bak you can issue this command
RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\BACKUP\SkatingResultsBackUp.bak
replacing the path with your
The output of this command basically list what is in the sysfiles table
Assuming that you database consist of two files, 1 data and 1 log
Restore the database with this command
RESTORE DATABASE SkatingResults FROM DISK = 'D:\MSSQL7\BACKUP\SkatingResultsBackUp.bck
WITH MOVE 'SkatingResults_dat' TO 'D:\MSSQL\Data\SkatingResults_Data.mdf'
MOVE 'SkatingResults_log' TO 'D:\MSSQL\Data\SkatingResults_log.ldf
again replacing the paths with your
HTH
Norman
backup database SkatingResults to SkatingResultsBackUp with skip, format,
DESCRIPTION='Skating Results Backup', MEDIADESCRIPTION='Disk File'
GO
I am wanting to restore that database backup onto another computer.
Since the computer that I will be going will not be restoring to the same
locatin, I must use the "move" option of the restore.
Here is where I am confuse, what does my restore statement look like. The
example I saw in BOL says I should "Determine the number and names of the
files in the backup" by executing "RESTORE FILELISTONLY". The example of
the command is as follows:
RESTORE FILELISTONLY
FROM MyNwind_1
What is "MyNwind_1" - is this the "backup devise". The Backup Devise
points the computer to the actual backup file to work with?
Would I have use a statement like the following to establish the "Backup
Devise"?
exec sp_addumpdevice 'disk', 'SkatingResultsBackUp', 'H:\Temp
\SkatingResultsBackup.dat'
Then I would place my backup file in this location and perform the "RESTORE
FILELISTONLY" and "RESTORE DATABASE". There is no need for me to restor
the transaction log if my database is empty?
Thanks inadvance for your assistance!!!!!!!!!!Jim
I am not sure what your back statement creates, since I have never used that syntax. I usually use this
BACKUP DATABASE DB1TO Disk='C:\MSSQL\Backup\DB1.bck
Assuming it creates a disk file called SkatingResultsBackUp.bck or SkatingResultsBackUp.bak you can issue this command
RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\BACKUP\SkatingResultsBackUp.bak
replacing the path with your
The output of this command basically list what is in the sysfiles table
Assuming that you database consist of two files, 1 data and 1 log
Restore the database with this command
RESTORE DATABASE SkatingResults FROM DISK = 'D:\MSSQL7\BACKUP\SkatingResultsBackUp.bck
WITH MOVE 'SkatingResults_dat' TO 'D:\MSSQL\Data\SkatingResults_Data.mdf'
MOVE 'SkatingResults_log' TO 'D:\MSSQL\Data\SkatingResults_log.ldf
again replacing the paths with your
HTH
Norman
Friday, February 24, 2012
database recovery
Is it possible to ignore / skip database recovery at SQL server startup?
Please reply.
Ram.If you restore master it wouldn't know about the user databases until you re-attached them.
If move or rename the .mdf and .ldf files it would error out.
What are you trying to do?|||Why do you want to do so, let SQL work it out to keep the consistency.|||About the best you yourself can do is set the Recovery Interval to be a bit lower (one minute, or so), but this will have an effect of lowering performance on your system overall. Lowering the Recovery Interval will increase the number of checkpoints that SQL Server is issuing.
After that, you have to try to be sure that you are not restarting the SQL Server service midway through a large transaction. An old rule of thumb was that it took SQL Server almost twice as long to back oout of a transaction as it took to get this far through. So, if you have been waiting staring at Query Analyzer for 20 minutes, you may have to wait up to 40 minutes after you hit "cancel query". Same could apply to restarting the SQL Service. Have you poked around to see if there are any extra-long/piggish transactions that someone may have put in a "begin/commit transaction"?
Please reply.
Ram.If you restore master it wouldn't know about the user databases until you re-attached them.
If move or rename the .mdf and .ldf files it would error out.
What are you trying to do?|||Why do you want to do so, let SQL work it out to keep the consistency.|||About the best you yourself can do is set the Recovery Interval to be a bit lower (one minute, or so), but this will have an effect of lowering performance on your system overall. Lowering the Recovery Interval will increase the number of checkpoints that SQL Server is issuing.
After that, you have to try to be sure that you are not restarting the SQL Server service midway through a large transaction. An old rule of thumb was that it took SQL Server almost twice as long to back oout of a transaction as it took to get this far through. So, if you have been waiting staring at Query Analyzer for 20 minutes, you may have to wait up to 40 minutes after you hit "cancel query". Same could apply to restarting the SQL Service. Have you poked around to see if there are any extra-long/piggish transactions that someone may have put in a "begin/commit transaction"?
Subscribe to:
Posts (Atom)