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 execute. Show all posts
Showing posts with label execute. 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
> > >> > **********************************
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
Restore runs longer in batch mode
I have created a Job that uses "cmcexec" to execute a SQL script. This Job
brings SQL to single_user mode then restores from a directory that is local
to the server.
1) When I run this restore in the GUI it takes 15 minutes but is still
executing
(1 + hours).
2) How can I tell that it is actually running and not hung up?
Hi,
From Query Analyzer, Could you execute the syetm procedure sp_who2 and see
the status of the backup process.
Repeatedly execute sp2 and check if the CPU time and DISK IO are increasing
for backup process. If the value is increasing
then the process is active.
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:E13E54D2-C77F-4A44-BDAB-630166185032@.microsoft.com...
> I have created a Job that uses "cmcexec" to execute a SQL script. This
Job
> brings SQL to single_user mode then restores from a directory that is
local
> to the server.
> 1) When I run this restore in the GUI it takes 15 minutes but is still
> executing
> (1 + hours).
> 2) How can I tell that it is actually running and not hung up?
brings SQL to single_user mode then restores from a directory that is local
to the server.
1) When I run this restore in the GUI it takes 15 minutes but is still
executing
(1 + hours).
2) How can I tell that it is actually running and not hung up?
Hi,
From Query Analyzer, Could you execute the syetm procedure sp_who2 and see
the status of the backup process.
Repeatedly execute sp2 and check if the CPU time and DISK IO are increasing
for backup process. If the value is increasing
then the process is active.
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:E13E54D2-C77F-4A44-BDAB-630166185032@.microsoft.com...
> I have created a Job that uses "cmcexec" to execute a SQL script. This
Job
> brings SQL to single_user mode then restores from a directory that is
local
> to the server.
> 1) When I run this restore in the GUI it takes 15 minutes but is still
> executing
> (1 + hours).
> 2) How can I tell that it is actually running and not hung up?
Wednesday, March 7, 2012
restore multiple databases (Transact SQL?)
Does anyone have a good method for quickly restored multiple SQL
Server 2000 databases'
I'm hoping to execute a Transact SQL script that will restore a bunch
of databases all in one step.
Any ideas/comments?
ThankyouAndrew wrote:
> Does anyone have a good method for quickly restored multiple SQL
> Server 2000 databases'
RESTORE DATABASE [myDB]
FROM DISK = 'd:\Databases\backup\myDb.BAK'
WITH
MOVE 'myDB_Data' TO 'F:\Databases\myDB_Data.mdf',
MOVE 'myDB_Log' TO 'F:\Databases\myDB_Log.ldf',
RECOVERY
rinse, repeat
As you can see this is very scriptable.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||In Addition to Aaron's response
You can detach a 'source' database. Copy mdf and ldf files and rename them.
Attach these databases with different names.
For more details please refer to BOL
sp_detach_db
sp_attach_db
Note: After remaining the mdf and ldf files you have to issue sp_detach_db
'new name database' in order to attach it successfuly.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:u5zATcmDFHA.1836@.tk2msftngp13.phx.gbl...
> Andrew wrote:
>
> RESTORE DATABASE [myDB]
> FROM DISK = 'd:\Databases\backup\myDb.BAK'
> WITH
> MOVE 'myDB_Data' TO 'F:\Databases\myDB_Data.mdf',
> MOVE 'myDB_Log' TO 'F:\Databases\myDB_Log.ldf',
> RECOVERY
>
> rinse, repeat
> As you can see this is very scriptable.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
Server 2000 databases'
I'm hoping to execute a Transact SQL script that will restore a bunch
of databases all in one step.
Any ideas/comments?
ThankyouAndrew wrote:
> Does anyone have a good method for quickly restored multiple SQL
> Server 2000 databases'
RESTORE DATABASE [myDB]
FROM DISK = 'd:\Databases\backup\myDb.BAK'
WITH
MOVE 'myDB_Data' TO 'F:\Databases\myDB_Data.mdf',
MOVE 'myDB_Log' TO 'F:\Databases\myDB_Log.ldf',
RECOVERY
rinse, repeat
As you can see this is very scriptable.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||In Addition to Aaron's response
You can detach a 'source' database. Copy mdf and ldf files and rename them.
Attach these databases with different names.
For more details please refer to BOL
sp_detach_db
sp_attach_db
Note: After remaining the mdf and ldf files you have to issue sp_detach_db
'new name database' in order to attach it successfuly.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:u5zATcmDFHA.1836@.tk2msftngp13.phx.gbl...
> Andrew wrote:
>
> RESTORE DATABASE [myDB]
> FROM DISK = 'd:\Databases\backup\myDb.BAK'
> WITH
> MOVE 'myDB_Data' TO 'F:\Databases\myDB_Data.mdf',
> MOVE 'myDB_Log' TO 'F:\Databases\myDB_Log.ldf',
> RECOVERY
>
> rinse, repeat
> As you can see this is very scriptable.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/
Subscribe to:
Posts (Atom)