RESTORE WITH REPLACE

Hi all, I am restoring a database just like from BOL
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH FILE = 1, STATS=10, RECOVERY,
MOVE 'MyNwind' TO 'c:\MSSQL\Data\NewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\MSSQL\Data\NewNwind.ldf'
I do have the database MyNwind already, but it
it is restored anyway without any problems or warnings. I though that one
would need to use the REPLACE clause to achieve this.
The REPLACE option is not needed when the source and target database names
are the same. From the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_ra-rz_25rm.htm">
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.
REPLACE also allows RESTORE to overwrite an existing file which cannot be
verified as belonging to the database being restored. Normally, RESTORE will
refuse to overwrite pre-existing files.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:CB543689-E5A9-4253-AF29-01048D208C1C@.microsoft.com...
> Hi all, I am restoring a database just like from BOL
> RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH FILE = 1, STATS=10, RECOVERY,
> MOVE 'MyNwind' TO 'c:\MSSQL\Data\NewNwind.mdf',
> MOVE 'MyNwindLog1' TO 'c:\MSSQL\Data\NewNwind.ldf'
> I do have the database MyNwind already, but it
> it is restored anyway without any problems or warnings. I though that one
> would need to use the REPLACE clause to achieve this.
>

RESTORE WITH REPLACE

Hi all, I am restoring a database just like from BOL
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH FILE = 1, STATS=10, RECOVERY,
MOVE 'MyNwind' TO 'c:\MSSQL\Data\NewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\MSSQL\Data\NewNwind.ldf'
I do have the database MyNwind already, but it
it is restored anyway without any problems or warnings. I though that one
would need to use the REPLACE clause to achieve this.The REPLACE option is not needed when the source and target database names
are the same. From the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_ra-rz_25rm.htm">
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.
REPLACE also allows RESTORE to overwrite an existing file which cannot be
verified as belonging to the database being restored. Normally, RESTORE will
refuse to overwrite pre-existing files.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:CB543689-E5A9-4253-AF29-01048D208C1C@.microsoft.com...
> Hi all, I am restoring a database just like from BOL
> RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH FILE = 1, STATS=10, RECOVERY,
> MOVE 'MyNwind' TO 'c:\MSSQL\Data\NewNwind.mdf',
> MOVE 'MyNwindLog1' TO 'c:\MSSQL\Data\NewNwind.ldf'
> I do have the database MyNwind already, but it
> it is restored anyway without any problems or warnings. I though that one
> would need to use the REPLACE clause to achieve this.
>

RESTORE WITH REPLACE

Hi all, I am restoring a database just like from BOL
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH FILE = 1, STATS=10, RECOVERY,
MOVE 'MyNwind' TO 'c:\MSSQL\Data\NewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\MSSQL\Data\NewNwind.ldf'
I do have the database MyNwind already, but it
it is restored anyway without any problems or warnings. I though that one
would need to use the REPLACE clause to achieve this.The REPLACE option is not needed when the source and target database names
are the same. From the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_ra-rz_25rm.htm">
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.
REPLACE also allows RESTORE to overwrite an existing file which cannot be
verified as belonging to the database being restored. Normally, RESTORE will
refuse to overwrite pre-existing files.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:CB543689-E5A9-4253-AF29-01048D208C1C@.microsoft.com...
> Hi all, I am restoring a database just like from BOL
> RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH FILE = 1, STATS=10, RECOVERY,
> MOVE 'MyNwind' TO 'c:\MSSQL\Data\NewNwind.mdf',
> MOVE 'MyNwindLog1' TO 'c:\MSSQL\Data\NewNwind.ldf'
> I do have the database MyNwind already, but it
> it is restored anyway without any problems or warnings. I though that one
> would need to use the REPLACE clause to achieve this.
>

RESTORE with RECOVERY and REPLACE?

Thanks to those who responded earlier. If someone would check my commands
below, I would appreciate it.
Again, what I tried to do is create a new database from an existing template
database. To do this, I tried (in Enterprise Manager) to restore from the
template to a new database name. However, something went wrong and at the
end of the restore process I got an error about "log begins at 30000 and is
too late to apply to database." The new database is stuck with a (loading)
next to it.
So I am going to try to do it in Query AnalyzeR with the RECOVERY option.
After trying to decipher the books online, this is what I came up with:
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH RECOVERY, REPLACE
Thank youJust to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
Tahnks|||Actually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!
> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE|||"mike" <mike@.commmcasssttt.com> wrote in message
news:12a7td2rqcpoo7d@.corp.supernews.com...
> Well, that did not work. It says no entry in sysdevices for
> 'c:\mybackups\template.bak'
>
below is a script that you can adapt for your own purposes. You really need
to read BOL for the commands involved to make sure you understand exactly
what happens. BOL also has many useful examples. To restore to a new
database from a backup of an existing database (the template in your
description), just use a new database name in the restore command ("test_db"
in this example) and be sure to specify the files you want to use for the
database (the move options). The 2nd command is useful to identify the
logical names (used by the database in the backup) that need to be moved.
use master
go
exec xp_cmdshell 'dir C:\sql2k\MSSQL\BACKUP\ /o-d'
go
RESTORE FILELISTONLY
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
GO
RESTORE DATABASE test_db
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
WITH RECOVERY, STATS, REPLACE,
MOVE 'main_Data' TO 'C:\sql2k\MSSQL\DATA\test_db_DATA.mdf',
MOVE 'main_Log' TO 'C:\sql2k\MSSQL\DATA\test_db_Log.ldf'
GOsql

RESTORE with RECOVERY and REPLACE?

Just to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
TahnksActually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!

> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE|||"mike" <mike@.commmcasssttt.com> wrote in message
news:12a7td2rqcpoo7d@.corp.supernews.com...
> Well, that did not work. It says no entry in sysdevices for
> 'c:\mybackups\template.bak'
>
below is a script that you can adapt for your own purposes. You really need
to read BOL for the commands involved to make sure you understand exactly
what happens. BOL also has many useful examples. To restore to a new
database from a backup of an existing database (the template in your
description), just use a new database name in the restore command ("test_db"
in this example) and be sure to specify the files you want to use for the
database (the move options). The 2nd command is useful to identify the
logical names (used by the database in the backup) that need to be moved.
use master
go
exec xp_cmdshell 'dir C:\sql2k\MSSQL\BACKUP\ /o-d'
go
RESTORE FILELISTONLY
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
GO
RESTORE DATABASE test_db
FROM DISK='C:\sql2k\MSSQL\BACKUP\save_me.BAK'
WITH RECOVERY, STATS, REPLACE,
MOVE 'main_Data' TO 'C:\sql2k\MSSQL\DATA\test_db_DATA.mdf',
MOVE 'main_Log' TO 'C:\sql2k\MSSQL\DATA\test_db_Log.ldf'
GO|||Thanks to those who responded earlier. If someone would check my commands
below, I would appreciate it.
Again, what I tried to do is create a new database from an existing template
database. To do this, I tried (in Enterprise Manager) to restore from the
template to a new database name. However, something went wrong and at the
end of the restore process I got an error about "log begins at 30000 and is
too late to apply to database." The new database is stuck with a (loading)
next to it.
So I am going to try to do it in Query AnalyzeR with the RECOVERY option.
After trying to decipher the books online, this is what I came up with:
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH RECOVERY, REPLACE
Thank you|||Just to be clear, I am not restoring to a different machine. Just trying to
create a new database (which is not hanging from my first attempt) from an
existing database.
Tahnks|||Actually, it seems that RECOVERY is the default, so maybe I don't need to
specify it.
A better command might be
RESTORE DATABASE stuckdb
FROM c:\mybackups\template.bak
WITH REPLACE|||Well, that did not work. It says no entry in sysdevices for
'c:\mybackups\template.bak'|||Tried adding DISK and putting a single quote around the path. Seems to have
worked.
Thanks!

> RESTORE DATABASE stuckdb
> FROM DISK = 'c:\mybackups\template.bak'
> WITH REPLACE

Restore Tranaction log

I am trying to restore a Transaction Log in SQL Server 2000 in Full recovery status, However I was received the following error Message:
----------------------------------------
Error message: Exclusive access could not be obtained because the database is in use.
----------------------------------------
Also I changed the UserAccess database from MULTI_USER to SINGLE_USER, but still do not work and shows the same error message.
Please let me know, if you have any solution for this problem.

Thanks,

Try the link below to test drive Lumigent. Hope this helps.
http://www.lumigent.com/

Restore SQL Server Express Databases after a Disaster Recovery

Hello,

I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.

I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.

However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.

-

Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 \.S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....

Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>sqlcmd -S.\SQLExpr
COPLEYNEWSDATABASEscripts\MASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

-

My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.

Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.

I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.

This is some kind of issue with SQL Server Express being restored by tape backup.

Any suggestions, thanks.

The error message, "Configuration block version 0 is not a valid version number" tells us that the configuration page of the master database, which is one of the first pages read when we start up, is invalid. It appears that for some reason, the master database is not being correctly restored by your DR process.

If you can tell us more about your process, what specific tools and scripts make up your backup/restore process, we can search for the root cause.

|||

Thank you for your reply.

I guess I am wondering what other people do for disaster recovery. Is there something wrong with my disaster recovery plan if I am only looking at the Simple Backup as an option?

I do a daily backup the Master, MSDB, Model, MyDatabase using simple backup and running a SQLCMD that backs up the database files Master.bak, MSDB.bak, Model.bak, and MyDatabase.bak to a different directory. This way I can have my full tape backup of my server run afterwards and back up everything (Operating System, SQLExpress, Master.bak, MSDB.bak, Model.bak, MyDatabase.bak etc.) . Then when disaster hits, I can restore my same server from last nights tape backup and then run my SQLCMD restore scripts to restore the Master.bak, MSDB.bak, Model.bak, MyDatabase.bak.

I have done many disaster recoveries from tape backup with no problems with the Operating System or any applications, except when it comes to SQL Express. I have also run my restore scripts on the server with no problem restoring.

For some reason, after I follow the steps above, if I then go to open the SQL Express management studio express, I cannot connect to the server and its databases. This does'nt seem to be a settings issue unless settings are being changed behind the scenes. It does'nt make any sense to me that I would not be able restore SQL Express and its databases by tape backup.

Is it possible to restore the SQL Server Express from tape backup and then run restore scripts generated from the same tape backup up of the Master, Model, MSDB, etc. databases?

|||My SQLCMD scripts for backup and restore are all generated from the Sql Express Management Studio Express, simply by allowing it to write the code and then I mannually paste them into *.sql files that are later run by SQLCMD. I've tested all of them and they all work. I only have problems when I do a disaster recovery senario on the same server by restoring all the OS and data back to the server. After restoring the server, then I restore the databases with the scripts and .bak files that were previously backed up. They all restore with no errors. However, when I try to connect to SQL Server Express, it can't connect.|||

The issue is that the backup app is not properly restoring master. Most likely it failed to back up the file because it was locked by SQL at the time. Backup apps using the VSS infrastructure do not have this issue.

So, you have a few options:

1) shut down SQL Express during the backup so that the database files are properly saved

2) after bare-metal restore, uninstall SQL Express (this will not remove any databases you've created), reinstall it, and then restore your master database from backup (along with all other databases). This should restore all customizations that you have made.

3) Books Online documents a procedure for rebuild the system databases (master, model, msdb) using setup.exe. I'm having trouble getting this to work. I'll post when I get this resolved.

|||

OK. Got it.

If you extract the setup files to a directory by downloading and saving sqlexpr.exe (if you haven't already) using the command sqlexpr.exe /x, you'll have all the setup files.

Then, use the command:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=SQLExpress REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> REINSTALLMODE=vomus

The setup will recreate all of the system databases without disturbing anything else. You can then restore master and the other databases from your backups and be up and running.

Can you please let me know what DR utility this is? I'd like to work with them to make it work better with SQL.

|||Thanks for your help Kevin, I am using HP one button disaster recovery with Tapeware backup software. It seems to work very well for me. The only thing it does'nt back up are log files and *.MDF files. Basically if its an open file then it might have trouble backing it up. Anyways here was my solution, which I feel is the least time consuming.

After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.

I wanted to do a disaster recovery to the same server with a tape backup and then restore the Master.bak, Model.bak, MSDB.bak, and MyDatabase.bak, back to the SQLExpress DBMS. Let me start out by saying that you cannot do that unless you have a working copy of the Master.mdf on the server you are trying to restore to. The catch here is that the tape backup software will not back up the Master.mdf because its always being used by SQLExpress.

Now, I suppose I could have dettached the Master.mdf before I backed it up, but I decided to go a different route.

I found out that there is a directory called "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data" and in it is a copy of the same contents (except I think that they are the original files created automatically by the original install, in other words, not uptodate) found in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory where the files need to reside in order to do a restore to the database, which happened to be missing after I did my tape back up restore. I simply draged and dropped a copy of the missing *mdf, *ldf files and then I was able to launch the SQLExpress Studio Management Express and connect to the SQL Server. The only thing different was that the Model database was set to (Read Only) but that didn't concern me because I was going to restore all the Databases (Master, Model, MSDB, MyDatabase) anyway.

After that was solved, I could now run any *.sql restore scripts and have them work.

Next step is to restore the Master.mdf but you can only do that in single user mode.
So, I went into SQLServer Express, single user mode with the command prompt:
C:cd "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn: Sqlservr.exe -s SQLEXPRESS -m
Press<enter>
After it does its thing you need to open an additional command prompt window because it will say that its ready for connections in single user mode, but you will not be given a new cursor prompt.

So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MASTERFULLRESTORE.sql"

This is what my MASTERFULLRESTORE.sql restore script file looks like (I created it previously using the GUI in sql express and then saved it to a script file instead of clicking on OK):
RESTORE DATABASE [Master] FROM DISK = N'E:\DATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10
GO

The SQL restore script will work this time becuase I have a working instance SQLExpress thanks to the previous drag and drop restore of the Master.mdf from the Template Data directory.

After that you need to go into Services and start the 'SQLServer Service'.
Then simply go into SQLExpress like normal and restore the Model, MSDB, from the GUI interface by right clicking on each one and selecting restore and then point to your *bak files.

When it came time to restore 'MyDatabase', I could see it in SQLExpress Management Studio Express but it was just a shell of it with no contents inside. This shell of my database is from the Master.mdf restore. Unfortunately, even though I could see it in the SQLExpress listing, when I right clicked on it, hoping for the restore option, It was grayed out. So I closed SQL Express and ran my restore.sql script from the command prompt using SQLCMD.exe and it succeeded.
So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MYDATABASEFULLRESTORE.sql"

I then went back into SQLExpress and ran some queries to test and everything seems to be working now.

Thanks to everyone for helping to point me in the right direction to solve this problem.

Restore SQL database

Hi,
There are two recovery models of SQL Server databases. The "SIMPLE" and
"FULL" and also bulked but I am not interested in that.
I need a full recovery model as I need to recover the most amount of data
and the last full backup from the night before is insufficient.
Question: In order to restore an SQL database to its last transaction, do I
restore the most recent database backup and use the existing LDF file to
bring SQL to its last transaction'
OR
Do I have to restore the last SQL database backup and then restore the last
transactional backup?
I guess if the Server disappears for whatever reason, the existing LDF would
be gone to and you would only be left with the previous nights database
backup. From this perspective I think you need both the last database and
transactional backups.
But what if database corruption occurs...can I restore the previous nights
database backups and use the existing ldf file of the corrupted database'
I am trying to figure out the best way to try and recover all SQL data in
the event of any disaster rather than just using the previous nights backup?
Your help and patience is greatly appreciated.
Thanking You
ElvisHi,
This was answered by Steen:
"How often you backup your log, depends on how many hours of work you (or
your company) can accept to loose in case of a break down.
Let me try to bring up a few examples.
1. You run a FULL backup at 2 AM and no log backup. First of all this
will not truncate your logfile hence it will continue to grow. If
something goes wrong with the database e.g. at 1.55 AM and you need to
restore your backup, you'll loose 23 hours and 55 minutes of work.
2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
truncate your logfile so it doesn't grow, but that's a bout all good the
log backup will do for you. If something happens at 1.55 AM, you'll
loose 23 hours and 25 minutes of work. You can restore your full backup
from 2 AM and then apply the log from 2.30 AM and then your database
will contain the data it had at 2.30 AM when you did the log backup.
3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
still truncate your logfile, but from a recovery point of view you're
doing a little better. Again if something happens at 1.55 AM you'll only
loose 11 hours and 55 minutes of work.
4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
to 6 PM (assuming that's your average working day). Now if something
happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
simply restore your FULL backup from 2 AM and then all the hourly log
backup up to 2 PM.
If you do FULL backup and log backups, you are "always" guaranteed that
you can restore your database up to the state it had at the time of
latest log file backup. In some case though - depending on what happens
to your database, you'll be able to backup your logfile after the
problem ocoured and then you can restore the logfile with the STOPAT
command so you can restore it up to the time just before things went
wrong meaning your data loss will be minimal.
I hope the above cleared things up a little bit. Just keep in mind that
there are no definitive answer to how often you should backup your log -
it all depends on how much data you are willing to loose in case of a
failure of some kind. In many cases there aren't really any penalty for
backing up too much - but there is for not having the right backup at
the right time...:-).
--
Regards
Steen Schlüter Persson
DBA"
"Elvis" wrote:

> Hi,
> There are two recovery models of SQL Server databases. The "SIMPLE" and
> "FULL" and also bulked but I am not interested in that.
> I need a full recovery model as I need to recover the most amount of data
> and the last full backup from the night before is insufficient.
> Question: In order to restore an SQL database to its last transaction, do
I
> restore the most recent database backup and use the existing LDF file to
> bring SQL to its last transaction'
> OR
> Do I have to restore the last SQL database backup and then restore the las
t
> transactional backup?
> I guess if the Server disappears for whatever reason, the existing LDF wou
ld
> be gone to and you would only be left with the previous nights database
> backup. From this perspective I think you need both the last database and
> transactional backups.
> But what if database corruption occurs...can I restore the previous night
s
> database backups and use the existing ldf file of the corrupted database'
> I am trying to figure out the best way to try and recover all SQL data in
> the event of any disaster rather than just using the previous nights backu
p?
> Your help and patience is greatly appreciated.
> Thanking You
> Elvis|||Note, depending on the form of the failure you may be able to back up the
tail of the log and lose virtually no work.
I highly recommend buying a book on the subject if your needs warrant.
And practice. Nothing is worse than having a real disaster and trying to
recover/restore a DB for the first time with the CEO breathing down your
neck.
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:024601F3-A046-41C3-971E-D2F985D6D394@.microsoft.com...[vbcol=seagreen]
> Hi,
> This was answered by Steen:
> "How often you backup your log, depends on how many hours of work you (or
> your company) can accept to loose in case of a break down.
> Let me try to bring up a few examples.
> 1. You run a FULL backup at 2 AM and no log backup. First of all this
> will not truncate your logfile hence it will continue to grow. If
> something goes wrong with the database e.g. at 1.55 AM and you need to
> restore your backup, you'll loose 23 hours and 55 minutes of work.
> 2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
> truncate your logfile so it doesn't grow, but that's a bout all good the
> log backup will do for you. If something happens at 1.55 AM, you'll
> loose 23 hours and 25 minutes of work. You can restore your full backup
> from 2 AM and then apply the log from 2.30 AM and then your database
> will contain the data it had at 2.30 AM when you did the log backup.
> 3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
> still truncate your logfile, but from a recovery point of view you're
> doing a little better. Again if something happens at 1.55 AM you'll only
> loose 11 hours and 55 minutes of work.
> 4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
> to 6 PM (assuming that's your average working day). Now if something
> happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
> simply restore your FULL backup from 2 AM and then all the hourly log
> backup up to 2 PM.
> If you do FULL backup and log backups, you are "always" guaranteed that
> you can restore your database up to the state it had at the time of
> latest log file backup. In some case though - depending on what happens
> to your database, you'll be able to backup your logfile after the
> problem ocoured and then you can restore the logfile with the STOPAT
> command so you can restore it up to the time just before things went
> wrong meaning your data loss will be minimal.
> I hope the above cleared things up a little bit. Just keep in mind that
> there are no definitive answer to how often you should backup your log -
> it all depends on how much data you are willing to loose in case of a
> failure of some kind. In many cases there aren't really any penalty for
> backing up too much - but there is for not having the right backup at
> the right time...:-).
> --
> Regards
> Steen Schlter Persson
> DBA"
>
> "Elvis" wrote:
>
data[vbcol=seagreen]
do I[vbcol=seagreen]
last[vbcol=seagreen]
would[vbcol=seagreen]
and[vbcol=seagreen]
nights[vbcol=seagreen]
database'[vbcol=seagreen]
in[vbcol=seagreen]
backup?[vbcol=seagreen]sql

Restore SQL database

Hi,
There are two recovery models of SQL Server databases. The "SIMPLE" and
"FULL" and also bulked but I am not interested in that.
I need a full recovery model as I need to recover the most amount of data
and the last full backup from the night before is insufficient.
Question: In order to restore an SQL database to its last transaction, do I
restore the most recent database backup and use the existing LDF file to
bring SQL to its last transaction'
OR
Do I have to restore the last SQL database backup and then restore the last
transactional backup?
I guess if the Server disappears for whatever reason, the existing LDF would
be gone to and you would only be left with the previous nights database
backup. From this perspective I think you need both the last database and
transactional backups.
But what if database corruption occurs...can I restore the previous nights
database backups and use the existing ldf file of the corrupted database'
I am trying to figure out the best way to try and recover all SQL data in
the event of any disaster rather than just using the previous nights backup?
Your help and patience is greatly appreciated.
Thanking You
ElvisHi,
This was answered by Steen:
"How often you backup your log, depends on how many hours of work you (or
your company) can accept to loose in case of a break down.
Let me try to bring up a few examples.
1. You run a FULL backup at 2 AM and no log backup. First of all this
will not truncate your logfile hence it will continue to grow. If
something goes wrong with the database e.g. at 1.55 AM and you need to
restore your backup, you'll loose 23 hours and 55 minutes of work.
2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
truncate your logfile so it doesn't grow, but that's a bout all good the
log backup will do for you. If something happens at 1.55 AM, you'll
loose 23 hours and 25 minutes of work. You can restore your full backup
from 2 AM and then apply the log from 2.30 AM and then your database
will contain the data it had at 2.30 AM when you did the log backup.
3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
still truncate your logfile, but from a recovery point of view you're
doing a little better. Again if something happens at 1.55 AM you'll only
loose 11 hours and 55 minutes of work.
4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
to 6 PM (assuming that's your average working day). Now if something
happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
simply restore your FULL backup from 2 AM and then all the hourly log
backup up to 2 PM.
If you do FULL backup and log backups, you are "always" guaranteed that
you can restore your database up to the state it had at the time of
latest log file backup. In some case though - depending on what happens
to your database, you'll be able to backup your logfile after the
problem ocoured and then you can restore the logfile with the STOPAT
command so you can restore it up to the time just before things went
wrong meaning your data loss will be minimal.
I hope the above cleared things up a little bit. Just keep in mind that
there are no definitive answer to how often you should backup your log -
it all depends on how much data you are willing to loose in case of a
failure of some kind. In many cases there aren't really any penalty for
backing up too much - but there is for not having the right backup at
the right time...:-).
--
Regards
Steen Schlüter Persson
DBA"
"Elvis" wrote:
> Hi,
> There are two recovery models of SQL Server databases. The "SIMPLE" and
> "FULL" and also bulked but I am not interested in that.
> I need a full recovery model as I need to recover the most amount of data
> and the last full backup from the night before is insufficient.
> Question: In order to restore an SQL database to its last transaction, do I
> restore the most recent database backup and use the existing LDF file to
> bring SQL to its last transaction'
> OR
> Do I have to restore the last SQL database backup and then restore the last
> transactional backup?
> I guess if the Server disappears for whatever reason, the existing LDF would
> be gone to and you would only be left with the previous nights database
> backup. From this perspective I think you need both the last database and
> transactional backups.
> But what if database corruption occurs...can I restore the previous nights
> database backups and use the existing ldf file of the corrupted database'
> I am trying to figure out the best way to try and recover all SQL data in
> the event of any disaster rather than just using the previous nights backup?
> Your help and patience is greatly appreciated.
> Thanking You
> Elvis|||Note, depending on the form of the failure you may be able to back up the
tail of the log and lose virtually no work.
I highly recommend buying a book on the subject if your needs warrant.
And practice. Nothing is worse than having a real disaster and trying to
recover/restore a DB for the first time with the CEO breathing down your
neck.
"Elvis" <Elvis@.discussions.microsoft.com> wrote in message
news:024601F3-A046-41C3-971E-D2F985D6D394@.microsoft.com...
> Hi,
> This was answered by Steen:
> "How often you backup your log, depends on how many hours of work you (or
> your company) can accept to loose in case of a break down.
> Let me try to bring up a few examples.
> 1. You run a FULL backup at 2 AM and no log backup. First of all this
> will not truncate your logfile hence it will continue to grow. If
> something goes wrong with the database e.g. at 1.55 AM and you need to
> restore your backup, you'll loose 23 hours and 55 minutes of work.
> 2. You run a FULL backup at 2 AM and a log backup at 2.30 AM. This will
> truncate your logfile so it doesn't grow, but that's a bout all good the
> log backup will do for you. If something happens at 1.55 AM, you'll
> loose 23 hours and 25 minutes of work. You can restore your full backup
> from 2 AM and then apply the log from 2.30 AM and then your database
> will contain the data it had at 2.30 AM when you did the log backup.
> 3. You run a FULL backup at 2 AM and a log backup at 2 PM. This will
> still truncate your logfile, but from a recovery point of view you're
> doing a little better. Again if something happens at 1.55 AM you'll only
> loose 11 hours and 55 minutes of work.
> 4. You run a FULL backup at 2 AM and do log backups every hour from 8 AM
> to 6 PM (assuming that's your average working day). Now if something
> happens at e.g. 2.30 PM, you'll only loose 30 minutes of work. You
> simply restore your FULL backup from 2 AM and then all the hourly log
> backup up to 2 PM.
> If you do FULL backup and log backups, you are "always" guaranteed that
> you can restore your database up to the state it had at the time of
> latest log file backup. In some case though - depending on what happens
> to your database, you'll be able to backup your logfile after the
> problem ocoured and then you can restore the logfile with the STOPAT
> command so you can restore it up to the time just before things went
> wrong meaning your data loss will be minimal.
> I hope the above cleared things up a little bit. Just keep in mind that
> there are no definitive answer to how often you should backup your log -
> it all depends on how much data you are willing to loose in case of a
> failure of some kind. In many cases there aren't really any penalty for
> backing up too much - but there is for not having the right backup at
> the right time...:-).
> --
> Regards
> Steen Schlüter Persson
> DBA"
>
> "Elvis" wrote:
> > Hi,
> >
> > There are two recovery models of SQL Server databases. The "SIMPLE" and
> > "FULL" and also bulked but I am not interested in that.
> > I need a full recovery model as I need to recover the most amount of
data
> > and the last full backup from the night before is insufficient.
> >
> > Question: In order to restore an SQL database to its last transaction,
do I
> > restore the most recent database backup and use the existing LDF file to
> > bring SQL to its last transaction'
> > OR
> > Do I have to restore the last SQL database backup and then restore the
last
> > transactional backup?
> >
> > I guess if the Server disappears for whatever reason, the existing LDF
would
> > be gone to and you would only be left with the previous nights database
> > backup. From this perspective I think you need both the last database
and
> > transactional backups.
> >
> > But what if database corruption occurs...can I restore the previous
nights
> > database backups and use the existing ldf file of the corrupted
database'
> >
> > I am trying to figure out the best way to try and recover all SQL data
in
> > the event of any disaster rather than just using the previous nights
backup?
> >
> > Your help and patience is greatly appreciated.
> >
> > Thanking You
> >
> > Elvis

Restore Problem

MSSQL 2000 SP3A

I'm trying to restore a database with the following commands.

restore database Test
from disk = 'D:\backups\9pm.bak'
WITH RECOVERY,

move 'DEV_Data' to 'c:\Program Files\Microsoft SQL
Server\MSSQL\data\Test_Data.MDF',
move 'DEV_Log' to 'c:\Program Files\Microsoft SQL
Server\MSSQL\data\Test_log.LDF'

The database doesn't exist on the restore server destination.

The size of the bak file is 681 mb.

The restore process never finishes or at least I've canceled after 30
minutes.

I do see the mdf file being created at 811 mbs.

I also see a log file for the database being created, its 17gb. I have 30
gbs of disk space when I initiate the restore.

Is this normal?Terri (terri@.cybernets.com) writes:
> MSSQL 2000 SP3A
> I'm trying to restore a database with the following commands.
> restore database Test
> from disk = 'D:\backups\9pm.bak'
> WITH RECOVERY,
> move 'DEV_Data' to 'c:\Program Files\Microsoft SQL
> Server\MSSQL\data\Test_Data.MDF',
> move 'DEV_Log' to 'c:\Program Files\Microsoft SQL
> Server\MSSQL\data\Test_log.LDF'
> The database doesn't exist on the restore server destination.
> The size of the bak file is 681 mb.
> The restore process never finishes or at least I've canceled after 30
> minutes.
> I do see the mdf file being created at 811 mbs.
> I also see a log file for the database being created, its 17gb. I have 30
> gbs of disk space when I initiate the restore.
> Is this normal?

17 GB of disk could take some time to initiate, so I would restart
the restore, and leave it running.

Once you are up, you should probably take action to shrink the
transaction log. Exactly how do to this, depends on your requirements
for up-to-the-point recovery.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

restore Master question

sql2k sp3
Im doing some disaster recovery testing. I first did a
Full backup of Master and a User db. Then I modified some
data in the User db. So then I uninstalled/ reinstalled
SQL to simulate a total box failure. I then restored the
full backup of Master. Now the thing thats weird is that
the user db came back online without restoring it. It even
has the data changes that occured after the full backups.
How can it be? I thought I would have had to restore the
User db as well in order to preserve the data?
TIA, ChrisAfter you uninstalled SQL Server, did you delete your mdf and ldf files?|||Chris,
When you were trying "to simulate a total box failure"
you forgot do delete actual databases files.
Next time, try to delete all files in MSSQL\Data folder.
HTH
Igor
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:6ba801c40170$18fd60c0$a101280a@.phx.gbl...
> sql2k sp3
> Im doing some disaster recovery testing. I first did a
> Full backup of Master and a User db. Then I modified some
> data in the User db. So then I uninstalled/ reinstalled
> SQL to simulate a total box failure. I then restored the
> full backup of Master. Now the thing thats weird is that
> the user db came back online without restoring it. It even
> has the data changes that occured after the full backups.
> How can it be? I thought I would have had to restore the
> User db as well in order to preserve the data?
> TIA, Chris|||Doh!!! Someone else just pointed that out to me also.

>--Original Message--
>After you uninstalled SQL Server, did you delete your mdf
and ldf files?
>.
>

restore Master question

sql2k sp3
Im doing some disaster recovery testing. I first did a
Full backup of Master and a User db. Then I modified some
data in the User db. So then I uninstalled/ reinstalled
SQL to simulate a total box failure. I then restored the
full backup of Master. Now the thing thats weird is that
the user db came back online without restoring it. It even
has the data changes that occured after the full backups.
How can it be? I thought I would have had to restore the
User db as well in order to preserve the data?
TIA, ChrisAfter you uninstalled SQL Server, did you delete your mdf and ldf files?|||Chris,
When you were trying "to simulate a total box failure"
you forgot do delete actual databases files.
Next time, try to delete all files in MSSQL\Data folder.
HTH
Igor
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:6ba801c40170$18fd60c0$a101280a@.phx.gbl...
> sql2k sp3
> Im doing some disaster recovery testing. I first did a
> Full backup of Master and a User db. Then I modified some
> data in the User db. So then I uninstalled/ reinstalled
> SQL to simulate a total box failure. I then restored the
> full backup of Master. Now the thing thats weird is that
> the user db came back online without restoring it. It even
> has the data changes that occured after the full backups.
> How can it be? I thought I would have had to restore the
> User db as well in order to preserve the data?
> TIA, Chris|||Doh!!! Someone else just pointed that out to me also.
>--Original Message--
>After you uninstalled SQL Server, did you delete your mdf
and ldf files?
>.
>