Solved

NAMING A BACKUP

Posted on 2004-07-30
10
254 Views
Last Modified: 2012-05-05
I am going to run a job nightly to restore backups from my prod box to my dev box.  Is it possible to drop and replace a backup with the same name?  

Let me clarify a little better.

When I wrote the script to restore the backups from the prod box to the dev box, I included the name of the backup file; exp.(backup_db_2004.bak).  Each night my backup runs, a new file is placed in the backup directory with a unique name.  Is there a way to change the name of that file before running my script?
0
Comment
Question by:luserafino
  • 6
  • 4
10 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678089
Im not quite following,   are you wanting to change the filename before a restore, or specify a filename in a restore statement or specify a filename for a backup?
0
 

Author Comment

by:luserafino
ID: 11678202
I need to either change the filename before the restore or specify a filename for a backup.  Either way, the file name has to stay the same or be changed in order for me not to have to go into the script and change it daily. If you can tell me how to change the filename before the restore, that would be perfect.  Here is my script:

USE master

RESTORE FILELISTONLY
FROM DISK = '\\production_serv\F$\SQLBCKUP\bkupdir\database.bak'
RESTORE DATABASE database
FROM DISK = '\\production_serv\F$\SQLBCKUP\bkupdir\database.bak'
WITH RECOVERY,
MOVE 'database_dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.mdf',
MOVE 'database_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.ldf'

GO
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678268
Ah ok.


personally I would create a little SP

CREATE PROCEDURE usp_Restore(@Filename varchar(200)) AS
BEGIN

RESTORE FILELISTONLY
FROM DISK =@Filename
RESTORE DATABASE database
FROM DISK = @Filename
WITH RECOVERY,
MOVE 'database_dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.mdf',
MOVE 'database_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.ldf'


END
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678272
i think that should do the trick
0
 

Author Comment

by:luserafino
ID: 11678598
Forgive me for being so ignorant, but how do i pass the name?  i am pretty new to this.  
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11678619
you would call this stored procedure by doing :


usp_Restore '\\production_serv\F$\SQLBCKUP\bkupdir\database.bak'

for example.
0
 

Author Comment

by:luserafino
ID: 11678774
Okay...maybe I'm not getting this.  One more time. :)  

My backup runs each night.  It names the file each night a different name that includes the date.

In order to run this automated (scheduled job), the name of that file has to be changed before I do a restore, how do i do that?  I must be having a blonde moment here.

Your suggestion for running an SP is good, however, when I call it, the name of the backup from the night before will be different each day. Am I making sense?
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 50 total points
ID: 11678949
lets say for example:

you name your backup files

c:\20040730backup.bak



if you called


 usp_Restore 'c:\' +  convert(varchar(8),getdatE(),112) + 'backup.bak'

or you could modify the sp to :


CREATE PROCEDURE usp_Restore AS
BEGIN

DECLARE @Filename varchar(100)
SET @Filename='c:\' +  convert(varchar(8),getdatE(),112) + 'backup.bak'


RESTORE FILELISTONLY
FROM DISK =@Filename
RESTORE DATABASE database
FROM DISK = @Filename
WITH RECOVERY,
MOVE 'database_dat' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.mdf',
MOVE 'database_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL\data\database.ldf'


END


0
 

Author Comment

by:luserafino
ID: 11679028
i gotcha.  thanks for the help and being so patient. Your the best!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11679078
your welcome.  
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question