November 22, 2002 at 1:31 pm
Here's a sample of something I did-not pretty but it works
From QA on the source server Copy and paste the code that follows
Edit accordingly to point at the latest backups of your database, if you need to recreate your backup with out time stamps.
Make sure you turn off the header output, (does anyone know hot do this in query analyzer?)
Copy the results into QA on the destnation server - edit if you need to and run.
Run the query, sit down open up your favorite SQL Server magazine and enjoy a glass of wine.
If you need to move login accounts on the dest server see:
How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/view/tn.asp?kb=246133
You might also need to run sp_change_users_login for each end users - See my friend A.Goldman at http://www.sqldevpro.com/ He's got some good scripts for automating this.
Scripting is easy take sometime you can do this just as easy for detaching and attaching
use master
go
declare @name sysname
declare @path1 varchar(50)
declare @path2 varchar(50)
select @path1 = '''' + '\\servername\e$\MSSQL7BKUP\'
select @path2 = '_full.bak'+ char(39) + ' with replace,' + '"'
set nocount on
declare sysdatabases_cursor cursor LOCAL STATIC
for select name
from sysdatabases where dbid >6
Order by name
open sysdatabases_cursor
fetch next
from sysdatabases_cursor
into @name
while (@@fetch_status = 0)
begin
select 'Select ' + '"' + 'restore database ' + @name + ' FROM DISK= ' + @path1 + @name + "\" + @name + @path2
select 'select '+ '" Move ", char(39) + rtrim(substring(name,1,30)) + char(39)," TO ", char(39) + rtrim(substring(filename,1,50)) + char(39) + char(44) from ' + @name + '..sysfiles'
SELECT 'Select ' + '"' + 'RECOVERY' + '"'
SELECT 'Select ' + '"' + 'GO' + '"'
fetch next
from sysdatabases_cursor
into @name
end
close sysdatabases_cursor
deallocate sysdatabases_cursor
John Zacharkan
John Zacharkan
November 25, 2002 at 3:14 am
"Make sure you turn off the header output, (does anyone know hot do this in query analyzer?)"
Query
Current Connection Options
Advanced
Print Headers (Remove Tick)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply