I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
Hi everyone.
I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.
However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).
Is there any way I can do this?
My query is like this:
SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]
Thanks
Andrew
Hi,
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
Thanks,
Tien,
Hi,
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book
Online, but is seemed that only save file to server. How can I save backup
file to local computer? or there is other way as easy as this statement for
backup/restore database?
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
Thanks,
Tien,
Our company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?
Michelle
I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.
Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?
This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.
Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?
Also, are there any issues going from Windows Server 2000 to Windows Server 2003?
Thanks in advance.
Hi, guys
I has a question for you:
How can i Export the users and permisions and restore it again?
Thanks in advance
We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
any suggestions would be appreciated
Regards
Chris V
here's my situation. i want to take a copy of my database at work and take it home to my home pc. what/how is the best way to do this. i am relatively new to this stuff, so detailed directions would be nice.
sidebar - if i have the .mdf and .ldf files, can I restore from them?
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
How can I Export Database with foreing Key and primary key.
Operation is that
SQL2005 Management Studio/Database/Tasks/Export Data
Before Version is SQL2000 we can Selected Copy Object and data between server and then Use Default Options click checked and Select Copy Index, Copy Foreing Primary key vs vs
But this options is not found in the SQL2005 Management Studio/Database/Tasks/Export Data wizard or I can't found it.
How can I export foreing Key and primary key with SQL2005 Management Studio/Database/Tasks/Export Data wizard.
Best Regards,
Athena.
Please, can anyone sort this out for me?
What is the difference between backup-restore and export-import data?
Thanks in advance :rolleyes:
I have created a DTS package that pulls data from one column (varchar,600) in a table and exports it to a text file. The max length in this field is only 285 characters long, however, the end of one of the records is being truncated in the text file. Data is tab delimited in the table and is being exported that way.
Any tips would be greatly appreciated.
Hi,
Its a common issue with reporting services, exporting a report with huge data to excel takes long time to render. Im facing the same issue. Trying to export a SSRS 2005 report to Excel 2003 takes very long time.
Problem 1:
The time taken to generate the excel report is pretty long (about 10 minutes) as the report runs to hundreds of pages. (The excel has about 30,000 rows and is ~15 MB)
Problem 2:
Once I open the excel and close it the size reduces to half of it. This is understood because of lot of characters values in the report that can be represented as single byte string, that€™s probably where Excel is making a difference. In Reporting Services it always write strings as 2-byte Unicode, but Excel will always try to compress to single byte when possible.
Am majorly concerned about the Problem 1. Any solutions would be highly appreciated. Thanks in advance.
How is it possible that a 133MB SQL7 database, the backup of the database itself takes 2 seconds, the transaction log backup takes 25 minutes??? We are doing log backup every 10 minutes, and appending.
Thanks.
a
guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
View 8 Replies View RelatedThe database is about 5 Gb and the transaction log is about 13 M
and it takes 11 hours to do a full back up.
I am not sure why it takes that long? Is there any thing I can check?
IT is SQL v7.
Thank you
Currently we update database A everyday with tables from database B.
Database is India and has poor connection speed. What would be the best method of moving this data.
Currently it's about 100 tables and on average its about 900 mb.
Any input is greatly appreciated.
thanks,
I have two databases DB_1 and DB_2 on my server. Both of them have 25 identical tables.
Tables under DB_2 gets refreshed with new data daily and once it's ready with new data, those data should be copied to DB_1 tables (deleting any existing data and loading it with new data).
My question is there any other way other than using Import Data Wizard for moving the data between two databases which included deleting any existing data on DB_1 tables and loadit with new data.
Please let me know...Thanx in advance...
I have two databases located on the same server. I was able to script a identical table in the other database, but how do I transfer the table data there?
View 5 Replies View Related1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
Hi there!
I'm developing an application and i need to do the next thing:
I need to make a process, but this process just need to be done, when nobody else is modyfing datas in the database.
Is there anyway i could check this? I have read about the sp_who stored procedure but this one return all the activity in all the databases, and i just need to check in one specifically. And there's another issue, we all access with the user dbo to the database...
Any help will be appreciated..
I restored a copy of a database onto a new server. I created the proper logins on the new server prior to the restore. The users and permissions should all be identical. They appear to be in the system tables, but none of the users or permissions appear in the appropriate screens on Enterprise Manager.
Also, even though the permissions appear to be intact in the syspermissions table, when I connect as a certain user and try to query a table the user should have permissions on, I'm denied access.
Does anyone have any suggestions?
Thanks in advance!
Lisa Rae
Is there an SP that can tell the database, the users with access to the database, and their permissions? Meaning the result would be something like...
<database> <username> db_datawriter
<database> <username> db_datareader
<database> <usernameII> db_owner
Thanks in advance
-Kyle
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate
use xxx
update tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate
ok, here is an interesting one.
We have multiple jobs that run the following SQL Backups to Permanent Backup devices.
Backup Database DBX to DBXFull with NoInit - 1 am
Backup Database DBX to DBXDiff with Differential, NoInit - 12 pm
Backup Log DBX to DBXLog with NoInit - every hour on the hour
the devices are in path E:SQLBackups<databasename>
E: is on a SAN
Every Night E: is backed up by legato, When Legato Starts to backup the directory, at the hour a transaction log backup occurs. The Backup Fails and The Database Is Taken OFFLINE.
Questions are as follows.
What is causing the db to be taken offlline?
How can i stop the db from being taken offline while still mainting my current backup strategy.
Temporarily we have stopped tlog backups during the directory backup phase (legato) and then resume them when it is complete.
Difficulty Level: we will not be using Legato's SQL Plugin for the backups, they have to be performed with jobs and SQL Backup.
sql 2005 stnd on a server of decent spec.
dbase in question is only about 5GB on a 450GB partition.
at the begining of the month I run:
BACKUP LOG [objectstore] TO DISK ='D:BackupsProdackup_objectstore.BAK'
WITH NOFORMAT , INIT , NAME = N'objectstore backup'
and then every 10 minutes (within working hours) for the rest of the month I
run:
BACKUP LOG [objectstore] TO DISK ='D:BackupsProdackup_objectstore.BAK'
WITH NOFORMAT , NOINIT , NAME = N'objectstore backup'.
The amount of data that gets backed up is the same through out the month and
the loading on the server as a whole also stays constant throughout the month
- NOTHING increases throughout the month that would affect this server in any
way, yet at the begining of the month the backup takes 10 seconds, and at the
end, it gets up to 5-6 minutes.
why?
THanks
Alastair Jones.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
I need to write TSQL script that will insert, update, delete rows in a production db from new, updated and deleted data from staging db. They are both in different servers and I can only use TSQL. Basically, the production DB needs to be synchornized with the staging DB.
I was thinking using dynamic sql, cursors, and linked servers. Any ideas?
Jim
Hi,
I want to create a new user for my database and allow them to only select data from the tables...
CREATE LOGIN NEHardcoreWITH PASSWORD = 'abc'USE aiaccontentdb;CREATE USER NEHardcore FOR LOGIN NEHardcore
What permissions will this give to the new user? Do I need to grant any permissions and/or revoke any?
Thanks!
I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.
Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.
I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.
I'm not sure why this is as I am logged in as sa.
Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.
Can anyone help?
Cheers.
Hi ,
I have a bunch of Users about 150 of them. I think they can't even run a SELECT .
When they try to run a select they get error messages :
"SELECT permission denied on object 'Object name', database 'Db_name', owner 'dbo'."
I have check out every thing !! All these Users are the members of public role on the database !!
They work fine on other Db's with the same settings !!
Not Sure what to do
PLEASE HELPP !!!
I have used DTS to imports users from one database(a) to another(b)
DTS run fine, but the original permissions don't were filled.
Can someone explain How I can do it?
Luiz Lucasi
Rio de Janeiro - RJ