sql backup files: users

Showing posts with label users. Show all posts

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

Backup Only Tool

Hi there,
We have several users that require a tool to make ad hoc backups. Fo
obvious reasons we don't want to give them any other functionality
such as restoring a database. Is there a tool, 3rd party or otherwise,
that would suitable for this?
Any help much appreciated.
Joeit would be very easy to slap together a simple script using dmo/smo that
your users could plug in a server and database name and location and have it
perform a backup.
TheSQLGuru
President
Indicium Resources, Inc.
"joe" <joelocker@.gmail.com> wrote in message
news:1186999278.638852.239050@.57g2000hsv.googlegroups.com...
> Hi there,
> We have several users that require a tool to make ad hoc backups. Fo
> obvious reasons we don't want to give them any other functionality
> such as restoring a database. Is there a tool, 3rd party or otherwise,
> that would suitable for this?
> Any help much appreciated.
> Joe
>|||I would be extremely careful of allowing this unless you have bagloads
of disk space available. it is impossible to manage the number of
backups that could be created which could lead to rapid disk space
usage. if you haven't given much thoguht to your server configuration
and have backups on the same disk space as your databases, you could
very quickly run into the situation where your databases will not have
any available space left to write to.
You may want to instead create a maintenance plan that kept only a
certain number of backups (or certain number of days) and then create
the script to run the maintenance plan to put some more controls in
place on disk space usage.
I will not list specific examples, but you should really consider the
disk space usage and also consider why the users need to backup
databases themselves.|||I wouldn't allow them to do backups on the server - only on their local
machine or a file server that it is ok if they fill up. :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Joe Mama" <Les.Barkhouse@.gmail.com> wrote in message
news:1187100581.123220.126500@.19g2000hsx.googlegroups.com...
>I would be extremely careful of allowing this unless you have bagloads
> of disk space available. it is impossible to manage the number of
> backups that could be created which could lead to rapid disk space
> usage. if you haven't given much thoguht to your server configuration
> and have backups on the same disk space as your databases, you could
> very quickly run into the situation where your databases will not have
> any available space left to write to.
> You may want to instead create a maintenance plan that kept only a
> certain number of backups (or certain number of days) and then create
> the script to run the maintenance plan to put some more controls in
> place on disk space usage.
> I will not list specific examples, but you should really consider the
> disk space usage and also consider why the users need to backup
> databases themselves.
>

Backup Only Tool

Hi there,
We have several users that require a tool to make ad hoc backups. Fo
obvious reasons we don't want to give them any other functionality
such as restoring a database. Is there a tool, 3rd party or otherwise,
that would suitable for this?
Any help much appreciated.
Joeit would be very easy to slap together a simple script using dmo/smo that
your users could plug in a server and database name and location and have it
perform a backup.
--
TheSQLGuru
President
Indicium Resources, Inc.
"joe" <joelocker@.gmail.com> wrote in message
news:1186999278.638852.239050@.57g2000hsv.googlegroups.com...
> Hi there,
> We have several users that require a tool to make ad hoc backups. Fo
> obvious reasons we don't want to give them any other functionality
> such as restoring a database. Is there a tool, 3rd party or otherwise,
> that would suitable for this?
> Any help much appreciated.
> Joe
>|||I would be extremely careful of allowing this unless you have bagloads
of disk space available. it is impossible to manage the number of
backups that could be created which could lead to rapid disk space
usage. if you haven't given much thoguht to your server configuration
and have backups on the same disk space as your databases, you could
very quickly run into the situation where your databases will not have
any available space left to write to.
You may want to instead create a maintenance plan that kept only a
certain number of backups (or certain number of days) and then create
the script to run the maintenance plan to put some more controls in
place on disk space usage.
I will not list specific examples, but you should really consider the
disk space usage and also consider why the users need to backup
databases themselves.|||I wouldn't allow them to do backups on the server - only on their local
machine or a file server that it is ok if they fill up. :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Joe Mama" <Les.Barkhouse@.gmail.com> wrote in message
news:1187100581.123220.126500@.19g2000hsx.googlegroups.com...
>I would be extremely careful of allowing this unless you have bagloads
> of disk space available. it is impossible to manage the number of
> backups that could be created which could lead to rapid disk space
> usage. if you haven't given much thoguht to your server configuration
> and have backups on the same disk space as your databases, you could
> very quickly run into the situation where your databases will not have
> any available space left to write to.
> You may want to instead create a maintenance plan that kept only a
> certain number of backups (or certain number of days) and then create
> the script to run the maintenance plan to put some more controls in
> place on disk space usage.
> I will not list specific examples, but you should really consider the
> disk space usage and also consider why the users need to backup
> databases themselves.
>

Saturday, February 25, 2012

Backup of Log files

Dear All,
My nightly backups for log files are failing as there are
users still attached to the database being backup up.
What is the best stratergy for dealing with this ?
Do I
1) Make the DB single user ?
2) Have a script to kill all connections
3) Modify my backup plans
4) Some other
Any help here would be greatly appriciated.
PeterHi Peter,
Which version of SQL Server do you use? SQL Server 2000 and 7 (and probably
some earlier versions as well, but I don't have experience with them) allow
database and log backups when there are users connected to the database.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:05ae01c35fe8$90bfb4c0$a101280a@.phx.gbl...
> Dear All,
> My nightly backups for log files are failing as there are
> users still attached to the database being backup up.
> What is the best stratergy for dealing with this ?
> Do I
> 1) Make the DB single user ?
> 2) Have a script to kill all connections
> 3) Modify my backup plans
> 4) Some other
> Any help here would be greatly appriciated.
> Peter|||Hello,
Thanks for both of your replies.
I am using SQL 2000. However the backups I am doing are
part of the database maintence plan backups.
I do know that you can back up log files when people are
connected (I do this every hour with our production
database) but once a week I perform a backup of all our
databases with all sort of integrity checks, re builds of
indexes and so on. Its these back ups the log file back
ups are failing on.
Thanks
Peter
>--Original Message--
>Dear All,
>My nightly backups for log files are failing as there are
>users still attached to the database being backup up.
>What is the best stratergy for dealing with this ?
>Do I
>1) Make the DB single user ?
>2) Have a script to kill all connections
>3) Modify my backup plans
>4) Some other
>Any help here would be greatly appriciated.
>Peter
>.
>|||How do you know that they are failing because users are attached? Where are
you getting these error messages from ?
What is the exact message from the Maintenance plan history ?
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:060601c35fee$5e973990$a101280a@.phx.gbl...
> Hello,
> Thanks for both of your replies.
> I am using SQL 2000. However the backups I am doing are
> part of the database maintence plan backups.
> I do know that you can back up log files when people are
> connected (I do this every hour with our production
> database) but once a week I perform a backup of all our
> databases with all sort of integrity checks, re builds of
> indexes and so on. Its these back ups the log file back
> ups are failing on.
> Thanks
> Peter
> >--Original Message--
> >Dear All,
> >
> >My nightly backups for log files are failing as there are
> >users still attached to the database being backup up.
> >
> >What is the best stratergy for dealing with this ?
> >
> >Do I
> >1) Make the DB single user ?
> >2) Have a script to kill all connections
> >3) Modify my backup plans
> >4) Some other
> >
> >Any help here would be greatly appriciated.
> >
> >Peter
> >.
> >

Friday, February 24, 2012

backup management

I there a backup method to automatically disconnect existing DB users and
prevent
additional logins during backup.
Thanks,
Ed
Hi
No. You need to write a script to KILL the users.
You do not need to kick users off the server, unless you have a good reason
to do it as Backups can be run under full usage..
Also, if the client applications are not well behaved, they might post
partial transactions.
Regards
Mike
"coenzyme" wrote:

> I there a backup method to automatically disconnect existing DB users and
> prevent
> additional logins during backup.
> Thanks,
> Ed

backup management

I there a backup method to automatically disconnect existing DB users and
prevent
additional logins during backup.
Thanks,
EdHi
No. You need to write a script to KILL the users.
You do not need to kick users off the server, unless you have a good reason
to do it as Backups can be run under full usage..
Also, if the client applications are not well behaved, they might post
partial transactions.
Regards
Mike
"coenzyme" wrote:

> I there a backup method to automatically disconnect existing DB users and
> prevent
> additional logins during backup.
> Thanks,
> Ed

backup management

I there a backup method to automatically disconnect existing DB users and
prevent
additional logins during backup.
Thanks,
EdHi
No. You need to write a script to KILL the users.
You do not need to kick users off the server, unless you have a good reason
to do it as Backups can be run under full usage..
Also, if the client applications are not well behaved, they might post
partial transactions.
Regards
Mike
"coenzyme" wrote:
> I there a backup method to automatically disconnect existing DB users and
> prevent
> additional logins during backup.
> Thanks,
> Ed

Sunday, February 12, 2012

Backup in Single-User Mode

I have a SQL Server 2000 database with numerous users. I would like to
backup the database without any users entering the database. I would like to
place the database in single-user mode and backup the database.
Could I have problems with this procedure.
Thanks,
In order to place it in single user mode you will have to force any other
users out first. But why do you want to do this as it is fine to backup the
db while users are in it.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:3F86F412-2679-4F11-9DA3-1B1D554DDF86@.microsoft.com...
> I have a SQL Server 2000 database with numerous users. I would like to
> backup the database without any users entering the database. I would like
> to
> place the database in single-user mode and backup the database.
> Could I have problems with this procedure.
> Thanks,
>

Backup in Single-User Mode

I have a SQL Server 2000 database with numerous users. I would like to
backup the database without any users entering the database. I would like to
place the database in single-user mode and backup the database.
Could I have problems with this procedure.
Thanks,In order to place it in single user mode you will have to force any other
users out first. But why do you want to do this as it is fine to backup the
db while users are in it.
--
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:3F86F412-2679-4F11-9DA3-1B1D554DDF86@.microsoft.com...
> I have a SQL Server 2000 database with numerous users. I would like to
> backup the database without any users entering the database. I would like
> to
> place the database in single-user mode and backup the database.
> Could I have problems with this procedure.
> Thanks,
>

Wednesday, March 7, 2012

backup only Users and Permissions

backup only Users and Permissions

backup only Users and Permissions

Backup Only Tool

Backup Only Tool

Backup Only Tool

Saturday, February 25, 2012

Backup of Log files

Friday, February 24, 2012

backup management

backup management

backup management

Sunday, February 12, 2012

Backup in Single-User Mode

Backup in Single-User Mode

sql backup files

Blog Archive

About Me