August 2, 2004 at 10:47 pm
Hi I'm using a sql server 2000, I need a script for next I hope you can help me:
I have a database lets call "sport" so I want to make a copy or a bacupup every 2 months so my fisrts backup i want to call "sportjan_feb2004.bck"
the second "sportmar_apr2004.bck" and so on... but some times maybe I want to check what I have in some period so I want to restore my database from that period and overwrite the existing (current) but first obviusly I will backup recent period to later restore this period.
I hope you can get my idea thanks
August 4, 2004 at 12:58 pm
/*
First, a couple of suggestions….
You should probably plan to make backups more frequently than once every two months. Depending on the activity in the database, and how much data you can afford to lose, you may want to take weekly, daily, or even more frequently than that.
You might want to rethink the naming convention of your backups. For example, if you should decide to take monthly backups, consider Sport2004-08.bck (for 2004, August). This way, they will sort properly in a backup folder, which makes locating a particular backup file much, much easier.
Next, when you wish to restore a backup to look at the old data, consider restoring it to a NEW database instead of overwriting your production data. To do this, you simply specify the new database name, and obviously change the physical file name to reflect the new database name.
RESTORE Sport2004Aug
FROM DISK = ‘X:\Sport_Backups\Sport2004-08.bck’
WITH MOVE ‘Sport_Data’ TO ‘F:\SQL_Data\Sport2004Aug_Data.mdf’,
MOVE ‘Sport_Log’ TO ‘L:\SQL_Logs\Sport2004Aug_Log.ldf’
Now, how to name the backup files automatically -
You'll need to do your backups from a scheduled job executing a script or stored procedure rather than a maintenance plan.
Here’s a stored procedure that I wrote to get you started….
Steve
*/
if exists
(select *
from sysobjects
where id = object_id(N'[dbo].[usp_db_backup]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_db_backup]
GO
create proc usp_db_backup
@path varchar(50),
@dbname varchar(50)
as
/*
To execute this stored procedure after it has been created -
exec usp_db_backup 'x:\SQL_Backups\', 'Sport'
*/
-- -- the following Declare and set statements are for testing only.
-- Declare
-- @path varchar(50),
-- @dbname varchar(50)
--
-- set @path = 'x:\SQL_Backups\'
-- set @dbname = 'Sport'
Declare
@full_path varchar(100),
@date datetime,
@date2 varchar(50),
@time varchar(10),
@query_string varchar(200),
@ret_char char(2)
select @date = getdate()
-- format the date stamp for the filename.
select @date2 = substring(convert(char(8),@date,112),1,4) + '-' + substring(convert(char(8),@date,112),5,2)
-- build the full path for the backup file
select @full_path = @path + @dbname + @date2 + '.bck'
-- build the backup command
select @query_string = 'BACKUP DATABASE ' + @dbname + ' to Disk = ''' + @full_path + ''' with init'
--following select for testing only
-- select @query_string
-- execute the backup command
exec ( @query_string )
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply