Friday, March 30, 2012

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

No comments:

Post a Comment