Yes the yallow that and support DTS packages.
Wha tis the best way to configure these to allow transferrring all data and objects? What needs to be setup first in terms of an empty database, user accounts, etc for this?
Showing posts with label restores. Show all posts
Showing posts with label restores. Show all posts
Thursday, March 8, 2012
Backups/Restores of remote hosted SQL Server to Local?
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.You could certinly use DTS to transfer all objects from one server to another, presuming that the host allows SQL Server access through their firewall.|||
Wednesday, March 7, 2012
Backups Best Practices
I have a question about using sqllitespeed.
They say it is faster doing a backup, what about restores,
I have several standby servers that I do full backups on
once in a while, so I don't have to run so many trans
logs. Does it restore fast as well?
Gary
>--Original Message--
>It looks like I have been presented with 2 options for
backing up to tape.
> 1. Use veritas SQL Agent and back up the full every
<blank> days and logs every <blank> days
> 2. Backup using native SQL agent to disk and using
veritas to backup the BAK files to tape every night.
>Anyone have an opinion on which is best and why?
>.
>Yes the restores are generally faster as well.
Andrew J. Kelly
SQL Server MVP
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:116ba01c3f5bb$cd70b130$a101280a@.phx
.gbl...
> I have a question about using sqllitespeed.
> They say it is faster doing a backup, what about restores,
> I have several standby servers that I do full backups on
> once in a while, so I don't have to run so many trans
> logs. Does it restore fast as well?
> Gary
>
> backing up to tape.
> <blank> days and logs every <blank> days
> veritas to backup the BAK files to tape every night.
They say it is faster doing a backup, what about restores,
I have several standby servers that I do full backups on
once in a while, so I don't have to run so many trans
logs. Does it restore fast as well?
Gary
>--Original Message--
>It looks like I have been presented with 2 options for
backing up to tape.
> 1. Use veritas SQL Agent and back up the full every
<blank> days and logs every <blank> days
> 2. Backup using native SQL agent to disk and using
veritas to backup the BAK files to tape every night.
>Anyone have an opinion on which is best and why?
>.
>Yes the restores are generally faster as well.
Andrew J. Kelly
SQL Server MVP
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:116ba01c3f5bb$cd70b130$a101280a@.phx
.gbl...
> I have a question about using sqllitespeed.
> They say it is faster doing a backup, what about restores,
> I have several standby servers that I do full backups on
> once in a while, so I don't have to run so many trans
> logs. Does it restore fast as well?
> Gary
>
> backing up to tape.
> <blank> days and logs every <blank> days
> veritas to backup the BAK files to tape every night.
Friday, February 24, 2012
Backup/Restore Tran Log
Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!
Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!
|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagreen]
example?
>
>.
>
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!
Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!
|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagreen]
example?
>
>.
>
Backup/Restore Tran Log
Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagree
n]
example?[vbcol=seagreen]
>
>.
>
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagree
n]
example?[vbcol=seagreen]
>
>.
>
Backup/Restore Tran Log
Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear :)
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
>> Hello Experts,
>> This is my first time that I work with the logic of
>> Transaction Log Backups/Restores seriously.
>> --First I made a full backup, then I backup my log in a
>> file:
>> BACKUP LOG [TEST_3]
>> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
>> --then I wanted to restore this log:
>> declare @.FilePosition as int
>> SELECT @.FilePosition = position
>> FROM msdb..backupset
>> WHERE database_name = 'TEST_3'
>> AND type != 'F'
>> AND backup_set_id = (SELECT MAX(backup_set_id)
>> FROM msdb..backupset
>> WHERE database_name
>> = 'TEST_3')
>> RESTORE LOG TEST_3
>> FROM DISK = N'D:\Microsoft SQL
Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH FILE = @.FilePosition
>> Even though I don't have any other connection in my DB
>> the error message I get is:
>> Server: Msg 3101, Level 16, State 1, Line 11
>> Exclusive access could not be obtained because the
>> database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 11
>> RESTORE LOG is terminating abnormally.
>> Can you please post a transaction backup/restore
example?
>> Thanks in advance!
>
>.
>
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear :)
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
>> Hello Experts,
>> This is my first time that I work with the logic of
>> Transaction Log Backups/Restores seriously.
>> --First I made a full backup, then I backup my log in a
>> file:
>> BACKUP LOG [TEST_3]
>> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
>> --then I wanted to restore this log:
>> declare @.FilePosition as int
>> SELECT @.FilePosition = position
>> FROM msdb..backupset
>> WHERE database_name = 'TEST_3'
>> AND type != 'F'
>> AND backup_set_id = (SELECT MAX(backup_set_id)
>> FROM msdb..backupset
>> WHERE database_name
>> = 'TEST_3')
>> RESTORE LOG TEST_3
>> FROM DISK = N'D:\Microsoft SQL
Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH FILE = @.FilePosition
>> Even though I don't have any other connection in my DB
>> the error message I get is:
>> Server: Msg 3101, Level 16, State 1, Line 11
>> Exclusive access could not be obtained because the
>> database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 11
>> RESTORE LOG is terminating abnormally.
>> Can you please post a transaction backup/restore
example?
>> Thanks in advance!
>
>.
>
Backup/Restore Status
Very often I have to do large backup and restores. This is either done via jobs, or the SQL Maintance tools. Neither so any status of a backup/restore. Overall progress, bytes written, etc. EM when backing up or restoring does show a GUI progress bar that isn't perfect, but does show progress to some extent.
So far I can look in the sysprocesses tables and find what spid is running the command, but that doesn't tell me much. The console command line util is for tape backups. Is there anything that can monitor the progress of backups and restores.
Thanks,
ETThe native SQL Server BACKUP and RESTORE commands have a STATUS parameter,
that when specified, returns status messages, indicating the progress of the
backup/restore operation.
Here's an example:
RESTORE DATABASE YourDB FROM DISK='\\SomePath\SomeFile.BAK' WITH STATUS = 1
See, SQL Server Books Online for more information.
Note that, even after specifying the STATUS with BACKUP or RESTORE command,
you will not see any progress messages coming up, while SQL Server is
creating the file.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Twiggy" <etwilegar@.nospamttgonline.com> wrote in message
news:D4500856-A29C-44EF-A7E8-828D7F6DDBCD@.microsoft.com...
Very often I have to do large backup and restores. This is either done via
jobs, or the SQL Maintance tools. Neither so any status of a backup/restore.
Overall progress, bytes written, etc. EM when backing up or restoring does
show a GUI progress bar that isn't perfect, but does show progress to some
extent.
So far I can look in the sysprocesses tables and find what spid is running
the command, but that doesn't tell me much. The console command line util is
for tape backups. Is there anything that can monitor the progress of
backups and restores.
Thanks,
ET|||Well I knew that EM was doing this and grabbing the results. In this case I'm not in control of the actual restore command. The command is already running, is there a way for another connection to get status on the operation. Is there a system table that information is written to?|||No Twiggy, there is not...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Twiggy" <etwilegar@.ttgonline.com> wrote in message
news:72E2D27A-36B1-4013-94D8-04A60F0A8782@.microsoft.com...
> Well I knew that EM was doing this and grabbing the results. In this case
I'm not in control of the actual restore command. The command is already
running, is there a way for another connection to get status on the
operation. Is there a system table that information is written to?
>|||I find this really weird. I wonder how the heck their internal developers debug backup/restore issues. I guess the code for backup and restoring hasn't changed a lot since 7, but still I'm sure there is a switch that would turn on logging of some sort. I'd hate to be the guy that had to write the Enterprise manager code, parsing the text output from restore with status.
Guess I'll wait for mirroring.
So far I can look in the sysprocesses tables and find what spid is running the command, but that doesn't tell me much. The console command line util is for tape backups. Is there anything that can monitor the progress of backups and restores.
Thanks,
ETThe native SQL Server BACKUP and RESTORE commands have a STATUS parameter,
that when specified, returns status messages, indicating the progress of the
backup/restore operation.
Here's an example:
RESTORE DATABASE YourDB FROM DISK='\\SomePath\SomeFile.BAK' WITH STATUS = 1
See, SQL Server Books Online for more information.
Note that, even after specifying the STATUS with BACKUP or RESTORE command,
you will not see any progress messages coming up, while SQL Server is
creating the file.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Twiggy" <etwilegar@.nospamttgonline.com> wrote in message
news:D4500856-A29C-44EF-A7E8-828D7F6DDBCD@.microsoft.com...
Very often I have to do large backup and restores. This is either done via
jobs, or the SQL Maintance tools. Neither so any status of a backup/restore.
Overall progress, bytes written, etc. EM when backing up or restoring does
show a GUI progress bar that isn't perfect, but does show progress to some
extent.
So far I can look in the sysprocesses tables and find what spid is running
the command, but that doesn't tell me much. The console command line util is
for tape backups. Is there anything that can monitor the progress of
backups and restores.
Thanks,
ET|||Well I knew that EM was doing this and grabbing the results. In this case I'm not in control of the actual restore command. The command is already running, is there a way for another connection to get status on the operation. Is there a system table that information is written to?|||No Twiggy, there is not...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Twiggy" <etwilegar@.ttgonline.com> wrote in message
news:72E2D27A-36B1-4013-94D8-04A60F0A8782@.microsoft.com...
> Well I knew that EM was doing this and grabbing the results. In this case
I'm not in control of the actual restore command. The command is already
running, is there a way for another connection to get status on the
operation. Is there a system table that information is written to?
>|||I find this really weird. I wonder how the heck their internal developers debug backup/restore issues. I guess the code for backup and restoring hasn't changed a lot since 7, but still I'm sure there is a switch that would turn on logging of some sort. I'd hate to be the guy that had to write the Enterprise manager code, parsing the text output from restore with status.
Guess I'll wait for mirroring.
Subscribe to:
Posts (Atom)