How to create a new database from existing database in SQL Server 2005
-
Hi everyone!
I want to create a new database from existing database in SQL Server 2005. I need all the objects of old database as it is in new database. However, I have an option to take backup of that database and restore it with different name. But I want to achieve this task with Database Script.
Please scrap required database script.
An early reply is requested...
Thanks in advance..
-
Probably the easiest was is to use the copy database function which will create a package that can be re-run later. Right click the database, Tasks, Copy Database.
-
Thanks ramses2nd
but what I need is a database script (query) to achieve the task.
Most welcome if have you could provide sql query.
-
Here you go:
USE master
BACKUP DATABASE northwind TO DISK='c:\northwind.bak'
RESTORE DATABASE northwind_new FROM DISK='c:\northwind.bak' WITH MOVE 'logical data file name' TO 'new physical file name', WITH MOVE 'logical log name' TO 'physical log file name'
That's it. You'll want to replace northwind with the name of the source database and northwind_new with the name of the target database. The logical file names can be found by running
RESTORE FILELISTONLY FROM 'c:\northwind.bak'
-
-
I've used this many times...it works great and is fairly fast...the only catch I've seen is if you are moving it to a different server the assigned users may be incorrect...even if the usernames are the same...easiest to drop and re-add the users once the new database is established...other than that I think it is the best way to make a new db....imho.
regards
Doug
-
You could also use Database Publishing Wizard.
Just give your username , password and datasource , it will automatically generate all your schema and data ,
into a text file.Viole!
For wiki here
Kindest Regards,bahadir cambel
softwareandlife.blogspot.com -
You need to execute sp_fix_users_login when moving the database from one server to another:
---
--- fix_users_login.sql
---
use master
go
set quoted_identifier off
go
if exists (select * from dbo.sysobjects where id = object_id('dbo.sp_fix_users_login'))
begin
print 'dropping old procedure dbo.sp_fix_users_login'
drop procedure dbo.sp_fix_users_login
end
go
---
create procedure sp_fix_users_login
as
---
set quoted_identifier off
---
declare @name varchar(64)
---
declare user_csr cursor for
select name
from sysusers
where issqluser = 1
and name not in ('public','dbo','guest')
order by name
---
open user_csr
fetch user_csr
into @name
print ''
print char(9) + 'Fixing database users ...' + char(13) + char(13)
---
while (@@FETCH_STATUS = 0)
begin
print char(9) + 'Adjusting user ' + @name
exec sp_change_users_login @Action='Auto_Fix', @UserNamePattern=@name
fetch user_csr
into @name
end
print ''
print char(9) + 'All database users fixed' + char(13)
close user_csr
deallocate user_csr
---
--- clean up and exit
---
return
go
use master
go
if exists (select * from dbo.sysobjects where id = object_id('dbo.sp_fix_users_login'))
begin
print 'procedure dbo.sp_fix_users_login created'
end
go
---
--- fix_users_login.sql
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply