Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Is there sql script or stored procedure or something simular I can use to create a copy and rename a sql server db?

Posted on 2011-02-26
3
Medium Priority
?
239 Views
Last Modified: 2012-05-11
A lot of my day is spent create new sql dbs via the UI

Is there a sql script or something simular I can use to speed this up for me, i.e. i'd pass in the name of an existing db, and a new name, and thescript would backup and create/restore a new db based on the new name?

THanks
Webbo
0
Comment
Question by:Webbo_1980
3 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34987591
use can use "create database", sp_renamedb stored procedure and "backup database" commands.
0
 

Author Comment

by:Webbo_1980
ID: 34987638
Thanks Devlab2012, but will that do the restore ?

Would it be possible to give me a quick examples if thats ok?
0
 
LVL 7

Accepted Solution

by:
rmm2001 earned 2000 total points
ID: 34989749
Try something like this. You'll have to set all of the variables to what you need though. You could put it in a stored proc and just pass in the variables too.
DECLARE @OrigDBName VARCHAR(100)
DECLARE @OrigDBDataName VARCHAR(100)
DECLARE @OrigDBLogName VARCHAR(100)
DECLARE @OrigDBNameBackup VARCHAR(100)

SET @OrigDBName = 'OrigDB'
SET @OrigDBDataName = 'OrigDB_Data'
SET @OrigDBLogName = 'OrigDB_Log'
SET @OrigDBNameBackup = 'OrigDBBackup'

DECLARE @RestoreDBName VARCHAR(100)
DECLARE @RestoreDBDataLocation VARCHAR(100)
DECLARE @RestoreDBLogLOcation VARCHAR(100)
DECLARE @RestoreDBNameBackup VARCHAR(100)

SET @RestoreDBName = 'NewDB'
SET @RestoreDBDataName = 'RestoreDB_Data'
SET @RestoreDBLogName = 'RestoreDB_Log'
SET @RestoreDBDataLocation = 'C:\Data\RestoreDB.mdf'
SET @RestoreDBNameLogLocation = 'C:\Data\RestoreDB.ldf'

DECLARE @SqlCmd VARCHAR(8000)

SET @SqlCmd = '
BACKUP DATABASE [' + @OrigDbName + ']
   TO ['+ @OrigDBNameBackup +']

RESTORE FILELISTONLY 
   FROM ['+ @OrigDBNameBackup +']

RESTORE DATABASE ['+ @RestoreDBName +'] 
   FROM ['+ @OrigDBNameBackup +'] 
   WITH MOVE ''' + @OrigDBDataName + ''' TO ''' + @RestoreDBDataLocation + ''',
   MOVE ''' + @OrigDBLogName + ''' TO ''' + @RestoreDBNameLogLocation'''
GO'

EXEC (@SqlCmd)

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 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