sql backup files: procedures

Showing posts with label procedures. Show all posts

Thursday, March 29, 2012

backup stored procedures?

Hey all,
I have to copy all the stored procs that i have on my development server to the clients server when I deploy my .NET app. I do not have remote access to the clients server. Anyone knows how I could do this? Are there any settings to take care of?
TIAScript your procedures to a file and then execute it through your install.

How are you getting the tables created?|||The tables have already been created by someone else.

Could you explain more about scripting the procedures to a file and executing through install??

Thanks !!|||In Enterprise Manager, select your stored procedures, right-click, and the follow the menu steps to generate a script. What you will get will be SQL statements that create your procedures. Save them to a file and then execute them during your install.

Sunday, March 25, 2012

Backup single table

I would like to take the backup of only 15 tables out of 800 tables and 8
stored procedures out of 90. The database is very huge and might take few
hours to take full database backup
is there anyway I can take backup only selected tables and stored
procedures? I have SQL Server 2000 running.
thanks
FarrukhIf you will regularly be backing up certain tables, then consider placing
them into their own filegroup and then backing up that filegroup. As for
stored procs, you can use Enterprise Manager to script them. As a best
practice, you should have your scripts in version control outside of your
SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
I would like to take the backup of only 15 tables out of 800 tables and 8
stored procedures out of 90. The database is very huge and might take few
hours to take full database backup
is there anyway I can take backup only selected tables and stored
procedures? I have SQL Server 2000 running.
thanks
Farrukh|||I dont get this request daily. Actually this is the first time i get this
request. I dont have any script. would you like to share ur script? thanks
"Tom Moreau" wrote:
> If you will regularly be backing up certain tables, then consider placing
> them into their own filegroup and then backing up that filegroup. As for
> stored procs, you can use Enterprise Manager to script them. As a best
> practice, you should have your scripts in version control outside of your
> SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
> I would like to take the backup of only 15 tables out of 800 tables and 8
> stored procedures out of 90. The database is very huge and might take few
> hours to take full database backup
> is there anyway I can take backup only selected tables and stored
> procedures? I have SQL Server 2000 running.
> thanks
> Farrukh
>
>|||There is no script here. Just right-click on the DB, click on All
Tasks->generate SQL script.
As for the filegoups, you are looking at a fair bit of work, since you now
need to migrate your existing tables to this filegroup, and that's a rebuild
of those tables.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
I dont get this request daily. Actually this is the first time i get this
request. I dont have any script. would you like to share ur script? thanks
"Tom Moreau" wrote:
> If you will regularly be backing up certain tables, then consider placing
> them into their own filegroup and then backing up that filegroup. As for
> stored procs, you can use Enterprise Manager to script them. As a best
> practice, you should have your scripts in version control outside of your
> SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
> I would like to take the backup of only 15 tables out of 800 tables and 8
> stored procedures out of 90. The database is very huge and might take few
> hours to take full database backup
> is there anyway I can take backup only selected tables and stored
> procedures? I have SQL Server 2000 running.
> thanks
> Farrukh
>
>|||> As for the filegoups, you are looking at a fair bit of work, since you now
> need to migrate your existing tables to this filegroup, and that's a rebuild
> of those tables.
... also, it isn't obvious how to get a backup of a filegroup available, unless you also have backup
of the rest of the database (which makes the filegroup backup kind of ... moot). In short, filegroup
backup should only be done with a very good understanding about backup in SQL Server and what it
does and does not give you. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uQgDLDnmIHA.4536@.TK2MSFTNGP06.phx.gbl...
> There is no script here. Just right-click on the DB, click on All
> Tasks->generate SQL script.
> As for the filegoups, you are looking at a fair bit of work, since you now
> need to migrate your existing tables to this filegroup, and that's a rebuild
> of those tables.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
> news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
> I dont get this request daily. Actually this is the first time i get this
> request. I dont have any script. would you like to share ur script? thanks
> "Tom Moreau" wrote:
>> If you will regularly be backing up certain tables, then consider placing
>> them into their own filegroup and then backing up that filegroup. As for
>> stored procs, you can use Enterprise Manager to script them. As a best
>> practice, you should have your scripts in version control outside of your
>> SQL Server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
>> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
>> I would like to take the backup of only 15 tables out of 800 tables and 8
>> stored procedures out of 90. The database is very huge and might take few
>> hours to take full database backup
>> is there anyway I can take backup only selected tables and stored
>> procedures? I have SQL Server 2000 running.
>> thanks
>> Farrukh
>>
>|||In a case like this, BCP MIGHT be a workable solution.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D39370AC-E225-4DAB-97C7-753FA5D4B378@.microsoft.com...
>> As for the filegoups, you are looking at a fair bit of work, since you
>> now
>> need to migrate your existing tables to this filegroup, and that's a
>> rebuild
>> of those tables.
> ... also, it isn't obvious how to get a backup of a filegroup available,
> unless you also have backup of the rest of the database (which makes the
> filegroup backup kind of ... moot). In short, filegroup backup should only
> be done with a very good understanding about backup in SQL Server and what
> it does and does not give you. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uQgDLDnmIHA.4536@.TK2MSFTNGP06.phx.gbl...
>> There is no script here. Just right-click on the DB, click on All
>> Tasks->generate SQL script.
>> As for the filegoups, you are looking at a fair bit of work, since you
>> now
>> need to migrate your existing tables to this filegroup, and that's a
>> rebuild
>> of those tables.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
>> news:055BC3BC-89AE-4424-95B0-A3CE72FA7AB3@.microsoft.com...
>> I dont get this request daily. Actually this is the first time i get this
>> request. I dont have any script. would you like to share ur script?
>> thanks
>> "Tom Moreau" wrote:
>> If you will regularly be backing up certain tables, then consider
>> placing
>> them into their own filegroup and then backing up that filegroup. As
>> for
>> stored procs, you can use Enterprise Manager to script them. As a best
>> practice, you should have your scripts in version control outside of
>> your
>> SQL Server.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "FARRUKH" <farrscorpio77@.hotmail.com> wrote in message
>> news:82CA5B73-6DBC-433B-A8F0-9249E9FD8590@.microsoft.com...
>> I would like to take the backup of only 15 tables out of 800 tables and
>> 8
>> stored procedures out of 90. The database is very huge and might take
>> few
>> hours to take full database backup
>> is there anyway I can take backup only selected tables and stored
>> procedures? I have SQL Server 2000 running.
>> thanks
>> Farrukh
>>
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup single table

Wednesday, March 7, 2012

Backup of Stored Procedures

How can I backup all the stored procedures from my SQL Server db to all .sql
files?Hi,
SQL 2000:-
1. Open Enterprise Manager
2. COnnect to SQL Server
3. Expand databases and select the correct database
4. Right click -- all tasks -- Select "Generate SQL script"
5. Click show all, click all stored Procedures
6. Select the required formating and options
7. Click ok and give the filename.sql and save to a location
SQL 2005
1. Open SQL Server Management stucio and connect to sql server
2. Expand the databases and select the database
3. Right click-- All taks -- generate scripts
4 select the necessar options and click next
5. Click procedures
6. click next and then the generated script will be craeted in query window
7. save to a location
Thanks
Hari
SQL Server MVP
"3451Jack329" <Jack1@.hotmail.com> wrote in message
news:DjEMg.45399$tQ.744575@.wagner.videotron.net...
> How can I backup all the stored procedures from my SQL Server db to all
> .sql files?
>|||You didn't say what version of SQL Server or whether you want to do it inter
actively or
programmatically. In Management Studio, you can right-click a database and s
ay "Generate Scripts. I
think Enterprise Manager has similar option. Or do it programmatically using
SMO or DMO, see
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"3451Jack329" <Jack1@.hotmail.com> wrote in message
news:DjEMg.45399$tQ.744575@.wagner.videotron.net...
> How can I backup all the stored procedures from my SQL Server db to all .s
ql files?
>

Backup of Stored Procedures

How can I backup all the stored procedures from my SQL Server db to all .sql
files?Hi,
SQL 2000:-
1. Open Enterprise Manager
2. COnnect to SQL Server
3. Expand databases and select the correct database
4. Right click -- all tasks -- Select "Generate SQL script"
5. Click show all, click all stored Procedures
6. Select the required formating and options
7. Click ok and give the filename.sql and save to a location
SQL 2005
1. Open SQL Server Management stucio and connect to sql server
2. Expand the databases and select the database
3. Right click-- All taks -- generate scripts
4 select the necessar options and click next
5. Click procedures
6. click next and then the generated script will be craeted in query window
7. save to a location
Thanks
Hari
SQL Server MVP
"3451Jack329" <Jack1@.hotmail.com> wrote in message
news:DjEMg.45399$tQ.744575@.wagner.videotron.net...
> How can I backup all the stored procedures from my SQL Server db to all
> .sql files?
>|||You didn't say what version of SQL Server or whether you want to do it interactively or
programmatically. In Management Studio, you can right-click a database and say "Generate Scripts. I
think Enterprise Manager has similar option. Or do it programmatically using SMO or DMO, see
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"3451Jack329" <Jack1@.hotmail.com> wrote in message
news:DjEMg.45399$tQ.744575@.wagner.videotron.net...
> How can I backup all the stored procedures from my SQL Server db to all .sql files?
>

Monday, February 13, 2012

Backup Job Sometimes Stops at msdb

Hi,
We have been using the same stored procedures for backing up our databases
for years with no issues. We now have a puzzling issue on SQL Server 2005
(sp1) and msdb. The stored procedure uses a cursor to get a list of databases
in the group (alphabetically). The cursor always gets all of the databases
but sometimes it doesn't back all of them up (like it thinks that it's done).
It always seems to stop at msdb. It's not because msdb is the last in the
list, I created a database called mz and it still only backed up master and
model (the only other two in this group). I used @.@.cursor_rows so I know that
all for database names were in the cursor. This doesn't ALWAYS happen -
sometimes it backs up all of the databases just fine. This seems to ONLY
happen on the 64-bit machines. We're using litespeed and I'll try to
reproduce the issue using native commands.
DECLARE @.db VARCHAR(100) -- database name
DECLARE @.NumBakFiles INT -- number of backup files to be created
DECLARE @.DestServer VARCHAR(50) -- backup destination server
DECLARE @.DestShare VARCHAR(50) -- backup destination file share
DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
DECLARE cDB CURSOR FOR
SELECT [name]
FROM master.sys.databases
--WHERE [name] in ('msdb')
WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')
-- AND UPPER([name]) < 'H' -- AthruG
AND [name] > 'h%' -- HthruM
AND [name] < 'n%' -- HthruM
-- AND [name] >= 'n%' -- NthruS
-- AND [name] <= 't%' -- NthruS
-- AND [name] <= 't%' -- TthruZ
ORDER BY
[name],
create_date
OPEN cDB
print @.@.cursor_rows
FETCH NEXT
FROM cDB
INTO @.db
WHILE (@.@.FETCH_STATUS=0)
BEGIN
PRINT @.db
SET @.NumBakFiles = 1
SET @.DestServer = '\\SQLDUMP\'
--SELECT @.DestServer = 'D:\SQLDATADUMP\'
SET @.DestShare = 'SQLDUMPSHARE\'
SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '\',
'_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +
REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
'.bak'
--SELECT @.dbDestDisk = @.DestServer + '\' + @.db + '_FullDB_' +
REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
'.bak'
-- Backup Database File
EXEC master.dbo.xp_backup_database @.database = @.db, @.filename = @.dbDestDisk, @.init=1
FETCH NEXT
FROM cDB
INTO @.db
END --WHILE
CLOSE cDB
DEALLOCATE cDB
GOI was able to reproduce the issue using native sql server commands:
4
master
Processed 360 pages for database 'master', file 'master' on file 10.
Processed 2 pages for database 'master', file 'mastlog' on file 10.
BACKUP DATABASE successfully processed 362 pages in 0.186 seconds (15.921
MB/sec).
model
Processed 160 pages for database 'model', file 'modeldev' on file 10.
Processed 2 pages for database 'model', file 'modellog' on file 10.
BACKUP DATABASE successfully processed 162 pages in 0.081 seconds (16.333
MB/sec).
"Michelle" wrote:
> Hi,
> We have been using the same stored procedures for backing up our databases
> for years with no issues. We now have a puzzling issue on SQL Server 2005
> (sp1) and msdb. The stored procedure uses a cursor to get a list of databases
> in the group (alphabetically). The cursor always gets all of the databases
> but sometimes it doesn't back all of them up (like it thinks that it's done).
> It always seems to stop at msdb. It's not because msdb is the last in the
> list, I created a database called mz and it still only backed up master and
> model (the only other two in this group). I used @.@.cursor_rows so I know that
> all for database names were in the cursor. This doesn't ALWAYS happen -
> sometimes it backs up all of the databases just fine. This seems to ONLY
> happen on the 64-bit machines. We're using litespeed and I'll try to
> reproduce the issue using native commands.
> DECLARE @.db VARCHAR(100) -- database name
> DECLARE @.NumBakFiles INT -- number of backup files to be created
> DECLARE @.DestServer VARCHAR(50) -- backup destination server
> DECLARE @.DestShare VARCHAR(50) -- backup destination file share
> DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
>
> DECLARE cDB CURSOR FOR
> SELECT [name]
> FROM master.sys.databases
> --WHERE [name] in ('msdb')
> WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')
> -- AND UPPER([name]) < 'H' -- AthruG
> AND [name] > 'h%' -- HthruM
> AND [name] < 'n%' -- HthruM
> -- AND [name] >= 'n%' -- NthruS
> -- AND [name] <= 't%' -- NthruS
> -- AND [name] <= 't%' -- TthruZ
> ORDER BY
> [name],
> create_date
> OPEN cDB
> print @.@.cursor_rows
> FETCH NEXT
> FROM cDB
> INTO @.db
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> PRINT @.db
> SET @.NumBakFiles = 1
> SET @.DestServer = '\\SQLDUMP\'
> --SELECT @.DestServer = 'D:\SQLDATADUMP\'
> SET @.DestShare = 'SQLDUMPSHARE\'
> SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '\',
> '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> --SELECT @.dbDestDisk = @.DestServer + '\' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> -- Backup Database File
> EXEC master.dbo.xp_backup_database @.database = @.db, @.filename => @.dbDestDisk, @.init=1
> FETCH NEXT
> FROM cDB
> INTO @.db
> END --WHILE
> CLOSE cDB
> DEALLOCATE cDB
> GO
>|||A couple of comments or suggestions here. One is to print out the results
of the dynamic backup statements and run the one for MSDB to see what you
get. Have you tried changing the order by to see if it always stops on the
same db? Another is that i would recommend creating a table that holds the
meta data for the backups that you want to issue. You can have a row for
each db with a column for Full backups that can denote if that backup should
be done or not instead of the WHERE clause you have now.
--
Andrew J. Kelly SQL MVP
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:922568F2-A7C0-48F0-A9DE-03FCB49D3CA3@.microsoft.com...
> Hi,
> We have been using the same stored procedures for backing up our databases
> for years with no issues. We now have a puzzling issue on SQL Server 2005
> (sp1) and msdb. The stored procedure uses a cursor to get a list of
> databases
> in the group (alphabetically). The cursor always gets all of the databases
> but sometimes it doesn't back all of them up (like it thinks that it's
> done).
> It always seems to stop at msdb. It's not because msdb is the last in the
> list, I created a database called mz and it still only backed up master
> and
> model (the only other two in this group). I used @.@.cursor_rows so I know
> that
> all for database names were in the cursor. This doesn't ALWAYS happen -
> sometimes it backs up all of the databases just fine. This seems to ONLY
> happen on the 64-bit machines. We're using litespeed and I'll try to
> reproduce the issue using native commands.
> DECLARE @.db VARCHAR(100) -- database name
> DECLARE @.NumBakFiles INT -- number of backup files to be created
> DECLARE @.DestServer VARCHAR(50) -- backup destination server
> DECLARE @.DestShare VARCHAR(50) -- backup destination file share
> DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
>
> DECLARE cDB CURSOR FOR
> SELECT [name]
> FROM master.sys.databases
> --WHERE [name] in ('msdb')
> WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')
> -- AND UPPER([name]) < 'H' -- AthruG
> AND [name] > 'h%' -- HthruM
> AND [name] < 'n%' -- HthruM
> -- AND [name] >= 'n%' -- NthruS
> -- AND [name] <= 't%' -- NthruS
> -- AND [name] <= 't%' -- TthruZ
> ORDER BY
> [name],
> create_date
> OPEN cDB
> print @.@.cursor_rows
> FETCH NEXT
> FROM cDB
> INTO @.db
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> PRINT @.db
> SET @.NumBakFiles = 1
> SET @.DestServer = '\\SQLDUMP\'
> --SELECT @.DestServer = 'D:\SQLDATADUMP\'
> SET @.DestShare = 'SQLDUMPSHARE\'
> SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '\',
> '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> --SELECT @.dbDestDisk = @.DestServer + '\' + @.db + '_FullDB_' +
> REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> '.bak'
> -- Backup Database File
> EXEC master.dbo.xp_backup_database @.database = @.db, @.filename => @.dbDestDisk, @.init=1
> FETCH NEXT
> FROM cDB
> INTO @.db
> END --WHILE
> CLOSE cDB
> DEALLOCATE cDB
> GO
>|||I reversed the order and was able to reproduce the issue. After several
successful runs, it ran and ONLY backed up the mz database. It's not even
building a backup statement for msdb. It's like it thinks that msdb isn't in
the cursor and quits when it finishes the database just before msdb. We've
only seen this with 2005 64-bit (Enterprise AND Standard Edition).
I appreciate the table suggestion. We've considered doing this for a lot of
admin stuff but just can't get resources allocated to that right now. We have
about 50 sql servers.
"Andrew J. Kelly" wrote:
> A couple of comments or suggestions here. One is to print out the results
> of the dynamic backup statements and run the one for MSDB to see what you
> get. Have you tried changing the order by to see if it always stops on the
> same db? Another is that i would recommend creating a table that holds the
> meta data for the backups that you want to issue. You can have a row for
> each db with a column for Full backups that can denote if that backup should
> be done or not instead of the WHERE clause you have now.
> --
> Andrew J. Kelly SQL MVP
> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
> news:922568F2-A7C0-48F0-A9DE-03FCB49D3CA3@.microsoft.com...
> >
> > Hi,
> >
> > We have been using the same stored procedures for backing up our databases
> > for years with no issues. We now have a puzzling issue on SQL Server 2005
> > (sp1) and msdb. The stored procedure uses a cursor to get a list of
> > databases
> > in the group (alphabetically). The cursor always gets all of the databases
> > but sometimes it doesn't back all of them up (like it thinks that it's
> > done).
> > It always seems to stop at msdb. It's not because msdb is the last in the
> > list, I created a database called mz and it still only backed up master
> > and
> > model (the only other two in this group). I used @.@.cursor_rows so I know
> > that
> > all for database names were in the cursor. This doesn't ALWAYS happen -
> > sometimes it backs up all of the databases just fine. This seems to ONLY
> > happen on the 64-bit machines. We're using litespeed and I'll try to
> > reproduce the issue using native commands.
> >
> > DECLARE @.db VARCHAR(100) -- database name
> > DECLARE @.NumBakFiles INT -- number of backup files to be created
> > DECLARE @.DestServer VARCHAR(50) -- backup destination server
> > DECLARE @.DestShare VARCHAR(50) -- backup destination file share
> > DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
> >
> >
> >
> > DECLARE cDB CURSOR FOR
> > SELECT [name]
> > FROM master.sys.databases
> > --WHERE [name] in ('msdb')
> > WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')
> > -- AND UPPER([name]) < 'H' -- AthruG
> > AND [name] > 'h%' -- HthruM
> > AND [name] < 'n%' -- HthruM
> >
> > -- AND [name] >= 'n%' -- NthruS
> > -- AND [name] <= 't%' -- NthruS
> >
> > -- AND [name] <= 't%' -- TthruZ
> > ORDER BY
> > [name],
> > create_date
> >
> > OPEN cDB
> > print @.@.cursor_rows
> > FETCH NEXT
> > FROM cDB
> > INTO @.db
> >
> > WHILE (@.@.FETCH_STATUS=0)
> > BEGIN
> > PRINT @.db
> > SET @.NumBakFiles = 1
> > SET @.DestServer = '\\SQLDUMP\'
> > --SELECT @.DestServer = 'D:\SQLDATADUMP\'
> > SET @.DestShare = 'SQLDUMPSHARE\'
> >
> > SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '\',
> > '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +
> > REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> > '.bak'
> > --SELECT @.dbDestDisk = @.DestServer + '\' + @.db + '_FullDB_' +
> > REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
> > '.bak'
> >
> > -- Backup Database File
> > EXEC master.dbo.xp_backup_database @.database = @.db, @.filename => > @.dbDestDisk, @.init=1
> >
> > FETCH NEXT
> > FROM cDB
> > INTO @.db
> >
> > END --WHILE
> >
> > CLOSE cDB
> > DEALLOCATE cDB
> > GO
> >
>
>|||If you can reproduce this I would give MS PSS a call. Does this happen if
you just run the select that the feeds the cursor? You might also try
creating a STATIC cursor instead of the default and see if that makes a
difference.
--
Andrew J. Kelly SQL MVP
"Michelle" <Michelle@.discussions.microsoft.com> wrote in message
news:7A06DB4B-E432-4625-8CEF-F521FB5F587D@.microsoft.com...
>I reversed the order and was able to reproduce the issue. After several
> successful runs, it ran and ONLY backed up the mz database. It's not even
> building a backup statement for msdb. It's like it thinks that msdb isn't
> in
> the cursor and quits when it finishes the database just before msdb. We've
> only seen this with 2005 64-bit (Enterprise AND Standard Edition).
> I appreciate the table suggestion. We've considered doing this for a lot
> of
> admin stuff but just can't get resources allocated to that right now. We
> have
> about 50 sql servers.
> "Andrew J. Kelly" wrote:
>> A couple of comments or suggestions here. One is to print out the
>> results
>> of the dynamic backup statements and run the one for MSDB to see what you
>> get. Have you tried changing the order by to see if it always stops on
>> the
>> same db? Another is that i would recommend creating a table that holds
>> the
>> meta data for the backups that you want to issue. You can have a row for
>> each db with a column for Full backups that can denote if that backup
>> should
>> be done or not instead of the WHERE clause you have now.
>> --
>> Andrew J. Kelly SQL MVP
>> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
>> news:922568F2-A7C0-48F0-A9DE-03FCB49D3CA3@.microsoft.com...
>> >
>> > Hi,
>> >
>> > We have been using the same stored procedures for backing up our
>> > databases
>> > for years with no issues. We now have a puzzling issue on SQL Server
>> > 2005
>> > (sp1) and msdb. The stored procedure uses a cursor to get a list of
>> > databases
>> > in the group (alphabetically). The cursor always gets all of the
>> > databases
>> > but sometimes it doesn't back all of them up (like it thinks that it's
>> > done).
>> > It always seems to stop at msdb. It's not because msdb is the last in
>> > the
>> > list, I created a database called mz and it still only backed up master
>> > and
>> > model (the only other two in this group). I used @.@.cursor_rows so I
>> > know
>> > that
>> > all for database names were in the cursor. This doesn't ALWAYS happen -
>> > sometimes it backs up all of the databases just fine. This seems to
>> > ONLY
>> > happen on the 64-bit machines. We're using litespeed and I'll try to
>> > reproduce the issue using native commands.
>> >
>> > DECLARE @.db VARCHAR(100) -- database name
>> > DECLARE @.NumBakFiles INT -- number of backup files to be
>> > created
>> > DECLARE @.DestServer VARCHAR(50) -- backup destination server
>> > DECLARE @.DestShare VARCHAR(50) -- backup destination file share
>> > DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination
>> > disk
>> >
>> >
>> >
>> > DECLARE cDB CURSOR FOR
>> > SELECT [name]
>> > FROM master.sys.databases
>> > --WHERE [name] in ('msdb')
>> > WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND',
>> > 'PUBS','SQLPROFILE')
>> > -- AND UPPER([name]) < 'H' -- AthruG
>> > AND [name] > 'h%' -- HthruM
>> > AND [name] < 'n%' -- HthruM
>> >
>> > -- AND [name] >= 'n%' -- NthruS
>> > -- AND [name] <= 't%' -- NthruS
>> >
>> > -- AND [name] <= 't%' -- TthruZ
>> > ORDER BY
>> > [name],
>> > create_date
>> >
>> > OPEN cDB
>> > print @.@.cursor_rows
>> > FETCH NEXT
>> > FROM cDB
>> > INTO @.db
>> >
>> > WHILE (@.@.FETCH_STATUS=0)
>> > BEGIN
>> > PRINT @.db
>> > SET @.NumBakFiles = 1
>> > SET @.DestServer = '\\SQLDUMP\'
>> > --SELECT @.DestServer = 'D:\SQLDATADUMP\'
>> > SET @.DestShare = 'SQLDUMPSHARE\'
>> >
>> > SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME,
>> > '\',
>> > '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +
>> > REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','')
>> > +
>> > '.bak'
>> > --SELECT @.dbDestDisk = @.DestServer + '\' + @.db + '_FullDB_' +
>> > REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','')
>> > +
>> > '.bak'
>> >
>> > -- Backup Database File
>> > EXEC master.dbo.xp_backup_database @.database = @.db, @.filename =>> > @.dbDestDisk, @.init=1
>> >
>> > FETCH NEXT
>> > FROM cDB
>> > INTO @.db
>> >
>> > END --WHILE
>> >
>> > CLOSE cDB
>> > DEALLOCATE cDB
>> > GO
>> >
>>|||... and I'd do some more investigating before opening a case with MS PSS. This includes:
Do the cursor without backup. Instead of backup, just insert a row into a table. This would
determine whether the problem is with the cursor or if backup has to be involved.
Do an insert both before and after the backup.
Etc...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkqrvvJ9GHA.2316@.TK2MSFTNGP04.phx.gbl...
> If you can reproduce this I would give MS PSS a call. Does this happen if you just run the select
> that the feeds the cursor? You might also try creating a STATIC cursor instead of the default and
> see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
> news:7A06DB4B-E432-4625-8CEF-F521FB5F587D@.microsoft.com...
>>I reversed the order and was able to reproduce the issue. After several
>> successful runs, it ran and ONLY backed up the mz database. It's not even
>> building a backup statement for msdb. It's like it thinks that msdb isn't in
>> the cursor and quits when it finishes the database just before msdb. We've
>> only seen this with 2005 64-bit (Enterprise AND Standard Edition).
>> I appreciate the table suggestion. We've considered doing this for a lot of
>> admin stuff but just can't get resources allocated to that right now. We have
>> about 50 sql servers.
>> "Andrew J. Kelly" wrote:
>> A couple of comments or suggestions here. One is to print out the results
>> of the dynamic backup statements and run the one for MSDB to see what you
>> get. Have you tried changing the order by to see if it always stops on the
>> same db? Another is that i would recommend creating a table that holds the
>> meta data for the backups that you want to issue. You can have a row for
>> each db with a column for Full backups that can denote if that backup should
>> be done or not instead of the WHERE clause you have now.
>> --
>> Andrew J. Kelly SQL MVP
>> "Michelle" <Michelle@.discussions.microsoft.com> wrote in message
>> news:922568F2-A7C0-48F0-A9DE-03FCB49D3CA3@.microsoft.com...
>> >
>> > Hi,
>> >
>> > We have been using the same stored procedures for backing up our databases
>> > for years with no issues. We now have a puzzling issue on SQL Server 2005
>> > (sp1) and msdb. The stored procedure uses a cursor to get a list of
>> > databases
>> > in the group (alphabetically). The cursor always gets all of the databases
>> > but sometimes it doesn't back all of them up (like it thinks that it's
>> > done).
>> > It always seems to stop at msdb. It's not because msdb is the last in the
>> > list, I created a database called mz and it still only backed up master
>> > and
>> > model (the only other two in this group). I used @.@.cursor_rows so I know
>> > that
>> > all for database names were in the cursor. This doesn't ALWAYS happen -
>> > sometimes it backs up all of the databases just fine. This seems to ONLY
>> > happen on the 64-bit machines. We're using litespeed and I'll try to
>> > reproduce the issue using native commands.
>> >
>> > DECLARE @.db VARCHAR(100) -- database name
>> > DECLARE @.NumBakFiles INT -- number of backup files to be created
>> > DECLARE @.DestServer VARCHAR(50) -- backup destination server
>> > DECLARE @.DestShare VARCHAR(50) -- backup destination file share
>> > DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
>> >
>> >
>> >
>> > DECLARE cDB CURSOR FOR
>> > SELECT [name]
>> > FROM master.sys.databases
>> > --WHERE [name] in ('msdb')
>> > WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')
>> > -- AND UPPER([name]) < 'H' -- AthruG
>> > AND [name] > 'h%' -- HthruM
>> > AND [name] < 'n%' -- HthruM
>> >
>> > -- AND [name] >= 'n%' -- NthruS
>> > -- AND [name] <= 't%' -- NthruS
>> >
>> > -- AND [name] <= 't%' -- TthruZ
>> > ORDER BY
>> > [name],
>> > create_date
>> >
>> > OPEN cDB
>> > print @.@.cursor_rows
>> > FETCH NEXT
>> > FROM cDB
>> > INTO @.db
>> >
>> > WHILE (@.@.FETCH_STATUS=0)
>> > BEGIN
>> > PRINT @.db
>> > SET @.NumBakFiles = 1
>> > SET @.DestServer = '\\SQLDUMP\'
>> > --SELECT @.DestServer = 'D:\SQLDATADUMP\'
>> > SET @.DestShare = 'SQLDUMPSHARE\'
>> >
>> > SET @.dbDestDisk = @.DestServer + @.DestShare + REPLACE(@.@.SERVERNAME, '\',
>> > '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +
>> > REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
>> > '.bak'
>> > --SELECT @.dbDestDisk = @.DestServer + '\' + @.db + '_FullDB_' +
>> > REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','') +
>> > '.bak'
>> >
>> > -- Backup Database File
>> > EXEC master.dbo.xp_backup_database @.database = @.db, @.filename =>> > @.dbDestDisk, @.init=1
>> >
>> > FETCH NEXT
>> > FROM cDB
>> > INTO @.db
>> >
>> > END --WHILE
>> >
>> > CLOSE cDB
>> > DEALLOCATE cDB
>> > GO
>> >
>>
>

Backup Job Sometimes Stops at msdb

Hi,

We have been using the same stored procedures for backing up our

databases for years with no issues. We now have a puzzling issue on SQL

Server 2005 (sp1) and msdb. The stored procedure uses a cursor to get a

list of databases in the group (alphabetically). The cursor always gets

all of the databases but sometimes it doesn't back all of them up (like

it thinks that it's done). It always seems to stop at msdb. It's not

because msdb is the last in the list, I created a database called mz

and it still only backed up master and model (the only other two in

this group). I used @.@.cursor_rows so I know that all for database names

were in the cursor. This doesn't ALWAYS happen - sometimes it backs up

all of the databases just fine. This seems to ONLY happen on the 64-bit

machines. We're using litespeed and I'll try to reproduce the issue

using native commands.

DECLARE @.db VARCHAR(100) -- database name

DECLARE @.NumBakFiles INT -- number of backup files to be created

DECLARE @.DestServer VARCHAR(50) -- backup destination server

DECLARE @.DestShare VARCHAR(50) -- backup destination file share

DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
DECLARE cDB CURSOR FOR

SELECT [name]

FROM master.sys.databases

--WHERE [name] in ('msdb')

WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')

-- AND UPPER([name]) < 'H' -- AthruG

AND [name] > 'h%' -- HthruM

AND [name] < 'n%' -- HthruM

-- AND [name] >= 'n%' -- NthruS

-- AND [name] <= 't%' -- NthruS

-- AND [name] <= 't%' -- TthruZ

ORDER BY

[name],

create_date

OPEN cDB

print @.@.cursor_rows

FETCH NEXT

FROM cDB

INTO @.db

WHILE (@.@.FETCH_STATUS=0)

BEGIN

PRINT @.db

SET @.NumBakFiles = 1

SET @.DestServer = '\\SQLDUMP\'

--SELECT @.DestServer = 'D:\SQLDATADUMP\'

SET @.DestShare = 'SQLDUMPSHARE\'

SET @.dbDestDisk = @.DestServer + @.DestShare +

REPLACE(@.@.SERVERNAME, '\', '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +

REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','')

+ '.bak'

--SELECT @.dbDestDisk = @.DestServer + '\' + @.db +

'_FullDB_' + REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':',

''), ' ','') + '.bak'

-- Backup Database File

EXEC master.dbo.xp_backup_database @.database = @.db, @.filename = @.dbDestDisk, @.init=1

FETCH NEXT

FROM cDB

INTO @.db

END --WHILE

CLOSE cDB

DEALLOCATE cDB

GOI was able to reproduce the issue using native sql server commands:

master

Processed 360 pages for database 'master', file 'master' on file 10.

Processed 2 pages for database 'master', file 'mastlog' on file 10.

BACKUP DATABASE successfully processed 362 pages in 0.186 seconds (15.921 MB/sec).

model

Processed 160 pages for database 'model', file 'modeldev' on file 10.

Processed 2 pages for database 'model', file 'modellog' on file 10.

BACKUP DATABASE successfully processed 162 pages in 0.081 seconds (16.333 MB/sec).|||I changed the cursor to a static cursor and have not had any issues

since then. It was intermittent before but it happened at least once or

twice per week.

Thursday, March 29, 2012

backup stored procedures?

Sunday, March 25, 2012

Backup single table

Backup single table

Wednesday, March 7, 2012

Backup of Stored Procedures

Backup of Stored Procedures

Monday, February 13, 2012

Backup Job Sometimes Stops at msdb

Backup Job Sometimes Stops at msdb

Backup Job Sometimes Stops at msdb

Backup Job Sometimes Stops at msdb

sql backup files

Blog Archive

About Me