sql backup files: time

Showing posts with label time. Show all posts

Thursday, March 22, 2012

Backup Schedules Failing

We have got backups set regularly each day. A full backup early in the
morning then a partial one every hour. These run time and the jobs for them
are in the SQL-SERVER Agent. After a few weeks the partial backups start to
fail. The full backups are fine. There is plenty of space an no errors in
the logs. When looking at the partial backups unser SQL-Server agent and
looking at the the schedule the box matked Enable is unchecked. We reset
the backup by clicking this box. It runs fine for a few wekes when it fials
again and the box has become unchecked? Has anyone got any ideas why this
might happen?
What version and service pack ? Might be related to
FIX: SQLAgent Job with Recurring Schedule is Disabled
Upon Completion of the Schedule for the Day
http://support.microsoft.com/default...;en-us;Q295378
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Scott Bailey" <scott.bailey@.migranthelpline.org> wrote in message
news:cp1jgv$2r3$1$8302bc10@.news.demon.co.uk...
> We have got backups set regularly each day. A full backup early in the
> morning then a partial one every hour. These run time and the jobs for
> them
> are in the SQL-SERVER Agent. After a few weeks the partial backups start
> to
> fail. The full backups are fine. There is plenty of space an no errors in
> the logs. When looking at the partial backups unser SQL-Server agent and
> looking at the the schedule the box matked Enable is unchecked. We reset
> the backup by clicking this box. It runs fine for a few wekes when it
> fials
> again and the box has become unchecked? Has anyone got any ideas why this
> might happen?
>

Monday, March 19, 2012

backup question

Does point-in-time restore only for full recovery model?
and if i use a full - backup then can i restore in any time or just the
backup time?
or if use transaction log , it can only to restore to the time of
transaction log, can i restore one minutes before the transaction logHi
Yes, Point in time only works when a database is in full recovery mode, the
database is backed up, and the transaction log is backed up on a regular
basis.
You can do a point in time restore anywhere between the time the backup is
completed and the last transaction log backup is completed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F0B43886-5D63-4F9B-A3E3-223CA20A831D@.microsoft.com...
> Does point-in-time restore only for full recovery model?
> and if i use a full - backup then can i restore in any time or just the
> backup time?
> or if use transaction log , it can only to restore to the time of
> transaction log, can i restore one minutes before the transaction log

backup question

Does point-in-time restore only for full recovery model?
and if i use a full - backup then can i restore in any time or just the
backup time?
or if use transaction log , it can only to restore to the time of
transaction log, can i restore one minutes before the transaction log
Hi
Yes, Point in time only works when a database is in full recovery mode, the
database is backed up, and the transaction log is backed up on a regular
basis.
You can do a point in time restore anywhere between the time the backup is
completed and the last transaction log backup is completed.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F0B43886-5D63-4F9B-A3E3-223CA20A831D@.microsoft.com...
> Does point-in-time restore only for full recovery model?
> and if i use a full - backup then can i restore in any time or just the
> backup time?
> or if use transaction log , it can only to restore to the time of
> transaction log, can i restore one minutes before the transaction log

backup question

Sunday, March 11, 2012

backup problems

Hi all,
I have a database on SQL Server 2000, and I back it up from time to time.
At one point, I moved the location of the backup file. Since then, every
time I want to do a backup, I get the old location;
I add the new one, remove the old one, do the backup (successfully), but
next time I still get the old one.
How can I fix this problem?
Thanks
AdrianUse T-SQL
BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
You will need to replace "foo" with the name of the database that you want
to back up
You will also want to replace "c:\" with a physical path on your database
server. That will be the location of your backup file.
--
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:e%23gL201SFHA.2756@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a database on SQL Server 2000, and I back it up from time to time.
> At one point, I moved the location of the backup file. Since then, every
> time I want to do a backup, I get the old location;
> I add the new one, remove the old one, do the backup (successfully), but
> next time I still get the old one.
> How can I fix this problem?
> Thanks
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Use T-SQL
> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of scheduling
an automatic backup. Or do you still suggest using T-SQL for that?
Adrian|||You can schedule the appropriate sql statement to run within the jobs node
under the management folder within Enterprise Manager.
--
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:eQhVGM2SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
>> Use T-SQL
>> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
> Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of
> scheduling an automatic backup. Or do you still suggest using T-SQL for
> that?
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> You can schedule the appropriate sql statement to run within the jobs node
> under the management folder within Enterprise Manager.
Thanks, I will look into that.
But even if I am able to do everything I need, I'm still wondering if there
is a solution to that unpleasant behavior described in my first message.
Where does Sql Server store the information about backup destinations, and
how can I change it?
Adrian|||Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
Key: BackupDirectory
Adjust the path if you are using a named instance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:%23ZohBo2SFHA.264@.tk2msftngp13.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
>> You can schedule the appropriate sql statement to run within the jobs
>> node under the management folder within Enterprise Manager.
> Thanks, I will look into that.
> But even if I am able to do everything I need, I'm still wondering if
> there is a solution to that unpleasant behavior described in my first
> message. Where does Sql Server store the information about backup
> destinations, and how can I change it?
> Adrian
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
> Key: BackupDirectory
The path I found there is neither the old one nor the new one, but it's the
"Program Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used
for making backups). I'm not sure what it is for, but it's quite clear it's
not what I need.
To be clearer, I used to back up that database to the file
"D:\db_backup\business" and then I moved it to "D:\backups\sql
server\business". I keep getting the "D:\db_backup\business" path when I try
to make a backup from Ent. Manager.
"business" is a file with no extension, not a folder.
Adrian|||Perhaps EM reads backup history tables? You could check out sp_delete_backuphistory.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:u3AbCW8SFHA.3636@.TK2MSFTNGP14.phx.gbl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
>> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
>> Key: BackupDirectory
> The path I found there is neither the old one nor the new one, but it's the "Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used for making backups). I'm not
> sure what it is for, but it's quite clear it's not what I need.
> To be clearer, I used to back up that database to the file "D:\db_backup\business" and then I
> moved it to "D:\backups\sql server\business". I keep getting the "D:\db_backup\business" path when
> I try to make a backup from Ent. Manager.
> "business" is a file with no extension, not a folder.
> Adrian
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Perhaps EM reads backup history tables? You could check out
> sp_delete_backuphistory.
Starting from your suggestion and digging further, I found the old paths in
the backupmediafamily table in the msdb database.
The field name is physical_device_name.
I wonder if it is safe to change the paths there. Or is there a stored
procedure for that?
Thanks
Adrian|||I wouldn't hack this. The question is why EM connects a database with this backupmodeiafamily? I
don't know as I don't use EM for things like these. I thought that EM suggests a backup device which
was the *last one* used for that database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:%23034Fy8SFHA.3464@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
>> Perhaps EM reads backup history tables? You could check out sp_delete_backuphistory.
> Starting from your suggestion and digging further, I found the old paths in the backupmediafamily
> table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored procedure for that?
> Thanks
> Adrian
>|||Could it be that you are backing up to a Backup Device that you have created
in EM? As Tibor, I'm not an expert on how EM does this since I'm not using
it either, but when you look at the Backup Device properties in EM, it has a
"physical location" and it might be this that are causing the issue. I think
that you'll have to drop this backup device and then re-create it to get the
right location back. You might also be able to change the path in the MSDB
database, but I don't know if it's as simple as that.
Regards
Steen
Adrian Sandor wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
>> Perhaps EM reads backup history tables? You could check out
>> sp_delete_backuphistory.
> Starting from your suggestion and digging further, I found the old
> paths in the backupmediafamily table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored
> procedure for that?
> Thanks
> Adrian|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uRt6zPITFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Could it be that you are backing up to a Backup Device that you have
> created in EM?
Yes, most likely.
I don't think I've ever used any other way to create a backup.
> As Tibor, I'm not an expert on how EM does this since I'm not using it
> either,
Is it really so unusual/uncivilized to make backups in EM?
> but when you look at the Backup Device properties in EM, it has a
> "physical location" and it might be this that are causing the issue.
I think I didn't use a "Backup Device", but just a "File Name" (I get the
choice when I add a new backup destination)
> that you'll have to drop this backup device and then re-create it to get
> the
> right location back.
Hm, creating a new file might help, I will try that.
> You might also be able to change the path in the MSDB database, but I
> don't know if it's as simple as that.
Oh.. then maybe I should keep it as a last resort.
Thanks
Adrian|||Oh, I forgot to report my progress
>> that you'll have to drop this backup device and then re-create it to get
>> the right location back.
> Hm, creating a new file might help, I will try that.
Well, that helped. Apparently, when creating a backup in a new path, the
file must not already exist.
Adrian

backup problems

Hi all,
I have a database on SQL Server 2000, and I back it up from time to time.
At one point, I moved the location of the backup file. Since then, every
time I want to do a backup, I get the old location;
I add the new one, remove the old one, do the backup (successfully), but
next time I still get the old one.
How can I fix this problem?
Thanks
AdrianUse T-SQL
BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
You will need to replace "foo" with the name of the database that you want
to back up
You will also want to replace "c:\" with a physical path on your database
server. That will be the location of your backup file.
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:e%23gL201SFHA.2756@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a database on SQL Server 2000, and I back it up from time to time.
> At one point, I moved the location of the backup file. Since then, every
> time I want to do a backup, I get the old location;
> I add the new one, remove the old one, do the backup (successfully), but
> next time I still get the old one.
> How can I fix this problem?
> Thanks
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Use T-SQL
> BACKUP DATABASE foo TO DISK = 'c:\foo.bak' WITH INIT
Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of scheduling
an automatic backup. Or do you still suggest using T-SQL for that?
Adrian|||You can schedule the appropriate sql statement to run within the jobs node
under the management folder within Enterprise Manager.
Keith
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:eQhVGM2SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OePMgJ2SFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hmm, I'd rather use Enterprise Manager; besides, I'm thinking of
> scheduling an automatic backup. Or do you still suggest using T-SQL for
> that?
> Adrian
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> You can schedule the appropriate sql statement to run within the jobs node
> under the management folder within Enterprise Manager.
Thanks, I will look into that.
But even if I am able to do everything I need, I'm still wondering if there
is a solution to that unpleasant behavior described in my first message.
Where does Sql Server store the information about backup destinations, and
how can I change it?
Adrian|||Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
Key: BackupDirectory
Adjust the path if you are using a named instance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message
news:%23ZohBo2SFHA.264@.tk2msftngp13.phx.gbl...
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eEubVV2SFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Thanks, I will look into that.
> But even if I am able to do everything I need, I'm still wondering if
> there is a solution to that unpleasant behavior described in my first
> message. Where does Sql Server store the information about backup
> destinations, and how can I change it?
> Adrian
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
> Key: BackupDirectory
The path I found there is neither the old one nor the new one, but it's the
"Program Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used
for making backups). I'm not sure what it is for, but it's quite clear it's
not what I need.
To be clearer, I used to back up that database to the file
"D:\db_backup\business" and then I moved it to "D:\backups\sql
server\business". I keep getting the "D:\db_backup\business" path when I try
to make a backup from Ent. Manager.
"business" is a file with no extension, not a folder.
Adrian|||Perhaps EM reads backup history tables? You could check out sp_delete_backup
history.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:u3AbCW8SFHA.3636@.TK2MSFTNGP14.phx.g
bl...
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uUBaG24SFHA.2424@.TK2MSFTNGP09.phx.gbl...
> The path I found there is neither the old one nor the new one, but it's th
e "Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" path (which I never used for maki
ng backups). I'm not
> sure what it is for, but it's quite clear it's not what I need.
> To be clearer, I used to back up that database to the file "D:\db_backup\b
usiness" and then I
> moved it to "D:\backups\sql server\business". I keep getting the "D:\db_ba
ckup\business" path when
> I try to make a backup from Ent. Manager.
> "business" is a file with no extension, not a folder.
> Adrian
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Perhaps EM reads backup history tables? You could check out
> sp_delete_backuphistory.
Starting from your suggestion and digging further, I found the old paths in
the backupmediafamily table in the msdb database.
The field name is physical_device_name.
I wonder if it is safe to change the paths there. Or is there a stored
procedure for that?
Thanks
Adrian|||I wouldn't hack this. The question is why EM connects a database with this b
ackupmodeiafamily? I
don't know as I don't use EM for things like these. I thought that EM sugges
ts a backup device which
was the *last one* used for that database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Sandor" <aditsu@.yahoo.com> wrote in message news:%23034Fy8SFHA.3464@.tk2msftngp13.phx
.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e6DLyZ8SFHA.560@.TK2MSFTNGP10.phx.gbl...
> Starting from your suggestion and digging further, I found the old paths i
n the backupmediafamily
> table in the msdb database.
> The field name is physical_device_name.
> I wonder if it is safe to change the paths there. Or is there a stored pro
cedure for that?
> Thanks
> Adrian
>

backup problems

Thursday, March 8, 2012

Backup Plan

I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.

My new plan is looking like this:

1) Run a complete database backup once a day

2) Run Transaction Log backups every hour

My questions are as follows:

1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?

2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.

3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.

Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!

Thanks in advance~1)A backup device is a logical name for a physical backup location that can be either on disk (a disk device) or tape (a tape device).
Rather than than having to specify the full pathname to the physical device, you can refrer to it by the Backup Device name.

2) What errors are you getting? Assuming that you have created a Job for both your Full and Transaction Log backups, you can simply set the schedule you want on the 'Schedules' tab. If you are using SQL Server 2005 you can pick from a list of existing schedules.

3) If you want to backup to a share on a network, you will need to specify the location using the full UNC path. You will also need to make sure that the MSSQLServer service is running under a Domain account rather than LocalSystem and that the Domain account has sufficient access to the share (i.e. Write permission).

I would advise you to take a look at BOL (Books On Line) to find out more about Backup/Restore.

HTH

Wednesday, March 7, 2012

backup only Users and Permissions

Hi,
I have a DataBase with several User's and specific permissions to every
User.
For test-reasons I have to restore the DataBase from time to time with
another DataBase that doesn't have these User's and permissions. After such
a restore my users's and permissions are gone offcourse.
So what I need is a way to have a backup of only my users and their
permissions, some kind of sql-script. In that way I should be able to put
them back after I restored the DataBase.
Does anybody knows how to do this?
Thansk a lot in advance,
PieterDragu
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
----
--
These two stored procedures are provided by Microsoft. Run them on source
server. It will produce the SPID/Scripts of users/logins and then run the
script on destination server.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eDod5oIzEHA.3512@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a DataBase with several User's and specific permissions to every
> User.
> For test-reasons I have to restore the DataBase from time to time with
> another DataBase that doesn't have these User's and permissions. After
such
> a restore my users's and permissions are gone offcourse.
> So what I need is a way to have a backup of only my users and their
> permissions, some kind of sql-script. In that way I should be able to put
> them back after I restored the DataBase.
> Does anybody knows how to do this?
> Thansk a lot in advance,
> Pieter
>|||Thanks,
These are two really nice and helpfull scripts.
Although: they aren't what I need: these are jsut the Login's, but I have
them alreaddy on my Server, and they aren't changed during the restore.
What I actually need is a script for the individual Permissions of each user
on every table...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqsgKLJzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Dragu
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
> ----
--
> --
> These two stored procedures are provided by Microsoft. Run them on source
> server. It will produce the SPID/Scripts of users/logins and then run the
> script on destination server.
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:eDod5oIzEHA.3512@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a DataBase with several User's and specific permissions to every
> > User.
> > For test-reasons I have to restore the DataBase from time to time with
> > another DataBase that doesn't have these User's and permissions. After
> such
> > a restore my users's and permissions are gone offcourse.
> >
> > So what I need is a way to have a backup of only my users and their
> > permissions, some kind of sql-script. In that way I should be able to
put
> > them back after I restored the DataBase.
> >
> > Does anybody knows how to do this?
> >
> > Thansk a lot in advance,
> >
> > Pieter
> >
> >
>|||Hi
Look at this stored procedure
sp_helprotect
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:uKU5kQJzEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Thanks,
> These are two really nice and helpfull scripts.
> Although: they aren't what I need: these are jsut the Login's, but I have
> them alreaddy on my Server, and they aren't changed during the restore.
> What I actually need is a script for the individual Permissions of each
user
> on every table...
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eqsgKLJzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Dragu
> > --Identify Orphan Users
> > select u.name from master..syslogins l right join
> > sysusers u on l.sid = u.sid
> > where l.sid is null and issqlrole <> 1 and isapprole <> 1
> > and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> > and u.name <> 'system_function_schema')
> ----
> --
> > --
> > These two stored procedures are provided by Microsoft. Run them on
source
> > server. It will produce the SPID/Scripts of users/logins and then run
the
> > script on destination server.
> >
> > USE master
> > GO
> > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> > DROP PROCEDURE sp_hexadecimal
> > GO
> > CREATE PROCEDURE sp_hexadecimal
> > @.binvalue varbinary(256),
> > @.hexvalue varchar(256) OUTPUT
> > AS
> > DECLARE @.charvalue varchar(256)
> > DECLARE @.i int
> > DECLARE @.length int
> > DECLARE @.hexstring char(16)
> > SELECT @.charvalue = '0x'
> > SELECT @.i = 1
> > SELECT @.length = DATALENGTH (@.binvalue)
> > SELECT @.hexstring = '0123456789ABCDEF'
> > WHILE (@.i <= @.length)
> > BEGIN
> > DECLARE @.tempint int
> > DECLARE @.firstint int
> > DECLARE @.secondint int
> > SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> > SELECT @.firstint = FLOOR(@.tempint/16)
> > SELECT @.secondint = @.tempint - (@.firstint*16)
> > SELECT @.charvalue = @.charvalue +
> > SUBSTRING(@.hexstring, @.firstint+1, 1) +
> > SUBSTRING(@.hexstring, @.secondint+1, 1)
> > SELECT @.i = @.i + 1
> > END
> > SELECT @.hexvalue = @.charvalue
> > GO
> >
> > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> > DROP PROCEDURE sp_help_revlogin
> > GO
> > CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> > DECLARE @.name sysname
> > DECLARE @.xstatus int
> > DECLARE @.binpwd varbinary (256)
> > DECLARE @.txtpwd sysname
> > DECLARE @.tmpstr varchar (256)
> > DECLARE @.SID_varbinary varbinary(85)
> > DECLARE @.SID_string varchar(256)
> >
> > IF (@.login_name IS NULL)
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name <> 'sa'
> > ELSE
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name = @.login_name
> > OPEN login_curs
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > IF (@.@.fetch_status = -1)
> > BEGIN
> > PRINT 'No login(s) found.'
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN -1
> > END
> > SET @.tmpstr = '/* sp_help_revlogin script '
> > PRINT @.tmpstr
> > SET @.tmpstr = '** Generated '
> > + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> > PRINT @.tmpstr
> > PRINT ''
> > PRINT 'DECLARE @.pwd sysname'
> > WHILE (@.@.fetch_status <> -1)
> > BEGIN
> > IF (@.@.fetch_status <> -2)
> > BEGIN
> > PRINT ''
> > SET @.tmpstr = '-- Login: ' + @.name
> > PRINT @.tmpstr
> > IF (@.xstatus & 4) = 4
> > BEGIN -- NT authenticated account/group
> > IF (@.xstatus & 1) = 1
> > BEGIN -- NT login is denied access
> > SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > ELSE BEGIN -- NT login has access
> > SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > END
> > ELSE BEGIN -- SQL Server authentication
> > IF (@.binpwd IS NOT NULL)
> > BEGIN -- Non-null password
> > EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> > IF (@.xstatus & 2048) = 2048
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> > ELSE
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> > PRINT @.tmpstr
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > ELSE BEGIN
> > -- Null password
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > IF (@.xstatus & 2048) = 2048
> > -- login upgraded from 6.5
> > SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> > ELSE
> > SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> > PRINT @.tmpstr
> > END
> > END
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > END
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN 0
> > GO
> >
> > sp_help_revlogin
> >
> > "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:eDod5oIzEHA.3512@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > I have a DataBase with several User's and specific permissions to
every
> > > User.
> > > For test-reasons I have to restore the DataBase from time to time with
> > > another DataBase that doesn't have these User's and permissions. After
> > such
> > > a restore my users's and permissions are gone offcourse.
> > >
> > > So what I need is a way to have a backup of only my users and their
> > > permissions, some kind of sql-script. In that way I should be able to
> put
> > > them back after I restored the DataBase.
> > >
> > > Does anybody knows how to do this?
> > >
> > > Thansk a lot in advance,
> > >
> > > Pieter
> > >
> > >
> >
> >
>

backup only Users and Permissions

Friday, February 24, 2012

Backup name with DateTime

I know how to backup my DB but i cant find a way to give a name with the current date time.

backup database SUSDB to disk = 'e:\backup\test_%date%.bak' with init;

Any idea how i can put the date to my file name?

thank you

You could use dynamic sq;:

declare

@.sql varchar(300)

set

@.sql='backup database SUSDB to disk = ''e:\backup\test_'+convert(varchar,getdate(),101)+'.bak'''

exec

(@.sql)

|||

You have two options modify the code in the first link for your needs or use the last two links to create one just for you. Hope this helps.

http://support.microsoft.com/kb/241397

Create custom code from these two.

http://forums.asp.net/thread/1049663.aspx

http://msdn2.microsoft.com/en-gb/library/aa259582(SQL.80).aspx

|||

Sorry i forgot to mention that i use my code inside SQL 2005 jobs,

so the backup is taken everynight

|||

So in your job step you should generate file name like was shown in one of previous steps, you can also if you wolud like to keep information how many backup you did in file name, modify backup step automatically on success to conatain current backup number. But for only modifing date, code from first answer should be helpful.

Good luck

Sunday, February 19, 2012

Backup logged time different

Hi,
I'm confused with what's being reported by SQL Server for the date of a
transacation log backup.
The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
the Archive SQL Server Logs. However, when I double-click that entry, I get
the following result:
Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
Note that the date-time for this entry is different. Any ideas why? I'm so
confused.
TIA.
Regards,The creation date in the log is the database creation date
and time, not the backup date and time.
-Sue
On Wed, 9 Aug 2006 07:50:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>I'm confused with what's being reported by SQL Server for the date of a
>transacation log backup.
>The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
>the Archive SQL Server Logs. However, when I double-click that entry, I get
>the following result:
>Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
>first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
>device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
>Note that the date-time for this entry is different. Any ideas why? I'm so
>confused.
>TIA.
>Regards,

Backup logged time different

Hi,
I'm confused with what's being reported by SQL Server for the date of a
transacation log backup.
The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen in
the Archive SQL Server Logs. However, when I double-click that entry, I get
the following result:
Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
Note that the date-time for this entry is different. Any ideas why? I'm so
confused.
TIA.
Regards,The creation date in the log is the database creation date
and time, not the backup date and time.
-Sue
On Wed, 9 Aug 2006 07:50:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:

>Hi,
>I'm confused with what's being reported by SQL Server for the date of a
>transacation log backup.
>The backup had been taken on Jun 29, 2006, 12:14:20 p.m. This can be seen i
n
>the Archive SQL Server Logs. However, when I double-click that entry, I get
>the following result:
>Log backed up: Database: ABC, creation date(time): 2006/05/31(09:21:36),
>first LSN: 15407:479:1, last LSN: 15769:73834:1, number of dump devices: 1,
>device information: (FILE=1, TYPE=DISK: {'R:\MSSQL.DATA\ABC.bak'}).
>Note that the date-time for this entry is different. Any ideas why? I'm so
>confused.
>TIA.
>Regards,

Thursday, February 16, 2012

BACKUP LOG DB WITH NO_LOG

our transaction log filled and I ran:
BACKUP LOG DB WITH NO_LOG
returns this error message:
Time out occurred while waiting for buffer latch type 3
for page (1:25176642), database ID 11, object ID
1517456680, index ID 0.
Does anyone have a clue on his (SQL Server 7)?Your disk subsystem is probably overloaded and is taking too long to do the
necessary read and writes. I would stop user access if you can until you
can fix the problem.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:96f101c3ea6a$f5ea93d0$a401280a@.phx.gbl...
> our transaction log filled and I ran:
> BACKUP LOG DB WITH NO_LOG
> returns this error message:
> Time out occurred while waiting for buffer latch type 3
> for page (1:25176642), database ID 11, object ID
> 1517456680, index ID 0.
> Does anyone have a clue on his (SQL Server 7)?|||I take it that this occured only once? you';ll see more error messages in
your SQL error logs. This error that you are running isnt so much a
'Trasnaction Log' issue, but more of a Disk I/O issue.A latch is a physical
lock that lets you acquire access on the page you need ot perform your
operation on. Due to some disk i/o access, you got this error whichmeans
that the command was unable to obtain a latch.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

backup locally and copy file vs backup remotely

Is it more or less the same amount of time to backup a database locally and
then copy the file to another server vs backing up remotely to the
destination ?
ThanksIt is more time to backup locally and copy but I would rather take the extra
time if possible for reliability.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it more or less the same amount of time to backup a database locally
> and then copy the file to another server vs backing up remotely to the
> destination ?
> Thanks|||If reliability in your backups is a concern you can use the (new in SQL
Server 2005) CHECKSUM option of the BACKUP command and the RESTORE VERIFYONL
Y
command. See BOL for more details.
And of course the only way to prove that your backup is valid is testing a
real restore on some other database server.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"jason" wrote:

> It is more time to backup locally and copy but I would rather take the ext
ra
> time if possible for reliability.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
>

backup locally and copy file vs backup remotely

Is it more or less the same amount of time to backup a database locally and
then copy the file to another server vs backing up remotely to the
destination ?
Thanks
It is more time to backup locally and copy but I would rather take the extra
time if possible for reliability.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it more or less the same amount of time to backup a database locally
> and then copy the file to another server vs backing up remotely to the
> destination ?
> Thanks
|||If reliability in your backups is a concern you can use the (new in SQL
Server 2005) CHECKSUM option of the BACKUP command and the RESTORE VERIFYONLY
command. See BOL for more details.
And of course the only way to prove that your backup is valid is testing a
real restore on some other database server.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"jason" wrote:

> It is more time to backup locally and copy but I would rather take the extra
> time if possible for reliability.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
>

backup locally and copy file vs backup remotely

Is it more or less the same amount of time to backup a database locally and
then copy the file to another server vs backing up remotely to the
destination ?
ThanksIt is more time to backup locally and copy but I would rather take the extra
time if possible for reliability.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Is it more or less the same amount of time to backup a database locally
> and then copy the file to another server vs backing up remotely to the
> destination ?
> Thanks|||If reliability in your backups is a concern you can use the (new in SQL
Server 2005) CHECKSUM option of the BACKUP command and the RESTORE VERIFYONLY
command. See BOL for more details.
And of course the only way to prove that your backup is valid is testing a
real restore on some other database server.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"jason" wrote:
> It is more time to backup locally and copy but I would rather take the extra
> time if possible for reliability.
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
> "Hassan" <hassan@.test.com> wrote in message
> news:egT4dqCRIHA.4880@.TK2MSFTNGP03.phx.gbl...
> > Is it more or less the same amount of time to backup a database locally
> > and then copy the file to another server vs backing up remotely to the
> > destination ?
> >
> > Thanks
>

Monday, February 13, 2012

Backup job Fails every time:

Dear Friends!
I have a scheduled job on my production server which always fails. When I
see the job history step details I can see 100 % backedup and along with
that I receive error
[SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'SLNKMIL',
file 'SLNKMIL_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The
step failed.
Pl, guide me. Job fail isn't affordable on my production database.
I sincerly thank you!
S.Lakshminarayanan.
Message posted via http://www.sqlmonster.com
Can you try issuing a BACKUP DATABASE statement via T-SQL through Query
Analyzer? It would be helpful to see the errors (if there are any). Before
running the backup make sure that you have results set to text.
The syntax is easy enough:
BACKUP DATABASE SLNKMIL TO DISK = 'x:\SLNKMIL.bak' WITH INIT
Keith
"lakshminarayan iyer via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:67ccca76be724649be8629b2da37796a@.SQLMonster.c om...
> Dear Friends!
> I have a scheduled job on my production server which always fails. When I
> see the job history step details I can see 100 % backedup and along with
> that I receive error
> [SQLSTATE 01000] (Message 3211) Processed 1 pages for database
'SLNKMIL',
> file 'SLNKMIL_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP
> DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The
> step failed.
> Pl, guide me. Job fail isn't affordable on my production database.
> I sincerly thank you!
> S.Lakshminarayanan.
> --
> Message posted via http://www.sqlmonster.com
|||Is the database in Simple recovery mode? If so you can not do a log backup.
Andrew J. Kelly SQL MVP
"lakshminarayan iyer via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:67ccca76be724649be8629b2da37796a@.SQLMonster.c om...
> Dear Friends!
> I have a scheduled job on my production server which always fails. When I
> see the job history step details I can see 100 % backedup and along with
> that I receive error
> [SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'SLNKMIL',
> file 'SLNKMIL_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP
> DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The
> step failed.
> Pl, guide me. Job fail isn't affordable on my production database.
> I sincerly thank you!
> S.Lakshminarayanan.
> --
> Message posted via http://www.sqlmonster.com

Thursday, March 22, 2012

Backup Schedules Failing

Monday, March 19, 2012

backup question

backup question

backup question

Sunday, March 11, 2012

backup problems

backup problems

backup problems

Thursday, March 8, 2012

Backup Plan

Wednesday, March 7, 2012

backup only Users and Permissions

backup only Users and Permissions

backup only Users and Permissions

Friday, February 24, 2012

Backup name with DateTime

Sunday, February 19, 2012

Backup logged time different

Backup logged time different

Thursday, February 16, 2012

BACKUP LOG DB WITH NO_LOG

backup locally and copy file vs backup remotely

backup locally and copy file vs backup remotely

backup locally and copy file vs backup remotely

Monday, February 13, 2012

Backup job Fails every time:

sql backup files

Blog Archive

About Me