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.
>
Showing posts with label mydb1. Show all posts
Showing posts with label mydb1. Show all posts
Tuesday, March 20, 2012
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.
>
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 messagenews: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 messagenews: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.
>
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
> > >> > **********************************
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
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
> > >> > **********************************
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)