Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

NAMING A BACKUP

Posted on 2004-07-30
10
255 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

829 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