Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Backup databases stored procedure using SQL LiteSpeed - new version

EDIT: Please see my new version of the isp_Backup stored procedure.

I decided to finish posting the new versions of my database maintenance routines.  Here's the new version for backing up your databases using SQL LiteSpeed:

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Backup_LS
--
-- AUTHOR               : Tara Duggan
-- DATE   : December 18, 2003
--
-- INPUTS  : @Path - location of the backups
-- OUTPUTS  : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional)  : EXEC isp_Backup_LS @Path = 'G:\MSSQL\Backup\', @Retention = 5
----------------------------------------------------------------------------------------------------
CREATE           PROC isp_Backup_LS
(@Path VARCHAR(100), @Retention INT = 2)
AS

SET NOCOUNT ON

DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @NumProcs INT -- stores the number of processors that the server has registered
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @desc VARCHAR(200) -- stores the description of the backup
DECLARE @filename VARCHAR(200) -- stores the path and file name of the bkp file

-- Get the list of the databases to be backed up, does not include master, model, msdb, tempdb, Northwind, or pubs
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
ORDER BY name

-- Get the number of processors that the server has
EXEC master..xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager',
  @value_name = 'RegisteredProcessors',
  @value = @NumProcs OUTPUT

--  We want n - 1 threads, where n is the number of processors
SELECT @NumProcs = @NumProcs - 1

-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN

 -- Get the current date using style 120, remove all dashes, spaces, and colons
 SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

 -- Build the .bkp path and file name
 SELECT @filename = @Path + @DBName + '\' + @DBName + '_LS_Full_' + @Now + '.bkp'

 -- Build the description of the backup
 SELECT @desc = 'Full backup of ' + @DBName + ' - ' + CONVERT(VARCHAR(50), GETDATE())
 
 -- Build the dir command that will check to see if the directory exists
 SELECT @cmd = 'dir ' + @Path + @DBName

 -- Run the dir command, put output of xp_cmdshell into @result
 EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

 -- If the directory does not exist, we must create it
 IF @result <> 0
 BEGIN
  
  -- Build the mkdir command  
  SELECT @cmd = 'mkdir ' + @Path + @DBName

  -- Create the directory
  EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

 END
 -- The directory exists, so let's delete files older than two days
 ELSE
 BEGIN

  -- Stores the name of the file to be deleted
  DECLARE @WhichFile VARCHAR(1000)

  CREATE TABLE #DeleteOldFiles
  (
  DirInfo VARCHAR(7000)
  )

  -- Build the command that will list out all of the files in a directory
  SELECT @cmd = 'dir ' + @Path + @DBName + ' /OD'

  -- Run the dir command and put the results into a temp table
  INSERT INTO #DeleteOldFiles
  EXEC master.dbo.xp_cmdshell @cmd

  -- Delete all rows from the temp table except the ones that correspond to the files to be deleted
  DELETE
  FROM #DeleteOldFiles
  WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention
  
  -- Get the file name portion of the row that corresponds to the file to be deleted
  SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
  FROM #DeleteOldFiles
  
  SET @RowCnt = @@ROWCOUNT
  
  -- Interate through the temp table until there are no more files to delete
  WHILE @RowCnt <> 0
  BEGIN
  
   -- Build the del command
   SELECT @cmd = 'del ' + @Path + + @DBName + '\' + @WhichFile + ' /Q /F'
   
   -- Delete the file
   EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
   
   -- To move to the next file, the current file name needs to be deleted from the temp table
   DELETE
   FROM #DeleteOldFiles
   WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @WhichFile

   -- Get the file name portion of the row that corresponds to the file to be deleted
   SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
   FROM #DeleteOldFiles
  
   SET @RowCnt = @@ROWCOUNT
  
  END
  
  DROP TABLE #DeleteOldFiles

 END

 -- Backup the database using xp_backup_database
 EXEC master.dbo.xp_backup_database
  @database = @DBName,
  @filename = @filename,
  @backupname = @DBName,
  @desc = @desc,
  @init = 1,
  @threads = @NumProcs

 -- To move onto the next database, the current database name needs to be deleted from the temp table
 DELETE
 FROM #WhichDatabase
 WHERE name = @DBName

 -- Get the database to be backed up
 SELECT TOP 1 @DBName = name
 FROM #WhichDatabase

 SET @RowCnt = @@ROWCOUNT

 -- Let the system rest for 5 seconds before starting on the next backup
 WAITFOR DELAY '00:00:05'

END

DROP TABLE #WhichDatabase

SET NOCOUNT OFF

RETURN 0

 

 

 

GO

Print | posted on Thursday, September 23, 2004 3:30 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Backup databases stored procedure - SQL LiteSpeed version

9/23/2004 6:31 PM | Ramblings of a DBA
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Help please. I have a file with .bkp extention. Can I use SQL Server to open it?

Many Thanks
9/29/2005 6:27 AM | M K Saumtally
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

What do you mean open it? If it is a SQL Server backup, then you can restore it to see what's inside. If it's from SQL Litespeed, then you need Litespeed to restore it into SQL Server.
9/29/2005 7:48 AM | Tara
Gravatar

# re: Backup databases stored procedure

Hi,

--@path = 'D\Program Files\MSSQL\Backup\'
-- Build the .bkp path and file name
SELECT @filename = @Path + @DBName + '\' + @DBName + '_' + @Now + '.bkp'

-- Build the dir command that will check to see if the directory exists
SELECT @cmd = 'dir ' + @Path + @DBName

-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN

-- Build the mkdir command
SELECT @cmd = 'mkdir ' + @Path + @DBName

-- Create the directory
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

END
-- The directory exists, so lets backup
BACKUP DATABASE @DBName
TO DISK = @filename
WITH INIT
print 'Completed'





I am using this code - within my stored proc(sp) to do my backup. Currently the sp is executed in Test1 database but i want the backup of Test2 database to be done here(connected to Test1) inside the sp. When i try to execute it - i am unable to see the directory created in the my computer explorer. (Its this step : -- Build the mkdir command
SELECT @cmd = 'mkdir ' + @Path + @DBName
that is causing some prob for me to execute).
What or where am i going wrong.
Please mail me regarding this.
Thnx,
Natalie

1/18/2006 1:59 PM | rnata12@yahoo.com
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

I don't understand what you are trying to do.
1/18/2006 2:02 PM | Tara
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Hi

i have download one file frm ftp server.in that depoy folder one file .bkp file.i want acceess that file.that file sige is 40 mega bytes.i think that file is sql database file.how to restore that file to sql database.

if any body knows plz help me.


thnx & regards,

Satya
2/15/2006 9:51 PM | satya
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Run RESTORE DATABASE command to restore the file to your SQL Server. You can also use the GUI in Enterprise Manager if you aren't comfortable with T-SQL commands.
2/16/2006 9:45 AM | Tara
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Where do I get xp_backup_database? I have SS2K Standard Edition.
3/21/2006 5:42 AM | Rick
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Rick, you have to purchase SQL LiteSpeed to use that extended stored procedure.
3/21/2006 9:07 AM | Tara
Gravatar

# To find out the current path in sql server

RESTORE DATABASE NewAbitaDB
FROM DISK = 'c:\7augbkup.bak' WITH REPLACE

here instead of "C:\", how to get the current path of the backupfile where it's loaded in any drive.
8/16/2006 9:24 PM | anukirthi
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

anukirthi,

I don't understand your question. Could you explain?
8/16/2006 9:29 PM | Tara
Gravatar

# To find out the current path in sql server

RESTORE DATABASE NewAbitaDB
FROM DISK = 'c:\7augbkup.bak' WITH REPLACE

Here i have to restore the backup file in my client machine, there i dont want to specify the current path, it might be in any drive,
so, in 'c:\7augbkup.bak' instead of c:\ it should take the the current path of the backup file where it's loaded i.e c:\ or d:\ likewise? how to get it?
8/16/2006 9:51 PM | anukirthi
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Well you'd have to implement some kind of search mechanism in order to find the file. But what if there were duplicate file names? How would it know which one to grab? SQL Server requires that you tell it exactly where it is located, so you'd have to write something to find it.
8/16/2006 10:15 PM | Tara
Gravatar

# To find out the current path in sql server

any method is there to get the current path of the database or backup file?
8/16/2006 10:22 PM | anukirthi
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

You can get the path of the database using sp_helpdb DatabaseNameGoesHere.
8/16/2006 11:31 PM | Tara
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

A question not actually related to SQL LiteSpeed: i'm using xp_cmdshell like shown here to check whether a file exists, and it works for files on local disks (i see the output of DIR in the result of xp_cmdshell); however, for files on network drives, it keeps returning "Access denied".

The command i execute from Query Analyzer is:

exec master.dbo.xp_cmdshell 'DIR \\MyServer\MyShare\MySubfolder\MyFile.txt'

and i'm logged as Administrator with Windows authentication (not SQL Server authentication).
8/25/2006 9:11 AM | Davide
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

xp_cmdshell runs under the SQL Server service account and not whatever you are logged on as. So you need to check if that service account has access to that path. If you are using Local System Account for the service, then you need to change it as Local System Account only has access to the local database server.
8/25/2006 9:34 AM | Tara
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

Thanks! On monday i'll try that and post the results; gotta run now, it's friday evening here in Italy :-) .
8/25/2006 9:42 AM | Davide
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

i need to know how tp make backup of ms access database throught vba code
10/6/2006 7:09 AM | ahmad
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

ahmad,

You should post your question in an Access forum or on someone's blog who supports Access. I don't support Access, just MS SQL Server.
10/6/2006 9:21 AM | Tara
Gravatar

# re: Backup databases stored procedure using SQL LiteSpeed - new version

HI,
I m a newbie in SQL. Recently i was asked to move an SQL databse from one pc to another.I googled a bit n found sum threads which says backinup n restoring is teh best way to move it.so i backed up teh databse from the original drive n tried to restore it to teh new pc.but then an error occured sayin " teh backup was created using anther version of SQL n create a new SQL backup using teh present version" [ one pc is hvin SQL old version another SQL new version ]...i dnt want to take any risk of losing any information in teh database.Can any one help me pls...n btw i went thru tht code thing u wrote up there but i dnt understand where to put the code..i kno v shud open CMD.exe n write sum stuff but i dnt kno much...if u have any article regrding my situation cn u pls post it here so tht i may read it..thnx..
11/25/2006 1:22 AM | Kuloo
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET