?
Solved

Renaming Databases with Variable...

Posted on 2003-03-05
5
Medium Priority
?
198 Views
Last Modified: 2012-06-27
Hi,
I'm using Win2k Server w/SQLServer2K.  I recieve a database dump every night and I use the restore function  to put it in my SQL Server then I have to rename the Database to a date format ("03_05_03" for example).  I want to know if there is anyway to automate this process.  I would have to be able to rename a table with a variable (like the date).

In short
I need to do the following things automated...
1. Grab the Database dump like "MyData.bak"
2. Restore Database.
3. Rename that Database with the day's date.

I am a newbie to this and really can't find much on this.

Hope to hear from you soon...
Thank You for your time and patience,
nile88@comcast.net
0
Comment
Question by:nile88
[X]
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
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 8087723
1) I think you know that already
2) that also, although you might want to restore the database directly to the final name instead of restoring+renaming it. Note that the RESTORE command doesn't expect the "old" name of the database that was backed up, but the new name.
3) the stored procedure sp_renamedb should help you to rename a database.

To automate this, you will probably need a scheduler, where you can take either the Windows Scheduler (scheduled tasks), or use the SQL Server Agent to schedule a job.

CHeers
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8102434
try this:
--With this code (below) you can for example:create stored procedure then schedule job
-----------------------------------------------------

declare @dbName varchar(100)
declare @newdataPath varchar(100)
declare @newLogPath varchar(100)


set @dbName= 'TestDB_' + convert(varchar(10),getdate(),112)
set @newdataPath= 'c:\' + @dbName + '.mdf'
set @newLogPath= 'c:\' + @dbName + '.ldf'


BACKUP DATABASE Northwind

    TO DISK = 'c:\Northwind.bak'



RESTORE DATABASE @dbName

    FROM DISK = 'c:\Northwind.bak'

    WITH MOVE 'Northwind' TO @newdataPath,

   MOVE 'Northwind_log' TO @newLOgPath

GO
------------------------------------------------
0
 

Expert Comment

by:CleanupPing
ID: 9276110
nile88:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10994311
No comment has been added to this question in more than 245 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: angelIII http:#8087723

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

766 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