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 SQL2000 database using vbscript

Hello,
I am trying to use a VBscript thru installshield to restore a sql2000 database. the script (below) worked fine for default instance of database. but with a specific named instance I get error 3201. any ideas? thx - Prasanna
-----------
code:

dim sql
dim sqlRest

on error resume next
Set sql = CreateObject("SQLDMO.SQLServer")
sql.LoginSecure = True
sql.Connect ".\TEST2000"

Set sqlRest = CreateObject("SQLDMO.Restore")
sqlRest.Files = "c:\testdb.bak"
sqlRest.Database = "TESTDB"
sqlRest.Action = SQLDMORestore_Database
sqlRest.ReplaceDatabase = True
sqlRest.SQLRestore sql
msgbox err.numberFound a solution:

The backup was created using default instance of SQL2000 and once I used a backup created using the 'TEST2000' instance the script worked. I think you can use the RelocateFiles property of the SQLDMO Restore object to set the location of the data and log files but due to time constraints I could not try that solution.

-Prasanna

Restore script does not seem to work

Hello,
I am trying to restore a 1GB database by using the script below:
alter database [auto] set single_user
go
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
with norecovery,
move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
stats = percentage
go
I added the stats line because it ran for over 2 hours.
I let this run for about half an hour, but it never showed any statistics.
The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
going up to 2 % every few seconds.
The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
Standard, and there is 20 GB of space on F.
I have restored the same DB by using the Enterprise Manager and I think it
took 5 minutes.
I want to write a script to do this to simplify the process, since this is a
backup server and I am trying to make the job easier for whoever end up with
the job of restoring.
Any ideas why this is happening? I have triple checked the folder structures
and the names.
Any help would be much appreciated.
RagnarThe problem is the stats thing ie
restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = 5
> go
THis means that SQL should show stats after each 5% has completed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to restore a 1GB database by using the script below:
> alter database [auto] set single_user
> go
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
> with norecovery,
> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
> stats = percentage
> go
> I added the stats line because it ran for over 2 hours.
> I let this run for about half an hour, but it never showed any statistics.
> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
> going up to 2 % every few seconds.
> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
> Standard, and there is 20 GB of space on F.
> I have restored the same DB by using the Enterprise Manager and I think it
> took 5 minutes.
> I want to write a script to do this to simplify the process, since this is
> a backup server and I am trying to make the job easier for whoever end up
> with the job of restoring.
> Any ideas why this is happening? I have triple checked the folder
> structures and the names.
> Any help would be much appreciated.
> Ragnar
>|||Thank you Wayne,
It turns out that there was something wrong with the server, our IS guys
eventually had to recycle it.
Once I fixed the STATS statement it ran in 202 seconds.
But I was puzzled about the stats display, nothing showed up until the job
finished!
I had used this before, I just copied some sample code, and as far as I can
remember it would print a line whenever a certain percentage had been
processed.
Ragnar
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23ZzzIlqdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> The problem is the stats thing ie
> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
>> with norecovery,
>> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
>> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
>> stats = 5
>> go
> THis means that SQL should show stats after each 5% has completed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:ufjA2LqdFHA.3012@.tk2msftngp13.phx.gbl...
>> Hello,
>> I am trying to restore a 1GB database by using the script below:
>> alter database [auto] set single_user
>> go
>> restore database [auto] from disk = 'F:\Temp\Restore\auto_db'
>> with norecovery,
>> move 'auto_dat' to 'F:\SQLDATA\MSSQL\Data\auto.mdf',
>> move 'auto_log' to 'F:\SQLDATA\MSSQL\Data\auto.ldf',
>> stats = percentage
>> go
>> I added the stats line because it ran for over 2 hours.
>> I let this run for about half an hour, but it never showed any
>> statistics.
>> The performance monitor shows 345 MB of memory used and mostly 0 % CPU,
>> going up to 2 % every few seconds.
>> The machine is a Pentiium 4 2.8 GHz with 512 MB RAM running 2003 Server
>> Standard, and there is 20 GB of space on F.
>> I have restored the same DB by using the Enterprise Manager and I think
>> it took 5 minutes.
>> I want to write a script to do this to simplify the process, since this
>> is a backup server and I am trying to make the job easier for whoever end
>> up with the job of restoring.
>> Any ideas why this is happening? I have triple checked the folder
>> structures and the names.
>> Any help would be much appreciated.
>> Ragnar
>

Restore script

Hi,
I have the script below and it is working find if I tried to restore the
backup on one database only i.e. MYDB1 but if I execute the script below and
change MYDB1 to MYDB2 I got the error below. It seems that it is still
trying to restore to MYDB1. Any idea why?
Msg 3101, Level 16, State 2, Server SERVER1, Line 35
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server SERVER1, Line 35
RESTORE DATABASE is terminating abnormally.
**********************************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB1
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
REPLACE
**********************************Hi,
Probably you can switch the database to single user before restore
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the script below and it is working find if I tried to restore the
> backup on one database only i.e. MYDB1 but if I execute the script below
> and
> change MYDB1 to MYDB2 I got the error below. It seems that it is still
> trying to restore to MYDB1. Any idea why?
> Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> RESTORE DATABASE is terminating abnormally.
>
> **********************************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB1
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> REPLACE
> **********************************
>|||Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems it
is trying to restore to MYDB1.
Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Probably you can switch the database to single user before restore
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE Command
> GO
> ALTER DATABASE <dbname> SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||Could you post the exact Restore statement you are issuing. The one you
posted earlier is to restore in MYDB1 database.
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems
> it
> is trying to restore to MYDB1.
> Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
>|||**********************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB2
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
REPLACE
**********************
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Could you post the exact Restore statement you are issuing. The one you
> posted earlier is to restore in MYDB1 database.
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
seems[vbcol=seagreen]
still[vbcol=seagreen]
use.[vbcol=seagreen]
>|||Hi Hari,
I already found out the problem I did not change restore database to MYDB2
it is still MYDB1 in my original script.
Thanks.
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:%235C6Z%23nZFHA.3840@.tk2msftngp13.phx.gbl...
> **********************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB2
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
> REPLACE
> **********************
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> seems
restore[vbcol=seagreen]
> still
> use.
>

Restore script

Hi,
I have the script below and it is working find if I tried to restore the
backup on one database only i.e. MYDB1 but if I execute the script below and
change MYDB1 to MYDB2 I got the error below. It seems that it is still
trying to restore to MYDB1. Any idea why?
Msg 3101, Level 16, State 2, Server SERVER1, Line 35
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server SERVER1, Line 35
RESTORE DATABASE is terminating abnormally.
**********************************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB1
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
REPLACE
**********************************Hi,
Probably you can switch the database to single user before restore
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the script below and it is working find if I tried to restore the
> backup on one database only i.e. MYDB1 but if I execute the script below
> and
> change MYDB1 to MYDB2 I got the error below. It seems that it is still
> trying to restore to MYDB1. Any idea why?
> Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> RESTORE DATABASE is terminating abnormally.
>
> **********************************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB1
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> REPLACE
> **********************************
>|||Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems it
is trying to restore to MYDB1.
Thanks.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Probably you can switch the database to single user before restore
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE Command
> GO
> ALTER DATABASE <dbname> SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||Could you post the exact Restore statement you are issuing. The one you
posted earlier is to restore in MYDB1 database.
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems
> it
> is trying to restore to MYDB1.
> Thanks.
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
>|||**********************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB2
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
REPLACE
**********************
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Could you post the exact Restore statement you are issuing. The one you
> posted earlier is to restore in MYDB1 database.
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
seems
still
use.
>|||Hi Hari,
I already found out the problem I did not change restore database to MYDB2
it is still MYDB1 in my original script.
Thanks.
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:%235C6Z%23nZFHA.3840@.tk2msftngp13.phx.gbl...
> **********************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB2
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
> REPLACE
> **********************
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> seems
restore
> still
> use.
>

Restore script

Hi,
I have the script below and it is working find if I tried to restore the
backup on one database only i.e. MYDB1 but if I execute the script below and
change MYDB1 to MYDB2 I got the error below. It seems that it is still
trying to restore to MYDB1. Any idea why?
Msg 3101, Level 16, State 2, Server SERVER1, Line 35
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server SERVER1, Line 35
RESTORE DATABASE is terminating abnormally.
**********************************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB1
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
REPLACE
**********************************
Hi,
Probably you can switch the database to single user before restore
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the script below and it is working find if I tried to restore the
> backup on one database only i.e. MYDB1 but if I execute the script below
> and
> change MYDB1 to MYDB2 I got the error below. It seems that it is still
> trying to restore to MYDB1. Any idea why?
> Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> RESTORE DATABASE is terminating abnormally.
>
> **********************************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB1
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> REPLACE
> **********************************
>
|||Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems it
is trying to restore to MYDB1.
Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Probably you can switch the database to single user before restore
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE Command
> GO
> ALTER DATABASE <dbname> SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>
|||Could you post the exact Restore statement you are issuing. The one you
posted earlier is to restore in MYDB1 database.
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems
> it
> is trying to restore to MYDB1.
> Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
>
|||**********************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB2
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
REPLACE
**********************
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Could you post the exact Restore statement you are issuing. The one you
> posted earlier is to restore in MYDB1 database.
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
seems[vbcol=seagreen]
still[vbcol=seagreen]
use.
>
|||Hi Hari,
I already found out the problem I did not change restore database to MYDB2
it is still MYDB1 in my original script.
Thanks.
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:%235C6Z%23nZFHA.3840@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> **********************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB2
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
> REPLACE
> **********************
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> seems
restore
> still
> use.
>

Restore script

Hi,
I have the script below and it is working find if I tried to restore the
backup on one database only i.e. MYDB1 but if I execute the script below and
change MYDB1 to MYDB2 I got the error below. It seems that it is still
trying to restore to MYDB1. Any idea why?
Msg 3101, Level 16, State 2, Server SERVER1, Line 35
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Server SERVER1, Line 35
RESTORE DATABASE is terminating abnormally.
**********************************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB1
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
REPLACE
**********************************Hi,
Probably you can switch the database to single user before restore
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the script below and it is working find if I tried to restore the
> backup on one database only i.e. MYDB1 but if I execute the script below
> and
> change MYDB1 to MYDB2 I got the error below. It seems that it is still
> trying to restore to MYDB1. Any idea why?
> Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> Exclusive access could not be obtained because the database is in use.
> Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> RESTORE DATABASE is terminating abnormally.
>
> **********************************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB1
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> REPLACE
> **********************************
>|||Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems it
is trying to restore to MYDB1.
Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Probably you can switch the database to single user before restore
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE Command
> GO
> ALTER DATABASE <dbname> SET MULTI_USER
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have the script below and it is working find if I tried to restore the
> > backup on one database only i.e. MYDB1 but if I execute the script below
> > and
> > change MYDB1 to MYDB2 I got the error below. It seems that it is still
> > trying to restore to MYDB1. Any idea why?
> >
> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> > Exclusive access could not be obtained because the database is in use.
> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> > RESTORE DATABASE is terminating abnormally.
> >
> >
> > **********************************
> > USE TEMPDB
> >
> > DECLARE @.DNAME AS VARCHAR(20)
> > DECLARE @.LNAME AS VARCHAR(20)
> >
> > CREATE TABLE #LIST
> > (
> > LogicalName sysname NOT NULL,
> > PhysicalName varchar(255) NOT NULL,
> > Type char(1) NOT NULL,
> > FileGroupName sysname NULL,
> > Size bigint NOT NULL,
> > MaxSize bigint NOT NULL
> > )
> > INSERT INTO #LIST
> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> >
> > SELECT @.DNAME =(
> > SELECT LogicalName
> > FROM #LIST
> > WHERE type = 'D')
> > SELECT @.LNAME =(
> > SELECT LogicalName
> > FROM #LIST
> > WHERE type = 'L')
> >
> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> >
> > USE MASTER
> >
> > SELECT @.LNAME
> > SELECT @.DNAME
> >
> > RESTORE DATABASE MYDB1
> > FROM DISK = 'E:\backup.bak'
> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> > REPLACE
> > **********************************
> >
> >
>|||Could you post the exact Restore statement you are issuing. The one you
posted earlier is to restore in MYDB1 database.
Thanks
Hari
SQL Server MVP
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Nobody is connected to MYDB2 but on MYDB1 one user is connected. It seems
> it
> is trying to restore to MYDB1.
> Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Probably you can switch the database to single user before restore
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> GO
>> RESTORE DATABASE Command
>> GO
>> ALTER DATABASE <dbname> SET MULTI_USER
>> Thanks
>> Hari
>> SQL Server MVP
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
>> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have the script below and it is working find if I tried to restore
>> > the
>> > backup on one database only i.e. MYDB1 but if I execute the script
>> > below
>> > and
>> > change MYDB1 to MYDB2 I got the error below. It seems that it is still
>> > trying to restore to MYDB1. Any idea why?
>> >
>> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
>> > Exclusive access could not be obtained because the database is in use.
>> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> >
>> > **********************************
>> > USE TEMPDB
>> >
>> > DECLARE @.DNAME AS VARCHAR(20)
>> > DECLARE @.LNAME AS VARCHAR(20)
>> >
>> > CREATE TABLE #LIST
>> > (
>> > LogicalName sysname NOT NULL,
>> > PhysicalName varchar(255) NOT NULL,
>> > Type char(1) NOT NULL,
>> > FileGroupName sysname NULL,
>> > Size bigint NOT NULL,
>> > MaxSize bigint NOT NULL
>> > )
>> > INSERT INTO #LIST
>> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
>> >
>> > SELECT @.DNAME =(
>> > SELECT LogicalName
>> > FROM #LIST
>> > WHERE type = 'D')
>> > SELECT @.LNAME =(
>> > SELECT LogicalName
>> > FROM #LIST
>> > WHERE type = 'L')
>> >
>> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
>> >
>> > USE MASTER
>> >
>> > SELECT @.LNAME
>> > SELECT @.DNAME
>> >
>> > RESTORE DATABASE MYDB1
>> > FROM DISK = 'E:\backup.bak'
>> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
>> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
>> > REPLACE
>> > **********************************
>> >
>> >
>>
>|||**********************
USE TEMPDB
DECLARE @.DNAME AS VARCHAR(20)
DECLARE @.LNAME AS VARCHAR(20)
CREATE TABLE #LIST
(
LogicalName sysname NOT NULL,
PhysicalName varchar(255) NOT NULL,
Type char(1) NOT NULL,
FileGroupName sysname NULL,
Size bigint NOT NULL,
MaxSize bigint NOT NULL
)
INSERT INTO #LIST
EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
SELECT @.DNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'D')
SELECT @.LNAME =(
SELECT LogicalName
FROM #LIST
WHERE type = 'L')
RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
USE MASTER
SELECT @.LNAME
SELECT @.DNAME
RESTORE DATABASE MYDB2
FROM DISK = 'E:\backup.bak'
WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
REPLACE
**********************
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Could you post the exact Restore statement you are issuing. The one you
> posted earlier is to restore in MYDB1 database.
> Thanks
> Hari
> SQL Server MVP
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > Nobody is connected to MYDB2 but on MYDB1 one user is connected. It
seems
> > it
> > is trying to restore to MYDB1.
> >
> > Thanks.
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >>
> >> Probably you can switch the database to single user before restore
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> GO
> >>
> >> RESTORE DATABASE Command
> >>
> >> GO
> >> ALTER DATABASE <dbname> SET MULTI_USER
> >>
> >> Thanks
> >> Hari
> >> SQL Server MVP
> >>
> >> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> >> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> >
> >> > I have the script below and it is working find if I tried to restore
> >> > the
> >> > backup on one database only i.e. MYDB1 but if I execute the script
> >> > below
> >> > and
> >> > change MYDB1 to MYDB2 I got the error below. It seems that it is
still
> >> > trying to restore to MYDB1. Any idea why?
> >> >
> >> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> >> > Exclusive access could not be obtained because the database is in
use.
> >> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> >
> >> > **********************************
> >> > USE TEMPDB
> >> >
> >> > DECLARE @.DNAME AS VARCHAR(20)
> >> > DECLARE @.LNAME AS VARCHAR(20)
> >> >
> >> > CREATE TABLE #LIST
> >> > (
> >> > LogicalName sysname NOT NULL,
> >> > PhysicalName varchar(255) NOT NULL,
> >> > Type char(1) NOT NULL,
> >> > FileGroupName sysname NULL,
> >> > Size bigint NOT NULL,
> >> > MaxSize bigint NOT NULL
> >> > )
> >> > INSERT INTO #LIST
> >> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> >> >
> >> > SELECT @.DNAME =(
> >> > SELECT LogicalName
> >> > FROM #LIST
> >> > WHERE type = 'D')
> >> > SELECT @.LNAME =(
> >> > SELECT LogicalName
> >> > FROM #LIST
> >> > WHERE type = 'L')
> >> >
> >> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> >> >
> >> > USE MASTER
> >> >
> >> > SELECT @.LNAME
> >> > SELECT @.DNAME
> >> >
> >> > RESTORE DATABASE MYDB1
> >> > FROM DISK = 'E:\backup.bak'
> >> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> >> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> >> > REPLACE
> >> > **********************************
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hi Hari,
I already found out the problem I did not change restore database to MYDB2
it is still MYDB1 in my original script.
Thanks.
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:%235C6Z%23nZFHA.3840@.tk2msftngp13.phx.gbl...
> **********************
> USE TEMPDB
> DECLARE @.DNAME AS VARCHAR(20)
> DECLARE @.LNAME AS VARCHAR(20)
> CREATE TABLE #LIST
> (
> LogicalName sysname NOT NULL,
> PhysicalName varchar(255) NOT NULL,
> Type char(1) NOT NULL,
> FileGroupName sysname NULL,
> Size bigint NOT NULL,
> MaxSize bigint NOT NULL
> )
> INSERT INTO #LIST
> EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> SELECT @.DNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'D')
> SELECT @.LNAME =(
> SELECT LogicalName
> FROM #LIST
> WHERE type = 'L')
> RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> USE MASTER
> SELECT @.LNAME
> SELECT @.DNAME
> RESTORE DATABASE MYDB2
> FROM DISK = 'E:\backup.bak'
> WITH MOVE @.DNAME TO 'E:\MYDB2_DATA.MDF',
> MOVE @.LNAME TO 'E:\MYDB2_LOG.LDF',
> REPLACE
> **********************
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:uqGamdhZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> > Could you post the exact Restore statement you are issuing. The one you
> > posted earlier is to restore in MYDB1 database.
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> > news:OGLau5dZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> > > Nobody is connected to MYDB2 but on MYDB1 one user is connected. It
> seems
> > > it
> > > is trying to restore to MYDB1.
> > >
> > > Thanks.
> > >
> > >
> > > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:O8UDuodZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> > >> Hi,
> > >>
> > >> Probably you can switch the database to single user before restore
> > >>
> > >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> > >> GO
> > >>
> > >> RESTORE DATABASE Command
> > >>
> > >> GO
> > >> ALTER DATABASE <dbname> SET MULTI_USER
> > >>
> > >> Thanks
> > >> Hari
> > >> SQL Server MVP
> > >>
> > >> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> > >> news:exM9cXdZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> >
> > >> > I have the script below and it is working find if I tried to
restore
> > >> > the
> > >> > backup on one database only i.e. MYDB1 but if I execute the script
> > >> > below
> > >> > and
> > >> > change MYDB1 to MYDB2 I got the error below. It seems that it is
> still
> > >> > trying to restore to MYDB1. Any idea why?
> > >> >
> > >> > Msg 3101, Level 16, State 2, Server SERVER1, Line 35
> > >> > Exclusive access could not be obtained because the database is in
> use.
> > >> > Msg 3013, Level 16, State 1, Server SERVER1, Line 35
> > >> > RESTORE DATABASE is terminating abnormally.
> > >> >
> > >> >
> > >> > **********************************
> > >> > USE TEMPDB
> > >> >
> > >> > DECLARE @.DNAME AS VARCHAR(20)
> > >> > DECLARE @.LNAME AS VARCHAR(20)
> > >> >
> > >> > CREATE TABLE #LIST
> > >> > (
> > >> > LogicalName sysname NOT NULL,
> > >> > PhysicalName varchar(255) NOT NULL,
> > >> > Type char(1) NOT NULL,
> > >> > FileGroupName sysname NULL,
> > >> > Size bigint NOT NULL,
> > >> > MaxSize bigint NOT NULL
> > >> > )
> > >> > INSERT INTO #LIST
> > >> > EXEC('RESTORE FILELISTONLY FROM DISK=''E:\backup.bak''')
> > >> >
> > >> > SELECT @.DNAME =(
> > >> > SELECT LogicalName
> > >> > FROM #LIST
> > >> > WHERE type = 'D')
> > >> > SELECT @.LNAME =(
> > >> > SELECT LogicalName
> > >> > FROM #LIST
> > >> > WHERE type = 'L')
> > >> >
> > >> > RESTORE FILELISTONLY FROM DISK='E:\backup.bak'
> > >> >
> > >> > USE MASTER
> > >> >
> > >> > SELECT @.LNAME
> > >> > SELECT @.DNAME
> > >> >
> > >> > RESTORE DATABASE MYDB1
> > >> > FROM DISK = 'E:\backup.bak'
> > >> > WITH MOVE @.DNAME TO 'E:\MYDB1_DATA.MDF',
> > >> > MOVE @.LNAME TO 'E:\MYDB1_LOG.LDF',
> > >> > REPLACE
> > >> > **********************************
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

OK

looks like that database is corrupted. You should restore from an earlier/different backup.

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

OK

looks like that database is corrupted. You should restore from an earlier/different backup.