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 tranlog backup file

I have 2 questions, and I appreciate if somebody can help me to figure out the good way to do. Thanks a lot.

1/. If I want to restore tranlog backup file from linked server to SQL Server 2000, Is the database online or offline during that time (since I want to make sure db online for users, but not offline)

2/. Can I restore tranlog backup file from SQL Server 2000 to SQL Server 2005 database?1) Your question is not completely clear, you want to restore a tranlog from "linked server to SQL Server 2000"?
But in general to restore a tranlog you will first have to restore a previous FULL BACKUP and all following tranlog backups until the tranlog you wish to restore last. During this whole process the db is in recovery mode, this means it is not possible to connect to this database.

2) Sadly I cannot answer this question for you because I don't know :(|||To the 1st question, I mean I want to restore the last tranlog backup file from Linked Server to SQL Server2000 (that I am using now) since I did backup from Linked Server and want to restore it in the current server.|||Could you explain what you mean by Linked Server. To my knowledge that's just a link between two SQL instances and has nothing to do with log backups.|||Oh, Linked server here I just mean another server (named Enterprise Server)linked to the SQL Server 2000 that I am using now. And I use the tranlog backup file in The Enterprise server to restore in SQL Server 2000. Actually, The Enterprise Server (linked server) does the whole database backup and I want to restore the tranlog backup file in SQL Server 2000.
When you open 'Enterprise Manager' in SQL Server 2000,
click 'Security' in the tree of the sql instance, you will see 'Linked Servers' located.
Thanks for concern|||BACKUP DATABASE [master] TO DISK = N'\\dca-05\sqldumps\Daily\DCA-SIEBDB_master_db_complete.BAK' WITH INIT , NOUNLOAD , NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT

When this is run as a job I get errors. If i run this from the anlyzer it run fine.|||Agent jobs run under the Agent login privileges and not yours. QA runs under your credentials. If the agent does not have the proper access on both machines, it fails.

Either make the agent account a local admin on both machines (if same domain), a domain admin, or build a trust relationship if different domains.|||Agent jobs run under the Agent login privileges and not yours. QA runs under your credentials. If
Not to undermine the point you're making but the access to the file share goes under the credentials of SQL Server, not the credentials QA is running under.

But we're diverting from the question from jennyphb, this should be a seperate thread!

@.jennyphb: I stand with my first answer that in order to restore a tx-log you will first have to restore a full backup (with NO RECOVERY) and subsequently the tx-log. During this time the db will be inaccessible to anyone.|||Is there anyone who can help me to answer my 2nd question:

"Can I restore tranlog backup file from sql server 2000 to sql server 2005 database? (the same db but just different server since I want to upgrade to sql server 2005)."

Thank you very much.|||Thank you Lexiflex for your 1st answer.|||Sure, This can be done. You should have the same rights and credential as on the other machine.|||Is there anyone who can help me to answer my 2nd question:

"Can I restore tranlog backup file from sql server 2000 to sql server 2005 database? (the same db but just different server since I want to upgrade to sql server 2005)."

Thank you very much.

you can restore a 2k database backup to a 2k5 server but I am not sure and i doubt this would work. I would test it on a database that is not in production.|||I've done this before only with 2000 many times. I haven't done ti with a 2000 backup to restore on 2005. Simple. Just use the DTS utility to bring everything over to setup all the protocals in 2005. Then just load the data.

G That's me|||what do you mean "everything"?
and how do you load the data? detach/attach? ...
thanks|||First of all, calm down. I can see by the number of post this can be a little overwelming. It happen to me to like most people in this industry. Please see attachment screen shoots

restore MS SQL2000 DB using log files and no backups?

Hello!
I have the serious problem and can't find any ways to solve it. So, greatly
appreciate for any help!
I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
Transaction Log of this Database. Could anybody show me anyway how to
restore a database, using only transaction log file?
Thanks in forvard!
DenisIt is impossible to restore a database with only logs.
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...
> Hello!
> I have the serious problem and can't find any ways to solve it. So,
greatly
> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>|||If what you describe is true, then your database is toast.
Log backups alone are only of value if you have a full backup to restore
from first.
Jim
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...
> Hello!
> I have the serious problem and can't find any ways to solve it. So,
greatly
> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>