Script to know whether database backups are being done on SQL SERVER databases ??

Hi All,
I want to have a script which will let me know whether all the
databases in a SQL SERVER are being backed up.I tried using
sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
the databases for which they are being backedup.
TIA,
AKIf you're using maintenance plans, you can query like this:
SELECT TOP 10 *
FROM msdb.dbo.sysdbmaintplan_history
WHERE activity = 'Backup database'
Andre
"AkB" <anilkb@.gmail.com> wrote in message
news:47ce6498.0410191209.612cca5e@.posting.google.com...
> Hi All,
> I want to have a script which will let me know whether all the
> databases in a SQL SERVER are being backed up.I tried using
> sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
> the databases for which they are being backedup.
> TIA,
> AKsql

Script to know whether database backups are being done on SQL SERVER databases ??

Hi All,
I want to have a script which will let me know whether all the
databases in a SQL SERVER are being backed up.I tried using
sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
the databases for which they are being backedup.
TIA,
AK
If you're using maintenance plans, you can query like this:
SELECT TOP 10 *
FROM msdb.dbo.sysdbmaintplan_history
WHERE activity = 'Backup database'
Andre
"AkB" <anilkb@.gmail.com> wrote in message
news:47ce6498.0410191209.612cca5e@.posting.google.c om...
> Hi All,
> I want to have a script which will let me know whether all the
> databases in a SQL SERVER are being backed up.I tried using
> sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
> the databases for which they are being backedup.
> TIA,
> AK

Script to know whether database backups are being done on SQL SERVER databases ??

Hi All,
I want to have a script which will let me know whether all the
databases in a SQL SERVER are being backed up.I tried using
sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
the databases for which they are being backedup.
TIA,
AKIf you're using maintenance plans, you can query like this:
SELECT TOP 10 *
FROM msdb.dbo.sysdbmaintplan_history
WHERE activity = 'Backup database'
Andre
"AkB" <anilkb@.gmail.com> wrote in message
news:47ce6498.0410191209.612cca5e@.posting.google.com...
> Hi All,
> I want to have a script which will let me know whether all the
> databases in a SQL SERVER are being backed up.I tried using
> sysjobs,sysjobhistory,etc on msdb,but am unbale to joing them to know
> the databases for which they are being backedup.
> TIA,
> AK

script to do full or differential backups for all databases

Can someone help me write a script to do full backups or differential
backups for all user databases on a server ?
ThanksHi,
Script to backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','no_rthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@._SERVERNAM
E+'_'+@.DBNAME+'_'+ltr_im(rtrim(conve
rt
(char,getdate(),105)))+'Dump.b_ak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uzarO8fRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Can someone help me write a script to do full backups or differential
> backups for all user databases on a server ?
> Thanks
>|||Hassan
Another one using undocumented stored procedure
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uzarO8fRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Can someone help me write a script to do full backups or differential
> backups for all user databases on a server ?
> Thanks
>|||Hassan,
Here is a link to one I know of.
http://www.planet-source-code.com/v...zysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Script DB Object Level Security with SQL2005

We regularly need to take production DB backups and restore them onto our
development SQL Server boxes for testing (SQL2005 SP2). Since DB level
security is much different between production and stage, we need to script
stage rights (server and object level) and refresh those rights onto the
restored production DB. I was able to do that with EM in SQL 2000, but am
unable to do it with 2005 (SP2). I have tried (I think) every possible
option in Management Studio under Tasks / Generate Scripts for the DB and
none generate SQL for object level security. Is this a "bug" with SQL2005
SP2? I believe we were able to do it with SP1, but I don't recall which
options we chose.
--
KevinKevinL (KevinL@.discussions.microsoft.com) writes:
> We regularly need to take production DB backups and restore them onto our
> development SQL Server boxes for testing (SQL2005 SP2). Since DB level
> security is much different between production and stage, we need to script
> stage rights (server and object level) and refresh those rights onto the
> restored production DB. I was able to do that with EM in SQL 2000, but am
> unable to do it with 2005 (SP2). I have tried (I think) every possible
> option in Management Studio under Tasks / Generate Scripts for the DB and
> none generate SQL for object level security. Is this a "bug" with SQL2005
> SP2? I believe we were able to do it with SP1, but I don't recall which
> options we chose.
There is a scripting option "Script Object-level Permissions". that you
can use when you use the Generate Scripts task.
Under Tools->Options there is a new page "Scripting" where you can set
options for when you script individual objects from Object Explorer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
That appears to have resolved the problem (bug?) with scripting object level
permission, but role membership is still not scripted. Any idea why that
might be?|||KevinL (KevinL@.discussions.microsoft.com) writes:
> That appears to have resolved the problem (bug?) with scripting object
> level permission, but role membership is still not scripted. Any idea
> why that might be?
How do you script? When I script a database by right-clicking it, selecting
Tasks/Generate Scripts and include both Database Roles and Users I do get
role membership scripted at the end.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I see no option to script roles. This is what I did:
Open Management Studio, expand databases, right click the DB, click Tasks,
then Generate Script. The DB I right clicked is highlighted, I click next.
I verified the Script Options to make sure Script Object-Level Permissions i
s
True (there is no Option that mentions Roles) and click next. On Object
Types window choices are Schema, Stored Procedures, Tables, User-defined dat
a
types, User-defined functions and Users.
No matter which option(s) I choose, and I've tried every combination I can
think of, Role level permissions are not scripted.
--
Kevin
"Erland Sommarskog" wrote:

> KevinL (KevinL@.discussions.microsoft.com) writes:
> How do you script? When I script a database by right-clicking it, selectin
g
> Tasks/Generate Scripts and include both Database Roles and Users I do get
> role membership scripted at the end.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||KevinL (KevinL@.discussions.microsoft.com) writes:
> I see no option to script roles. This is what I did:
> Open Management Studio, expand databases, right click the DB, click
> Tasks, then Generate Script. The DB I right clicked is highlighted, I
> click next. I verified the Script Options to make sure Script
> Object-Level Permissions is True (there is no Option that mentions
> Roles) and click next. On Object Types window choices are Schema,
> Stored Procedures, Tables, User-defined data types, User-defined
> functions and Users.
> No matter which option(s) I choose, and I've tried every combination I can
> think of, Role level permissions are not scripted.
If "Database roles" are not listed, this would indicate that you don't
have any user-defined roles in the database, only the pre-defined roles,
db_owner and the like. Indeed, it appears that membership in these roles
are not scripted.
You can script all role membership with this SELECT:
SELECT 'EXEC sp_addrolemember ''' + r.name + ''', ''' + u.name + ''''
FROM sys.database_role_members rm
JOIN sys.database_principals u
ON rm.member_principal_id = u.principal_id
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE u.name <> 'dbo'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx