Is it a bad thing to do a manual copy of all my databases
from one server to another in Win Exp? Will it hurt
anything? I detached the DB, copied it over, and then
reattached on the new server. ?Detach/attach is the way the copy database wizard does it as well, so that
is fine.
The other option is to do a backup, copy the backup and then do a restore on
the new server.
--
Jacco Schalkwijk
SQL Server MVP
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?|||If you're concerned run DBCC CHECKDB after you have successfully attached
the db. If the file is very large > 40Gb I generally use robocopy
--
HTH
Ryan Waight, MCDBA, MCSE
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?|||See if these help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?
Showing posts with label manual. Show all posts
Showing posts with label manual. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
Copying Database To Database
Hello,
I have two databases, One is full of tables, The Other is empty,
What I am trying to do is To Copy Full DB to Empty DB,
Manual way is Backup DB and Restore to Target DB
But, I wanna do it using a script and trying to make this process Automatic.
What this automatic process might do is; Every Day One time ;
First; Delete Target DB s all Tables
Second; Backup Source DB
Third; Restore (Overwrite) DB to Target DB
s it possible in MS SQL Server ?
Thank you.Hi,
It is possible but it is very time consuming if you have Foreign key
relation ship. So it is recommended to do a
Backup and Restore every day. Steps:-
1. BACKUP source DB (See BACKUP DATABASE command)
2. Set the destination DB to sinlg user (ALTER DATABASE DBNAME SET
SINGLE_USER with ROLLBACK IMMEDIATE)
3. RESTORE the BACKUP (See RESTORE DATABASE command)
4. Set back te database to Multi user (ALTER DATABASE DBNAME SET MULTI_USER)
Thanks
Hari
SQL Server MVP
"Bongee" <bongeee@.bonbon.net> wrote in message
news:ePsYYdXqFHA.1328@.tk2msftngp13.phx.gbl...
> Hello,
> I have two databases, One is full of tables, The Other is empty,
> What I am trying to do is To Copy Full DB to Empty DB,
> Manual way is Backup DB and Restore to Target DB
> But, I wanna do it using a script and trying to make this process
> Automatic.
> What this automatic process might do is; Every Day One time ;
> First; Delete Target DB s all Tables
> Second; Backup Source DB
> Third; Restore (Overwrite) DB to Target DB
> s it possible in MS SQL Server ?
> Thank you.
>|||You don't need to delete any objects in your target DB, the restore will do
Another easy way is Snapshot replication
"Bongee" <bongeee@.bonbon.net> wrote in message
news:ePsYYdXqFHA.1328@.tk2msftngp13.phx.gbl...
> Hello,
> I have two databases, One is full of tables, The Other is empty,
> What I am trying to do is To Copy Full DB to Empty DB,
> Manual way is Backup DB and Restore to Target DB
> But, I wanna do it using a script and trying to make this process
> Automatic.
> What this automatic process might do is; Every Day One time ;
> First; Delete Target DB s all Tables
> Second; Backup Source DB
> Third; Restore (Overwrite) DB to Target DB
> s it possible in MS SQL Server ?
> Thank you.
>|||You do not need first steep. You can use "Backup Database" or "Maintenance
Plan" wizard to setup a schedule backup, and create a schedule job that will
restore the last database backup to another db. This job has to be scheduled
to run after the backup one. See "restore" in BOL, to see learn how to use i
t.
AMB
"Bongee" wrote:
> Hello,
> I have two databases, One is full of tables, The Other is empty,
> What I am trying to do is To Copy Full DB to Empty DB,
> Manual way is Backup DB and Restore to Target DB
> But, I wanna do it using a script and trying to make this process Automati
c.
> What this automatic process might do is; Every Day One time ;
> First; Delete Target DB s all Tables
> Second; Backup Source DB
> Third; Restore (Overwrite) DB to Target DB
> Ys it possible in MS SQL Server ?
> Thank you.
>
>
I have two databases, One is full of tables, The Other is empty,
What I am trying to do is To Copy Full DB to Empty DB,
Manual way is Backup DB and Restore to Target DB
But, I wanna do it using a script and trying to make this process Automatic.
What this automatic process might do is; Every Day One time ;
First; Delete Target DB s all Tables
Second; Backup Source DB
Third; Restore (Overwrite) DB to Target DB
s it possible in MS SQL Server ?
Thank you.Hi,
It is possible but it is very time consuming if you have Foreign key
relation ship. So it is recommended to do a
Backup and Restore every day. Steps:-
1. BACKUP source DB (See BACKUP DATABASE command)
2. Set the destination DB to sinlg user (ALTER DATABASE DBNAME SET
SINGLE_USER with ROLLBACK IMMEDIATE)
3. RESTORE the BACKUP (See RESTORE DATABASE command)
4. Set back te database to Multi user (ALTER DATABASE DBNAME SET MULTI_USER)
Thanks
Hari
SQL Server MVP
"Bongee" <bongeee@.bonbon.net> wrote in message
news:ePsYYdXqFHA.1328@.tk2msftngp13.phx.gbl...
> Hello,
> I have two databases, One is full of tables, The Other is empty,
> What I am trying to do is To Copy Full DB to Empty DB,
> Manual way is Backup DB and Restore to Target DB
> But, I wanna do it using a script and trying to make this process
> Automatic.
> What this automatic process might do is; Every Day One time ;
> First; Delete Target DB s all Tables
> Second; Backup Source DB
> Third; Restore (Overwrite) DB to Target DB
> s it possible in MS SQL Server ?
> Thank you.
>|||You don't need to delete any objects in your target DB, the restore will do
Another easy way is Snapshot replication
"Bongee" <bongeee@.bonbon.net> wrote in message
news:ePsYYdXqFHA.1328@.tk2msftngp13.phx.gbl...
> Hello,
> I have two databases, One is full of tables, The Other is empty,
> What I am trying to do is To Copy Full DB to Empty DB,
> Manual way is Backup DB and Restore to Target DB
> But, I wanna do it using a script and trying to make this process
> Automatic.
> What this automatic process might do is; Every Day One time ;
> First; Delete Target DB s all Tables
> Second; Backup Source DB
> Third; Restore (Overwrite) DB to Target DB
> s it possible in MS SQL Server ?
> Thank you.
>|||You do not need first steep. You can use "Backup Database" or "Maintenance
Plan" wizard to setup a schedule backup, and create a schedule job that will
restore the last database backup to another db. This job has to be scheduled
to run after the backup one. See "restore" in BOL, to see learn how to use i
t.
AMB
"Bongee" wrote:
> Hello,
> I have two databases, One is full of tables, The Other is empty,
> What I am trying to do is To Copy Full DB to Empty DB,
> Manual way is Backup DB and Restore to Target DB
> But, I wanna do it using a script and trying to make this process Automati
c.
> What this automatic process might do is; Every Day One time ;
> First; Delete Target DB s all Tables
> Second; Backup Source DB
> Third; Restore (Overwrite) DB to Target DB
> Ys it possible in MS SQL Server ?
> Thank you.
>
>
Monday, February 13, 2012
Copy SPROCs to different database
Is there a manual or code way to copy stored procedures from 1one sql db to
another without opening each one, copying, creating a new SPROC in target db
and pasting?
Can DTS accomplish this?You can use DTS. If you have enterprise manager, you can also just
right-click on a database and choose to import or export data. When you get
into the wizard, choose to "Copy objects and data betwen SQL Server
databases". You can then save this entire thing as a DTS package, which you
can manipulate, schedule, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"scott" wrote:
> Is there a manual or code way to copy stored procedures from 1one sql db t
o
> another without opening each one, copying, creating a new SPROC in target
db
> and pasting?
> Can DTS accomplish this?
>
>|||You can use Enterprise Manager and right click on the db and choose "All
Tasks - Generate SQL Script". Then choose all the sp's and you will get one
script for all the sps. Just run that in the other db.
Andrew J. Kelly SQL MVP
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>|||You can use DTS to copy database objects from one database to another.
Are you using Sql Server 2000 or Sql Server 2005? I think, if I rememer
correctly, there is a copy database or copy database objects task in 2000.
2005 makes it a whole lot easier.
-Tim
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>
another without opening each one, copying, creating a new SPROC in target db
and pasting?
Can DTS accomplish this?You can use DTS. If you have enterprise manager, you can also just
right-click on a database and choose to import or export data. When you get
into the wizard, choose to "Copy objects and data betwen SQL Server
databases". You can then save this entire thing as a DTS package, which you
can manipulate, schedule, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"scott" wrote:
> Is there a manual or code way to copy stored procedures from 1one sql db t
o
> another without opening each one, copying, creating a new SPROC in target
db
> and pasting?
> Can DTS accomplish this?
>
>|||You can use Enterprise Manager and right click on the db and choose "All
Tasks - Generate SQL Script". Then choose all the sp's and you will get one
script for all the sps. Just run that in the other db.
Andrew J. Kelly SQL MVP
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>|||You can use DTS to copy database objects from one database to another.
Are you using Sql Server 2000 or Sql Server 2005? I think, if I rememer
correctly, there is a copy database or copy database objects task in 2000.
2005 makes it a whole lot easier.
-Tim
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>
Subscribe to:
Posts (Atom)